analyze table estimate statistics : Analyze « SQL Plus « Oracle PL / SQL






analyze table estimate statistics

  
SQL> CREATE TABLE EMP(
  2      EMPNO NUMBER(4) NOT NULL,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2),
  9      DEPTNO NUMBER(2)
 10  );

SQL> INSERT INTO EMP VALUES(2, 'Jack', 'Tester', 6,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);

1 row created.

SQL> INSERT INTO EMP VALUES(3, 'Wil', 'Tester', 6,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);

1 row created.

SQL> INSERT INTO EMP VALUES(4, 'Jane', 'Designer', 9,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES(5, 'Mary', 'Tester', 6,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);

1 row created.

SQL> INSERT INTO EMP VALUES(7, 'Chris', 'Designer', 9,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES(8, 'Smart', 'Helper', 4,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES(9, 'Peter', 'Manager', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES(10, 'Take', 'Tester', 6,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);

1 row created.

SQL> INSERT INTO EMP VALUES(13, 'Fake', 'Helper', 4,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);

1 row created.




SQL>
SQL> set arraysize 50
SQL> set autotrace on statistics
SQL> select empno, deptno
  2   from emp
  3  /
Enter...

     2     30
     3     30
     4     20
     5     30
     7     10
     8     20
     9     10
    10     30
    13     20
     2     30
     3     30
     4     20
     5     30
     7     10
     8     20
     9     10
    10     30
    13     20

18 rows selected.


Statistics
----------------------------------------------------------
         64  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        679  bytes sent via SQL*Net to client
        380  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         18  rows processed

SQL> create table IX ( x number primary key ) organization index;



SQL>
SQL> analyze table IX estimate statistics;

Table analyzed.

SQL>
SQL> select empno, cursor(select x from ix)
  2  from emp
  3  /
Enter...

     2 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected

Enter...

     3 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected

Enter... drop table emp;

     4 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected

Enter...
     5 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected

Enter...
     7 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected

Enter...
     8 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected

Enter...
     9 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected

Enter...
    10 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected

Enter...
    13 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected

Enter...
     2 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected

     3 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected

Enter...
     4 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected

Enter...
     5 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected

Enter...

     7 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected

Enter... CREATE TABLE EMP(

     8 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected

Enter...
     9 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected

Enter...
    10 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected

Enter...
    13 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected


18 rows selected.

SQL> set autotrace off statistics

   
    
  








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 tableName compute statistics for table, for all indexes, for all indexed columns;
6.ANALYZE TABLE employee COMPUTE STATISTICS
7.analyze index validate structure
8.analyze table compute statistics
9.analyze table compute statistics for all indexes
10.analyze table compute statistics for a table with two indexes
11.create or replace outline and analyze table compute statistics
12.Analyze a table with/without index
13.Anayze all objects in the APPOWNER schema.
14.Analyze index on varchar2 value and date type value
15.analyze and autotrace full outer join and union
16.analyze and autotrace single column key and multi-column key
17.analyze and autotrace table with primary key
18.analyze index t_idx validate structure
19.analyze table TABLENAME compute statistics;
20.analyze table after creating index
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