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