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.
Related examples in the same category