IN-EXISTS

SELECT /* EXISTS example */
e.employee_id
, e.first_name
, e.last_name
, e.salary
FROM employees e
WHERE EXISTS (SELECT 1 FROM orders o /* Note 1 */
WHERE e.employee_id = o.sales_rep_id /* Note 2 */
AND o.customer_id = 144); /* Note 3

ID OPERATION OPTIONS OBJECT_NAME OPT COST
---- -------------------- --------------- ---------------------- --- ----------
0 SELECT STATEMENT CHO
1 FILTER
2 TABLE ACCESS FULL EMPLOYEES ANA 155
3 TABLE ACCESS BY INDEX ROWID ORDERS ANA 3
4 INDEX RANGE SCAN ORD_CUSTOMER_IX ANA 1

 

 

SELECT /* IN example */
e.employee_id
, e.first_name
, e.last_name
, e.salary
FROM employees e
WHERE e.employee_id IN (SELECT o.sales_rep_id /* Note 4 */
FROM orders o
WHERE o.customer_id = 144); /* Note 3 */

ID OPERATION OPTIONS OBJECT_NAME OPT COST
---- -------------------- --------------- ---------------------- --- ----------
0 SELECT STATEMENT CHO
1 NESTED LOOPS 5
2 VIEW 3
3 SORT UNIQUE 3
4 TABLE ACCESS FULL ORDERS ANA 1
5 TABLE ACCESS BY INDEX ROWID EMPLOYEES ANA 1
6 INDEX UNIQUE SCAN EMP_EMP_ID_PK ANA

 

-----------------------

The following SQL statement uses IN:
SELECT /* IN example */
e.employee_id
, e.first_name
, e.last_name
, e.department_id
, e.salary
FROM employees e
WHERE e.department_id = 80 /* Note 5 */
AND e.job_id = 'SA_REP' /* Note 6 */
AND e.employee_id IN (SELECT o.sales_rep_id FROM orders o); /* Note 4 */

 

ID OPERATION OPTIONS OBJECT_NAME OPT COST
---- -------------------- --------------- ---------------------- --- ----------
0 SELECT STATEMENT CHO
1 NESTED LOOPS 125
2 VIEW 116
3 SORT UNIQUE 116
4 TABLE ACCESS FULL ORDERS ANA 40
5 TABLE ACCESS BY INDEX ROWID EMPLOYEES ANA 1
6 INDEX UNIQUE SCAN EMP_EMP_ID_PK ANA

SELECT /* EXISTS example */
e.employee_id
, e.first_name
, e.last_name
, e.salary
FROM employees e
WHERE e.department_id = 80 /* Note 5 */
AND e.job_id = 'SA_REP' /* Note 6 */
AND EXISTS (SELECT 1 /* Note 1 */
FROM orders o
WHERE e.employee_id = o.sales_rep_id); /* Note 2 */

ID OPERATION OPTIONS OBJECT_NAME OPT COST
---- -------------------- --------------- ---------------------- --- ----------
0 SELECT STATEMENT CHO
1 FILTER
2 TABLE ACCESS BY INDEX ROWID EMPLOYEES ANA 98
3 AND-EQUAL
4 INDEX RANGE SCAN EMP_JOB_IX ANA
5 INDEX RANGE SCAN EMP_DEPARTMENT_IX ANA
6 INDEX RANGE SCAN ORD_SALES_REP_IX ANA 8

 

 

 

 

select *  from  tssa.eosoperator

select *  from   tssa.eosorg_t_employee

select *  from   tssa.eosorg_t_organization

SELECT /* IN example */
e.operatorid
, e.empcode
, e.orgid

FROM eosorg_t_employee e
WHERE e.orgid IN (SELECT o.orgid
FROM eosorg_t_organization o
WHERE o.parentorgid = 41);

SELECT /* EXISTS example */
e.operatorid
, e.empcode
, e.orgid
FROM eosorg_t_employee e
WHERE EXISTS (SELECT 1 FROM eosorg_t_organization o
WHERE e.orgid = o.orgid
AND o.parentorgid = 41);

 

 

create  index EOSORG_IDX_prid on eosorg_t_organization (parentorgid)
  tablespace TSSAPUB
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 128K
    minextents 1
    maxextents unlimited
  );