analyze table tableName compute statistics for table, for all indexes, for all indexed columns; : Analyze « SQL Plus « Oracle PL / SQL






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> --

   
  








Related examples in the same category

1.analyze materialized view
2.analyze index
3.Analyze table with primary key and foreign key
4.Demonstrate IN versus EXISTS performance
5.ANALYZE TABLE employee COMPUTE STATISTICS
6.analyze index validate structure
7.analyze table compute statistics
8.analyze table compute statistics for all indexes
9.analyze table compute statistics for a table with two indexes
10.create or replace outline and analyze table compute statistics
11.Analyze a table with/without index
12.Anayze all objects in the APPOWNER schema.
13.Analyze index on varchar2 value and date type value
14.analyze and autotrace full outer join and union
15.analyze and autotrace single column key and multi-column key
16.analyze and autotrace table with primary key
17.analyze index t_idx validate structure
18.analyze table TABLENAME compute statistics;
19.analyze table after creating index
20.analyze table estimate statistics
21.analyze table students compute statistics
22.analyze table t compute statistics for table for columns id;
23.analyze table t compute statistics for table, for all indexes, for all indexed columns