Mega Code Archive

Autotrace a nested query

SQL> SQL> create table customer( 2 cust_no integer primary key 3 ,lastname varchar2(20) not null 4 ,firstname varchar2(15) not null 5 ,midinit varchar2(1) 6 ,street varchar2(30) 7 ,city varchar2(20) 8 ,state varchar2(2) 9 ,zip varchar2(5) 10 ,zip_4 varchar2(4) 11 ,area_code varchar2(3) 12 ,phone varchar2(8) 13 ,company_name varchar2(50) 14 ); Table created. SQL> SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name) 2 values(1, 'Allen', 'Joe','J','10 Ave','London','CA','11111','1111','111', '111-1111','Big Company'); 1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name) 2 values(2,'Ward','Sue','W','20 Ave','New York','NY','44444','4444','444', '436-4444','B Company'); 1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name) 2 values(3,'Jason','Pure','J','50 St','Longli','CA','55555','5555','555', '234-4444','C Company'); 1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name) 2 values(4,'Bird','Jill', null,'30 St','Pais','NY','22222','2222','222', '634-7733','D Company'); 1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name) 2 values(5,'Hill','Carl','H','19 Drive','A Town','CA','66666','6566','666', '243-4243','E Company'); 1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name) 2 values(6,'Peter','Yari','P','38 Ave','Small City','NY','77777','7777','777', '454-5443','F Inc'); 1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name) 2 values(7,'Joe','Paula','J','78 St. Apt 3A','Queen City','NY','32322','2323','888', '664-4333','E Inc'); 1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name) 2 values(8,'Chili','Steve','C','38 Ave Apt 62','Mili','CA','88888','8888','787', '456-4566','G Inc'); 1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name) 2 values(9,'Mona','Joe','M','930 Ave933','Kansas City','MO','12345','1234','412', '456-4563','H Inc'); 1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name) 2 values(10,'Hack','Kisi','H','Kings Rd','Bellmore','NY','54321','3898','516', '767-5677','I Inc'); 1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name) 2 values(11,'Bill','Jose','B','12 Giant Rd.','Newton','NJ','23454','1234','958', '123-7367','J Associates'); 1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name) 2 values(12,'Taker','Lawrence','T','1 Sask Rd.','Camp','NJ','19191','3298','928', '123-7384','K Company'); 1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name) 2 values(13,'Richer','Doris','R','213 Easy Street','WarPease','RI','34343','2112','501', '123-7384','L Inc'); 1 row created. SQL> insert into customer( cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name) 2 values(14,'Pete','Doris','P','9 Ave','New York','NY','45454','4222','112', '123-1234','M Company'); 1 row created. SQL> SQL> create table ord( 2 order_no integer primary key 3 ,cust_no integer 4 ,order_date date not null 5 ,total_order_price number(7,2) 6 ,deliver_date date 7 ,deliver_time varchar2(7) 8 ,payment_method varchar2(2) 9 ,emp_no number(3,0) 10 ,deliver_name varchar2(35) 11 ,gift_message varchar2(100) 12 ); Table created. SQL> SQL> SQL> insert into ord(order_no,cust_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message) 2 values(1,1,'14-Feb-2002', 23.00, '14-Feb-2002', '12 noon', 'CA',1, null, 'Gift for wife'); 1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message ) 2 values(2,1,'14-Feb-2003', 510.98, '14-feb-2003', '5 pm', 'NY',7, 'Rose Ted', 'Happy Valentines Day to Mother'); 1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message ) 2 values(3, 2,'14-Feb-2004', 315.99, '14-feb-2004', '3 pm', 'VS',2, 'Ani Forest', 'Happy Valentines Day to Father'); 1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message ) 2 values(4, 2,'14-Feb-1999', 191.95, '14-feb-1999', '2 pm', 'NJ',2, 'O. John', 'Happy Valentines Day'); 1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message ) 2 values(5, 6,'4-mar-2002', 101.95, '5-mar-2002', '2:30 pm', 'MO' , 2, 'Cora', 'Happy Birthday from John'); 1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message ) 2 values(6, 9,'7-apr-2003', 221.95, '7-apr-2003', '3 pm', 'MA', 2, 'Sake Keith', 'Happy Birthday from Joe' ); 1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message ) 2 values(7, 9,'20-jun-2004', 315.95, '21-jun-2004', '12 noon', 'BC', 2, 'Jessica Li', 'Happy Birthday from Jessica'); 1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message ) 2 values (8, 12, '31-dec-1999', 135.95, '1-jan-2000', '12 noon', 'DI', 3, 'Larry', 'Happy New Year from Lawrence'); 1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message ) 2 values (9, 12, '26-dec-2003', 715.95, '2-jan-2004', '12 noon', 'SK',7, 'Did', 'Happy Birthday from Nancy' ); 1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message ) 2 values(10, 4, sysdate-1, 119.95, sysdate+2, '6:30 pm', 'VG',2, 'P. Jing', 'Happy Valentines Day to Jason'); 1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message ) 2 values(11, 2, sysdate, 310.00, sysdate+2, '3:30 pm', 'DC',2, 'C. Late', 'Happy Birthday Day to Jack'); 1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message) 2 values(12, 7, sysdate-3, 121.95, sysdate-2, '1:30 pm', 'AC',2, 'W. Last', 'Happy Birthday Day to You'); 1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message) 2 values(13, 7, sysdate, 211.95, sysdate-4, '4:30 pm', 'CA',2, 'J. Bond', 'Thanks for hard working'); 1 row created. SQL> SQL> SQL> SQL> set autotrace traceonly SQL> SELECT cust_no, lastname 2 FROM customer c 3 WHERE cust_no NOT IN (SELECT cust_no 4 FROM ord ); 7 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2957014985 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 4 (0)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL| CUSTOMER | 14 | 350 | 2 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| ORD | 12 | 156 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "ORD" "ORD" WHERE LNNVL("CUST_NO"<>:B1))) 3 - filter(LNNVL("CUST_NO"<>:B1)) Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 58 recursive calls 0 db block gets 66 consistent gets 0 physical reads 0 redo size 586 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) 7 rows processed SQL> SQL> set autotrace off SQL> drop table customer; Table dropped. SQL> drop table ord; Table dropped. SQL> SQL> --