Mega Code Archive

Analyze table tableName compute statistics for table, for all indexes, for all indexed columns

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> select * from customer; CUST_NO LASTNAME FIRSTNAME M STREET CITY ST ZIP ZIP_ ARE PHONE ---------- -------------------- --------------- - ------------------------------ -------------------- -- ----- ---- --- -------- COMPANY_NAME -------------------------------------------------- 1 Allen Joe J 10 Ave London CA 11111 1111 111 111-1111 Big Company 2 Ward Sue W 20 Ave New York NY 44444 4444 444 436-4444 B Company 3 Jason Pure J 50 St Longli CA 55555 5555 555 234-4444 C Company 4 Bird Jill 30 St Pais NY 22222 2222 222 634-7733 D Company 5 Hill Carl H 19 Drive A Town CA 66666 6566 666 243-4243 E Company 6 Peter Yari P 38 Ave Small City NY 77777 7777 777 454-5443 F Inc 7 Joe Paula J 78 St. Apt 3A Queen City NY 32322 2323 888 664-4333 E Inc 8 Chili Steve C 38 Ave Apt 62 Mili CA 88888 8888 787 456-4566 G Inc 9 Mona Joe M 930 Ave933 Kansas City MO 12345 1234 412 456-4563 H Inc 10 Hack Kisi H Kings Rd Bellmore NY 54321 3898 516 767-5677 I Inc 11 Bill Jose B 12 Giant Rd. Newton NJ 23454 1234 958 123-7367 J Associates 12 Taker Lawrence T 1 Sask Rd. Camp NJ 19191 3298 928 123-7384 K Company 13 Richer Doris R 213 Easy Street WarPease RI 34343 2112 501 123-7384 L Inc 14 Pete Doris P 9 Ave New York NY 45454 4222 112 123-1234 M Company 14 rows selected. SQL> SQL> SQL> analyze table customer compute statistics 2 for table 3 for all indexes 4 for all indexed columns; Table analyzed. SQL> SQL> SQL> set autotrace off SQL> SQL> drop table customer; Table dropped. SQL> SQL> --