Mega Code Archive

AUTOTRACE exists (subquery)

SQL>CREATE TABLE project ( 2 pro_id NUMBER(4), 3 pro_name VARCHAR2(40), 4 budget NUMBER(9,2), 5 CONSTRAINT project_pk PRIMARY KEY (pro_id) 6 ); Table created. SQL> SQL> SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1001, 'A',12345); 1 row created. SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1002, 'ERP',23456); 1 row created. SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1003, 'SQL',34567); 1 row created. SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1004, 'CRM',45678); 1 row created. SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1005, 'VPN',56789); 1 row created. SQL> SQL> SQL>CREATE TABLE server_usage ( 2 pro_id NUMBER(4), 3 emp_id NUMBER, 4 time_log_date DATE, 5 hours_logged NUMBER(8,2), 6 dollars_charged NUMBER(8,2), 7 CONSTRAINT server_usage_pk PRIMARY KEY (pro_id, emp_id, time_log_date) 8 ); Table created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged) 2 VALUES (1001,101,to_date('4-Apr-2004','dd-mon-yyyy'),1123,222); 1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged) 2 VALUES (1002,102,to_date('4-Apr-2005','dd-mon-yyyy'),1124,223); 1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged) 2 VALUES (1003,103,to_date('4-Apr-2006','dd-mon-yyyy'),1125,224); 1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged) 2 VALUES (1004,104,to_date('4-Apr-2007','dd-mon-yyyy'),1126,225); 1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged) 2 VALUES (1005,105,to_date('4-Apr-2008','dd-mon-yyyy'),1127,226); 1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged) 2 VALUES (1001,106,to_date('4-Apr-2009','dd-mon-yyyy'),1128,227); 1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged) 2 VALUES (1002,107,to_date('4-Apr-2010','dd-mon-yyyy'),1129,228); 1 row created. SQL> SQL>CREATE TABLE emp ( 2 emp_id NUMBER, 3 ename VARCHAR2(40), 4 hire_date DATE DEFAULT sysdate, 5 end_date DATE, 6 rate NUMBER(5,2), 7 CONSTRAINT emp_pk PRIMARY KEY (emp_id) 8 ); Table created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, 'Mary', to_date('15-Nov-1961','dd-mon-yyyy'),null,169); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, 'Tom', to_date('16-Sep-1964','dd-mon-yyyy'),to_date('5-May-2004','dd-mon-yyyy'),135); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, 'Peter', to_date('29-Dec-1987','dd-mon-yyyy'),to_date('1-Apr-2004','dd-mon-yyyy'),99); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, 'Mike', to_date('15-Jun-2004','dd-mon-yyyy'),null,121); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, 'Less', to_date('2-Jan-2004','dd-mon-yyyy'),null,45); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, 'Park', to_date('1-Mar-1994','dd-mon-yyyy'),to_date('15-Nov-2004','dd-mon-yyyy'),220); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, 'Ink', to_date('4-Apr-2004','dd-mon-yyyy'),to_date('30-Sep-2004','dd-mon-yyyy'),84); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, 'Tike', to_date('23-Aug-1976','dd-mon-yyyy'),null,100); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, 'Inn', to_date('15-Nov-1961','dd-mon-yyyy'),to_date('4-Apr-2004','dd-mon-yyyy'),70); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, 'Kate', to_date('3-Mar-2004','dd-mon-yyyy'),to_date('31-Oct-2004','dd-mon-yyyy'),300); 1 row created. SQL> SQL> SQL>SET ECHO ON SQL>SET AUTOTRACE ON SQL> SQL>SELECT emp_id, ename 2 FROM emp 3 WHERE EXISTS (SELECT * 4 FROM server_usage 5 WHERE server_usage.pro_id = 1001 6 AND server_usage.emp_id = emp.emp_id); User System Privileges Page 1 ---------- -------------------- ########## Mary 1 row selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2816981487 ---------------------------------------------- | Id | Operation | Name | ---------------------------------------------- | 0 | SELECT STATEMENT | | |* 1 | FILTER | | | 2 | TABLE ACCESS FULL| EMP | |* 3 | INDEX RANGE SCAN | SERVER_USAGE_PK | ---------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( EXISTS (SELECT 0 FROM "SERVER_USAGE" "SERVER_USAGE" WHERE "SERVER_USAGE"."EMP_ID"=:B1 AND "SERVER_USAGE"."PRO_ID"=1 001)) 3 - access("SERVER_USAGE"."PRO_ID"=1001 AND "SERVER_USAGE"."EMP_ID"=:B1) Note ----- - rule based optimizer used (consider using cbo) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 14 consistent gets 0 physical reads 0 redo size 470 bytes sent via SQL*Net to client 380 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SQL>set autotrace off SQL> SQL>drop table emp; Table dropped. SQL>drop table project; Table dropped. SQL>drop table server_usage; Table dropped.