Oracle PL/SQL Tutorial/SQL PLUS Session Environment/analyze

Материал из SQL эксперт
Перейти к: навигация, поиск

analyze and autotrace full outer join and union

   <source lang="sql">

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

Table created. SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> CREATE TABLE DEPT(

 2      DEPTNO NUMBER(2),
 3      DNAME VARCHAR2(14),
 4      LOC VARCHAR2(13)
 5  );

Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> update emp

 2     set deptno = 9
 3   where deptno = 10;

3 rows updated. SQL> SQL> SQL> alter table emp add constraint emp_pk primary key(empno); Table altered. SQL> SQL> alter table dept add constraint dept_pk primary key(deptno); Table altered. SQL> SQL> analyze table emp compute statistics; Table analyzed. SQL> SQL> analyze table dept compute statistics; Table analyzed. SQL> SQL> set autotrace on explain SQL> SQL> select empno, ename, dept.deptno, dname

 2    from emp, dept
 3   where emp.deptno(+) = dept.deptno
 4   UNION ALL
 5  select empno, ename, emp.deptno, null
 6    from emp, dept
 7   where emp.deptno = dept.deptno(+)
 8     and dept.deptno is null
 9   order by 1, 2, 3, 4
10  /
    EMPNO ENAME          DEPTNO DNAME

---------- ---------- --------------
     7369 SMITH              20 RESEARCH
     7499 ALLEN              30 SALES
     7521 WARD                  SALES
     7566 JONES              20 RESEARCH
     7654 MARTIN             30 SALES
     7698 BLAKE                 SALES
     7782 CLARK               9
    EMPNO ENAME          DEPTNO DNAME

---------- ---------- --------------
     7788 SCOTT              20 RESEARCH
     7839 KING                9
     7844 TURNER             30 SALES
     7876 ADAMS              20 RESEARCH
     7900 JAMES              30 SALES
    EMPNO ENAME          DEPTNO DNAME

---------- ---------- --------------
     7902 FORD               20 RESEARCH
     7934 MILLER              9
                             10 ACCOUNTING
                             40 OPERATIONS

16 rows selected.

Execution Plan


Plan hash value: 1556511399


| Id | Operation | Name | Rows | Bytes | Cost |


| 0 | SELECT STATEMENT | | 28 | 1666 | 7 | | 1 | SORT ORDER BY | | 28 | 1666 | 4 | | 2 | UNION-ALL | | | | | |* 3 | HASH JOIN OUTER | | 14 | 784 | 3 | | 4 | TABLE ACCESS FULL | DEPT | 4 | 92 | 1 | | 5 | TABLE ACCESS FULL | EMP | 14 | 462 | 1 | |* 6 | FILTER | | | | | | 7 | NESTED LOOPS OUTER| | 14 | 882 | 1 | | 8 | TABLE ACCESS FULL| EMP | 14 | 560 | 1 | |* 9 | INDEX UNIQUE SCAN| DEPT_PK | 1 | 23 | |


Predicate Information (identified by operation id):


  3 - access("EMP"."DEPTNO"(+)="DEPT"."DEPTNO")
  6 - filter("DEPT"."DEPTNO" IS NULL)
  9 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+))

Note


  - cpu costing is off (consider enabling it)

SQL> SQL> select empno, ename, nvl(dept.deptno,emp.deptno), dname

 2    from emp FULL OUTER JOIN dept on ( emp.deptno = dept.deptno )
 3   order by 1, 2, 3, 4
 4  /
    EMPNO ENAME      NVL(DEPT.DEPTNO,EMP.DEPTNO) DNAME

---------- --------------------------- --------------
     7369 SMITH                               20 RESEARCH
     7499 ALLEN                               30 SALES
     7521 WARD                                30 SALES
     7566 JONES                               20 RESEARCH
     7654 MARTIN                              30 SALES
     7698 BLAKE                               30 SALES
     7782 CLARK                                9
     7788 SCOTT                               20 RESEARCH
     7839 KING                                 9
     7844 TURNER                              30 SALES
     7876 ADAMS                               20 RESEARCH
    EMPNO ENAME      NVL(DEPT.DEPTNO,EMP.DEPTNO) DNAME

---------- --------------------------- --------------
     7900 JAMES                               30 SALES
     7902 FORD                                20 RESEARCH
     7934 MILLER                               9
                                              10 ACCOUNTING
                                              40 OPERATIONS

16 rows selected.

Execution Plan


Plan hash value: 1591132751


| Id | Operation | Name | Rows | Bytes | Cost |


| 0 | SELECT STATEMENT | | 15 | 825 | 7 | | 1 | SORT ORDER BY | | 15 | 825 | 7 | | 2 | VIEW | | 15 | 825 | 4 | | 3 | UNION-ALL | | | | | |* 4 | HASH JOIN OUTER | | 14 | 882 | 3 | | 5 | TABLE ACCESS FULL| EMP | 14 | 560 | 1 | | 6 | TABLE ACCESS FULL| DEPT | 4 | 92 | 1 | |* 7 | FILTER | | | | | | 8 | TABLE ACCESS FULL| DEPT | 1 | 23 | 1 | |* 9 | TABLE ACCESS FULL| EMP | 5 | 65 | 1 |


Predicate Information (identified by operation id):


  4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+))
  7 - filter( NOT EXISTS (SELECT /*+ UNNEST */ 0 FROM "EMP" "EMP"
             WHERE "EMP"."DEPTNO"=:B1))
  9 - filter("EMP"."DEPTNO"=:B1)

Note


  - cpu costing is off (consider enabling it)

SQL> SQL> set autotrace off SQL> SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped.</source>


analyze and autotrace single column key and multi-column key

   <source lang="sql">

SQL> SQL> create table t

 2  as
 3  select * from all_objects;

Table created. SQL> SQL> create index t_idx1 on t(object_id); Index created. SQL> SQL> create index t_idx2 on t(owner,object_type); Index created. SQL> SQL> analyze table t

 2  compute statistics
 3  for table
 4  for all indexes
 5  for all indexed columns;

Table analyzed. SQL> set autotrace traceonly explain SQL> select object_id, owner, object_type

 2    from t
 3   where object_id between 100 and 2000
 4     and owner = "SYS"
 5  /

Execution Plan


Plan hash value: 1601196873


| Id | Operation | Name | Rows | Bytes | Cost |


| 0 | SELECT STATEMENT | | 938 | 66598 | 25 | |* 1 | TABLE ACCESS FULL| T | 938 | 66598 | 25 |


Predicate Information (identified by operation id):


  1 - filter("OBJECT_ID">=100 AND "OBJECT_ID"<=2000 AND "OWNER"="SYS")

Note


  - cpu costing is off (consider enabling it)

SQL> SQL> set autotrace traceonly SQL> select object_id, owner, object_type

 2    from t
 3   where object_id between 100 and 2000
 4     and owner = "SYS"
 5  /

1250 rows selected.

Execution Plan


Plan hash value: 1601196873


| Id | Operation | Name | Rows | Bytes | Cost |


| 0 | SELECT STATEMENT | | 938 | 66598 | 25 | |* 1 | TABLE ACCESS FULL| T | 938 | 66598 | 25 |


Predicate Information (identified by operation id):


  1 - filter("OBJECT_ID">=100 AND "OBJECT_ID"<=2000 AND "OWNER"="SYS")

Note


  - cpu costing is off (consider enabling it)

Statistics


         0  recursive calls
         0  db block gets
       246  consistent gets
         0  physical reads
         0  redo size
     25173  bytes sent via SQL*Net to client
      1293  bytes received via SQL*Net from client
        85  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
      1250  rows processed

SQL> SQL> select /*+ index( t t_idx1 ) */ object_id, owner, object_type

 2    from t
 3   where object_id between 100 and 2000
 4     and owner = "SYS"
 5  /

1250 rows selected.

Execution Plan


Plan hash value: 4055207394


| Id | Operation | Name | Rows | Bytes | Cost |


| 0 | SELECT STATEMENT | | 938 | 66598 | 32 | |* 1 | TABLE ACCESS BY INDEX ROWID| T | 938 | 66598 | 32 | |* 2 | INDEX RANGE SCAN | T_IDX1 | 1810 | | 5 |


Predicate Information (identified by operation id):


  1 - filter("OWNER"="SYS")
  2 - access("OBJECT_ID">=100 AND "OBJECT_ID"<=2000)

Note


  - cpu costing is off (consider enabling it)

Statistics


         1  recursive calls
         0  db block gets
       195  consistent gets
         0  physical reads
         0  redo size
     25173  bytes sent via SQL*Net to client
      1293  bytes received via SQL*Net from client
        85  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
      1250  rows processed

SQL> SQL> select /*+ index( t t_idx2 ) */ object_id, owner, object_type

 2    from t
 3   where object_id between 100 and 2000
 4     and owner = "SYS"
 5  /

1250 rows selected.

Execution Plan


Plan hash value: 3371054274


| Id | Operation | Name | Rows | Bytes | Cost |


| 0 | SELECT STATEMENT | | 938 | 66598 | 522 | |* 1 | TABLE ACCESS BY INDEX ROWID| T | 938 | 66598 | 522 | |* 2 | INDEX RANGE SCAN | T_IDX2 | 6520 | | 23 |


Predicate Information (identified by operation id):


  1 - filter("OBJECT_ID">=100 AND "OBJECT_ID"<=2000)
  2 - access("OWNER"="SYS")

Note


  - cpu costing is off (consider enabling it)

Statistics


         1  recursive calls
         0  db block gets
       670  consistent gets
         0  physical reads
         0  redo size
     22392  bytes sent via SQL*Net to client
      1293  bytes received via SQL*Net from client
        85  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
      1250  rows processed

SQL> SQL> set autotrace off SQL> SQL> drop table t; Table dropped.</source>


analyze and autotrace table with primary key

   <source lang="sql">

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

Table created. SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> CREATE TABLE DEPT(

 2      DEPTNO NUMBER(2),
 3      DNAME VARCHAR2(14),
 4      LOC VARCHAR2(13)
 5  );

Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> update emp

 2     set deptno = 9
 3   where deptno = 10;

3 rows updated. SQL> SQL> SQL> alter table emp add constraint emp_pk primary key(empno); Table altered. SQL> SQL> alter table dept add constraint dept_pk primary key(deptno); Table altered. SQL> SQL> analyze table emp compute statistics; Table analyzed. SQL> SQL> analyze table dept compute statistics; Table analyzed. SQL> SQL> set autotrace on explain SQL> SQL> select empno, ename, dept.deptno, dname

 2    from emp, dept
 3   where emp.deptno(+) = dept.deptno
 4   UNION ALL
 5  select empno, ename, emp.deptno, null
 6    from emp, dept
 7   where emp.deptno = dept.deptno(+)
 8     and dept.deptno is null
 9   order by 1, 2, 3, 4
10  /
    EMPNO ENAME          DEPTNO DNAME

---------- ---------- --------------
     7369 SMITH              20 RESEARCH
     7499 ALLEN              30 SALES
     7521 WARD                  SALES
     7566 JONES              20 RESEARCH
     7654 MARTIN             30 SALES
     7698 BLAKE                 SALES
     7782 CLARK               9
    EMPNO ENAME          DEPTNO DNAME

---------- ---------- --------------
     7788 SCOTT              20 RESEARCH
     7839 KING                9
     7844 TURNER             30 SALES
     7876 ADAMS              20 RESEARCH
     7900 JAMES              30 SALES
    EMPNO ENAME          DEPTNO DNAME

---------- ---------- --------------
     7902 FORD               20 RESEARCH
     7934 MILLER              9
                             10 ACCOUNTING
                             40 OPERATIONS

16 rows selected.

Execution Plan


Plan hash value: 1556511399


| Id | Operation | Name | Rows | Bytes | Cost |


| 0 | SELECT STATEMENT | | 28 | 1666 | 7 | | 1 | SORT ORDER BY | | 28 | 1666 | 4 | | 2 | UNION-ALL | | | | | |* 3 | HASH JOIN OUTER | | 14 | 784 | 3 | | 4 | TABLE ACCESS FULL | DEPT | 4 | 92 | 1 | | 5 | TABLE ACCESS FULL | EMP | 14 | 462 | 1 | |* 6 | FILTER | | | | | | 7 | NESTED LOOPS OUTER| | 14 | 882 | 1 | | 8 | TABLE ACCESS FULL| EMP | 14 | 560 | 1 | |* 9 | INDEX UNIQUE SCAN| DEPT_PK | 1 | 23 | |


Predicate Information (identified by operation id):


  3 - access("EMP"."DEPTNO"(+)="DEPT"."DEPTNO")
  6 - filter("DEPT"."DEPTNO" IS NULL)
  9 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+))

Note


  - cpu costing is off (consider enabling it)

SQL> SQL> select empno, ename, nvl(dept.deptno,emp.deptno), dname

 2    from emp FULL OUTER JOIN dept on ( emp.deptno = dept.deptno )
 3   order by 1, 2, 3, 4
 4  /
    EMPNO ENAME      NVL(DEPT.DEPTNO,EMP.DEPTNO) DNAME

---------- --------------------------- --------------
     7369 SMITH                               20 RESEARCH
     7499 ALLEN                               30 SALES
     7521 WARD                                30 SALES
     7566 JONES                               20 RESEARCH
     7654 MARTIN                              30 SALES
     7698 BLAKE                               30 SALES
     7782 CLARK                                9
     7788 SCOTT                               20 RESEARCH
     7839 KING                                 9
     7844 TURNER                              30 SALES
     7876 ADAMS                               20 RESEARCH
    EMPNO ENAME      NVL(DEPT.DEPTNO,EMP.DEPTNO) DNAME

---------- --------------------------- --------------
     7900 JAMES                               30 SALES
     7902 FORD                                20 RESEARCH
     7934 MILLER                               9
                                              10 ACCOUNTING
                                              40 OPERATIONS

16 rows selected.

Execution Plan


Plan hash value: 1591132751


| Id | Operation | Name | Rows | Bytes | Cost |


| 0 | SELECT STATEMENT | | 15 | 825 | 7 | | 1 | SORT ORDER BY | | 15 | 825 | 7 | | 2 | VIEW | | 15 | 825 | 4 | | 3 | UNION-ALL | | | | | |* 4 | HASH JOIN OUTER | | 14 | 882 | 3 | | 5 | TABLE ACCESS FULL| EMP | 14 | 560 | 1 | | 6 | TABLE ACCESS FULL| DEPT | 4 | 92 | 1 | |* 7 | FILTER | | | | | | 8 | TABLE ACCESS FULL| DEPT | 1 | 23 | 1 | |* 9 | TABLE ACCESS FULL| EMP | 5 | 65 | 1 |


Predicate Information (identified by operation id):


  4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+))
  7 - filter( NOT EXISTS (SELECT /*+ UNNEST */ 0 FROM "EMP" "EMP"
             WHERE "EMP"."DEPTNO"=:B1))
  9 - filter("EMP"."DEPTNO"=:B1)

Note


  - cpu costing is off (consider enabling it)

SQL> SQL> set autotrace off SQL> SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped.</source>


analyze compute statistics on table with index

   <source lang="sql">

SQL> SQL> create table I1(n number primary key, v varchar2(10)); Table created. SQL> create table I2(n number primary key, v varchar2(10)); Table created. SQL> SQL> create table MAP

 2  (n number primary key,
 3   i1 number referencing I1(n),
 4   i2 number referencing I2(n));

Table created. SQL> SQL> create unique index IDX_MAP on MAP(i1, i2); Index created. SQL> set autotrace traceonly SQL> SQL> select * from i1,map,i2

 2   where     i1.n = map.i1
 3   and i2.n = map.i2
 4   and i1.v = "x"
 5   and i2.v = "y";

no rows selected

Execution Plan


Plan hash value: 3070822050


| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |


| 0 | SELECT STATEMENT | | 1 | 79 | 4 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 79 | 4 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 1 | 59 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | MAP | 1 | 39 | 2 (0)| 00:00:01 | |* 4 | TABLE ACCESS BY INDEX ROWID| I1 | 1 | 20 | 1 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | SYS_C008275 | 1 | | 1 (0)| 00:00:01 | |* 6 | TABLE ACCESS BY INDEX ROWID | I2 | 1 | 20 | 1 (0)| 00:00:01 | |* 7 | INDEX UNIQUE SCAN | SYS_C008276 | 1 | | 1 (0)| 00:00:01 |


Predicate Information (identified by operation id):


  4 - filter("I1"."V"="x")
  5 - access("I1"."N"="MAP"."I1")
  6 - filter("I2"."V"="y")
  7 - access("I2"."N"="MAP"."I2")

Note


  - dynamic sampling used for this statement

Statistics


        44  recursive calls
         0  db block gets
        13  consistent gets
         0  physical reads
         0  redo size
       578  bytes sent via SQL*Net to client
       369  bytes received via SQL*Net from client
         1  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         0  rows processed

SQL> SQL> analyze table i1 compute statistics; Table analyzed. SQL> SQL> analyze table i2 compute statistics; Table analyzed. SQL> SQL> analyze table map compute statistics; Table analyzed. SQL> SQL> select * from i1,map,i2

 2   where     i1.n = map.i1
 3   and i2.n = map.i2
 4   and i1.v = "x"
 5   and i2.v = "y";

no rows selected

Execution Plan


Plan hash value: 1158434662


| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |


| 0 | SELECT STATEMENT | | 1 | 79 | 4 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 79 | 4 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 1 | 59 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL | I1 | 1 | 20 | 2 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| MAP | 1 | 39 | 1 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | IDX_MAP | 1 | | 1 (0)| 00:00:01 | |* 6 | TABLE ACCESS BY INDEX ROWID | I2 | 1 | 20 | 1 (0)| 00:00:01 | |* 7 | INDEX UNIQUE SCAN | SYS_C008276 | 1 | | 1 (0)| 00:00:01 |


Predicate Information (identified by operation id):


  3 - filter("I1"."V"="x")
  5 - access("I1"."N"="MAP"."I1")
  6 - filter("I2"."V"="y")
  7 - access("I2"."N"="MAP"."I2")

Statistics


         1  recursive calls
         0  db block gets
         3  consistent gets
         0  physical reads
         0  redo size
       578  bytes sent via SQL*Net to client
       369  bytes received via SQL*Net from client
         1  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         0  rows processed

SQL> SQL> set autotrace off SQL> SQL> create index i1_idx on i1(v); Index created. SQL> SQL> analyze table i1 compute statistics; Table analyzed. SQL> SQL> set autotrace traceonly SQL> SQL> select * from i1,map,i2

 2  where     i1.n = map.i1
 3  and i2.n = map.i2
 4  and i1.v = "x"
 5  and i2.v = "y";

no rows selected

Execution Plan


Plan hash value: 1388106715


| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |


| 0 | SELECT STATEMENT | | 1 | 79 | 3 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 79 | 3 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 1 | 59 | 2 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| I1 | 1 | 20 | 1 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | I1_IDX | 1 | | 1 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| MAP | 1 | 39 | 1 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | IDX_MAP | 1 | | 1 (0)| 00:00:01 | |* 7 | TABLE ACCESS BY INDEX ROWID | I2 | 1 | 20 | 1 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | SYS_C008276 | 1 | | 1 (0)| 00:00:01 |


Predicate Information (identified by operation id):


  4 - access("I1"."V"="x")
  6 - access("I1"."N"="MAP"."I1")
  7 - filter("I2"."V"="y")
  8 - access("I2"."N"="MAP"."I2")

Statistics


         1  recursive calls
         0  db block gets
         1  consistent gets
         0  physical reads
         0  redo size
       578  bytes sent via SQL*Net to client
       369  bytes received via SQL*Net from client
         1  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         0  rows processed

SQL> SQL> set autotrace off</source>


analyze index t_idx validate structure

   <source lang="sql">

SQL> SQL> SQL> create table t ( x int, y int ); Table created. SQL> SQL> create unique index t_idx on t(x,y); Index created. SQL> SQL> insert into t values ( 1, 1 ); 1 row created. SQL> insert into t values ( 1, NULL ); 1 row created. SQL> insert into t values ( NULL, 1 ); 1 row created. SQL> insert into t values ( NULL, NULL ); 1 row created. SQL> SQL> analyze index t_idx validate structure; Index analyzed. SQL> SQL> select name, lf_rows from index_stats; NAME LF_ROWS


----------

T_IDX 3 SQL> SQL> insert into t values ( NULL, NULL ); 1 row created. SQL> insert into t values ( NULL, 1 ); insert into t values ( NULL, 1 )

ERROR at line 1: ORA-00001: unique constraint (sqle.T_IDX) violated

SQL> insert into t values ( 1, NULL ); insert into t values ( 1, NULL )

ERROR at line 1: ORA-00001: unique constraint (sqle.T_IDX) violated

SQL> SQL> select x, y, count(*) from t group by x,y having count(*) > 1;

        X          Y   COUNT(*)

---------- ----------
                              2

SQL> SQL> drop table t; Table dropped. SQL> SQL> create table t ( x int, y int NOT NULL ); Table created. SQL> create unique index t_idx on t(x,y); Index created. SQL> SQL> insert into t values ( 1, 1 ); 1 row created. SQL> insert into t values ( NULL, 1 ); 1 row created. SQL> SQL> begin

 2    dbms_stats.gather_table_stats(user,"T");
 3  end;
 4  /

PL/SQL procedure successfully completed. SQL> set autotrace on SQL> SQL> select * from t where x is null;

        X          Y

----------
                   1

Execution Plan


Plan hash value: 2946670127


| Id | Operation | Name | Rows | Bytes | Cost |


| 0 | SELECT STATEMENT | | 1 | 5 | 1 | |* 1 | INDEX RANGE SCAN| T_IDX | 1 | 5 | 1 |


Predicate Information (identified by operation id):


  1 - access("X" IS NULL)

Note


  - cpu costing is off (consider enabling it)

Statistics


         1  recursive calls
         0  db block gets
         1  consistent gets
         0  physical reads
         0  redo size
       456  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)
         1  rows processed

SQL> SQL> set autotrace off SQL> SQL> drop table t; Table dropped. SQL></source>


analyze table after creating index

   <source lang="sql">

SQL> SQL> SQL> SQL> create table myTable1 as select *

 2    from all_objects where ROWNUM <=000;

Table created. SQL> SQL> SQL> SQL> SQL> create table myTable2 as select *

 2   from all_objects where ROWNUM <= 9950;

Table created. SQL> SQL> SQL> SQL> SQL> create index myTable2_idx on myTable2(object_id); Index created. SQL> SQL> SQL> select count(*) from myTable1 rbo

 2   where object_id not in ( select object_id from myTable2 )
 3   /
 COUNT(*)

        0

SQL> SQL> SQL> SQL> select count(*) from myTable1 rbo

 2    where NOT EXISTS (select null from myTable2 where myTable2.object_id = rbo.object_id )
 3   /
 COUNT(*)

        0

SQL> SQL> SQL> SQL> SQL> select count(*) from myTable1, myTable2 rbo

 2    where myTable1.object_id = rbo.object_id(+) and rbo.object_id IS NULL
 3   /
 COUNT(*)

        0

SQL> SQL> SQL> SQL> SQL> analyze table myTable1 compute statistics; Table analyzed. SQL> SQL> SQL> SQL> analyze table myTable2 compute statistics; Table analyzed. SQL> SQL> SQL> SQL> SQL> select count(*) from myTable1 cbo

 2   where object_id not in ( select object_id from myTable2 )
 3   /
 COUNT(*)

        0

SQL> SQL> SQL> SQL> SQL> select count(*) from myTable1 cbo

 2   where object_id not in ( select object_id from myTable2 )
 3   /
 COUNT(*)

        0

SQL> SQL> SQL> SQL> SQL> select count(*) from myTable1 cbo

 2   where object_id not in ( select object_id from myTable2 )
 3   /
 COUNT(*)

        0

SQL> SQL> SQL> SQL> SQL> alter table myTable2 modify object_id null; Table altered. SQL> SQL> SQL> SQL> select count(*)

 2   from
 3    myTable1 cbo where object_id not in ( select object_id from myTable2 )
 4   /
 COUNT(*)

        0

SQL> SQL> SQL> SQL> SQL> drop table myTable1; Table dropped. SQL> SQL> SQL> SQL> drop table myTable2; Table dropped. SQL> SQL> SQL> SQL></source>


analyze table compute statistics

   <source lang="sql">

SQL> SQL> SQL> create table myTable1 as select * from all_objects where rownum < 50; Table created. SQL> create table myTable2 as select * from all_objects where rownum <= 50; Table created. SQL> SQL> alter table myTable1 add constraint myTable1_pk primary key(object_id); Table altered. SQL> alter table myTable2 add constraint myTable2_pk primary key(object_id); Table altered. SQL> SQL> analyze table myTable1 compute statistics

 2  for table for all indexes for all indexed columns;

Table analyzed. SQL> SQL> analyze table myTable2 compute statistics

 2  for table for all indexes for all indexed columns;

Table analyzed. SQL> SQL> create or replace function get_data( p_object_id in number ) return varchar2

 2  is
 3      l_object_name myTable2.object_name%type;
 4  begin
 5      select object_name into l_object_name
 6        from myTable2
 7       where object_id = p_object_id;
 8      return l_object_name;
 9  exception
10      when no_data_found then
11          return NULL;
12  end;
13  /

Function created. SQL> SQL> select a.object_id, a.object_name oname1, b.object_name oname2

 2    from myTable1 a, myTable2 b
 3   where a.object_id = b.object_id(+);
OBJECT_ID ONAME1                         ONAME2

------------------------------ ------------------------------
       20 ICOL$                          ICOL$
       44 I_USER1                        I_USER1
       28 CON$                           CON$
       15 UNDO$                          UNDO$
       29 C_COBJ#                        C_COBJ#
        3 I_OBJ#                         I_OBJ#
       25 PROXY_ROLE_DATA$               PROXY_ROLE_DATA$
       39 I_IND1                         I_IND1
       51 I_CDEF2                        I_CDEF2
       26 I_PROXY_ROLE_DATA$_1           I_PROXY_ROLE_DATA$_1
       17 FILE$                          FILE$
OBJECT_ID ONAME1                         ONAME2

------------------------------ ------------------------------
       13 UET$                           UET$
        9 I_FILE#_BLOCK#                 I_FILE#_BLOCK#
       41 I_FILE1                        I_FILE1
       48 I_CON1                         I_CON1
       38 I_OBJ3                         I_OBJ3
        7 I_TS#                          I_TS#
       53 I_CDEF4                        I_CDEF4
       19 IND$                           IND$
       14 SEG$                           SEG$
        6 C_TS#                          C_TS#
       42 I_FILE2                        I_FILE2
OBJECT_ID ONAME1                         ONAME2

------------------------------ ------------------------------
       21 COL$                           COL$
       43 I_TS1                          I_TS1
       35 I_UNDO2                        I_UNDO2
        5 CLU$                           CLU$
       23 PROXY_DATA$                    PROXY_DATA$
       24 I_PROXY_DATA$                  I_PROXY_DATA$
       36 I_OBJ1                         I_OBJ1
       46 I_COL2                         I_COL2
       37 I_OBJ2                         I_OBJ2
       54 I_CCOL1                        I_CCOL1
       16 TS$                            TS$
OBJECT_ID ONAME1                         ONAME2

------------------------------ ------------------------------
        8 C_FILE#_BLOCK#                 C_FILE#_BLOCK#
       10 C_USER#                        C_USER#
       34 I_UNDO1                        I_UNDO1
       56 BOOTSTRAP$                     BOOTSTRAP$
       12 FET$                           FET$
       33 I_TAB1                         I_TAB1
       32 CCOL$                          CCOL$
       22 USER$                          USER$
       49 I_CON2                         I_CON2
       30 I_COBJ#                        I_COBJ#
       18 OBJ$                           OBJ$
OBJECT_ID ONAME1                         ONAME2

------------------------------ ------------------------------
       47 I_COL3                         I_COL3
        2 C_OBJ#                         C_OBJ#
        4 TAB$                           TAB$
       31 CDEF$                          CDEF$
       50 I_CDEF1                        I_CDEF1

49 rows selected. SQL> SQL> select object_id, object_name oname1, get_data(object_id) oname2

 2    from myTable1;
OBJECT_ID ONAME1

------------------------------

ONAME2


       20 ICOL$

ICOL$

       44 I_USER1

I_USER1

       28 CON$

CON$

OBJECT_ID ONAME1

------------------------------

ONAME2


       15 UNDO$

UNDO$

       29 C_COBJ#

C_COBJ#

        3 I_OBJ#

I_OBJ#

OBJECT_ID ONAME1

------------------------------

ONAME2


       25 PROXY_ROLE_DATA$

PROXY_ROLE_DATA$

       39 I_IND1

I_IND1

       51 I_CDEF2

I_CDEF2

OBJECT_ID ONAME1

------------------------------

ONAME2


       26 I_PROXY_ROLE_DATA$_1

I_PROXY_ROLE_DATA$_1

       17 FILE$

FILE$

       13 UET$

UET$

OBJECT_ID ONAME1

------------------------------

ONAME2


        9 I_FILE#_BLOCK#

I_FILE#_BLOCK#

       41 I_FILE1

I_FILE1

       48 I_CON1

I_CON1

OBJECT_ID ONAME1

------------------------------

ONAME2


       38 I_OBJ3

I_OBJ3

        7 I_TS#

I_TS#

       53 I_CDEF4

I_CDEF4

OBJECT_ID ONAME1

------------------------------

ONAME2


       19 IND$

IND$

       14 SEG$

SEG$

        6 C_TS#

C_TS#

OBJECT_ID ONAME1

------------------------------

ONAME2


       42 I_FILE2

I_FILE2

       21 COL$

COL$

       43 I_TS1

I_TS1

OBJECT_ID ONAME1

------------------------------

ONAME2


       35 I_UNDO2

I_UNDO2

        5 CLU$

CLU$

       23 PROXY_DATA$

PROXY_DATA$

OBJECT_ID ONAME1

------------------------------

ONAME2


       24 I_PROXY_DATA$

I_PROXY_DATA$

       36 I_OBJ1

I_OBJ1

       46 I_COL2

I_COL2

OBJECT_ID ONAME1

------------------------------

ONAME2


       37 I_OBJ2

I_OBJ2

       54 I_CCOL1

I_CCOL1

       16 TS$

TS$

OBJECT_ID ONAME1

------------------------------

ONAME2


        8 C_FILE#_BLOCK#

C_FILE#_BLOCK#

       10 C_USER#

C_USER#

       34 I_UNDO1

I_UNDO1

OBJECT_ID ONAME1

------------------------------

ONAME2


       56 BOOTSTRAP$

BOOTSTRAP$

       12 FET$

FET$

       33 I_TAB1

I_TAB1

OBJECT_ID ONAME1

------------------------------

ONAME2


       32 CCOL$

CCOL$

       22 USER$

USER$

       49 I_CON2

I_CON2

OBJECT_ID ONAME1

------------------------------

ONAME2


       30 I_COBJ#

I_COBJ#

       18 OBJ$

OBJ$

       47 I_COL3

I_COL3

OBJECT_ID ONAME1

------------------------------

ONAME2


        2 C_OBJ#

C_OBJ#

        4 TAB$

TAB$

       31 CDEF$

CDEF$

OBJECT_ID ONAME1

------------------------------

ONAME2


       50 I_CDEF1

I_CDEF1

49 rows selected. SQL> SQL> begin

 2
 3      for x in ( select a.object_id,
 4                        a.object_name oname1,
 5                        b.object_name oname2
 6                   from myTable1 a, myTable2 b
 7                  where a.object_id = b.object_id(+) )
 8      loop
 9          null;
10      end loop;
11
12      for x in ( select object_id,
13                        object_name oname1,
14                        get_data(object_id) oname2
15                   from myTable1 )
16      loop
17          null;
18      end loop;
19
20  end;
21  /

PL/SQL procedure successfully completed. SQL> SQL> drop table myTable1; Table dropped. SQL> drop table myTable2; Table dropped. SQL></source>


analyze table compute statistics for table for all indexes for all indexed columns

   <source lang="sql">

SQL> SQL> SQL> create table myTable as

 2  select decode( mod(rownum,100), 0, "N", "Y" ) processed, a.*
 3    from all_objects a;

Table created. SQL> SQL> create index processed_idx on myTable(processed); Index created. SQL> SQL> select * from myTable where status = "N"; no rows selected SQL> SQL> SQL> analyze table myTable compute statistics

 2  for table
 3  for all indexes
 4  for all indexed columns
 5  /

Table analyzed. SQL> SQL> variable processed varchar2(1); SQL> exec :processed := "N" PL/SQL procedure successfully completed. SQL> SQL> set autotrace traceonly explain SQL> SQL> select *

 2    from myTable
 3   where processed = "N";

Execution Plan


Plan hash value: 362789735



| Id | Operation | Name | Rows | Bytes | Cost (%CPU )| Time |



| 0 | SELECT STATEMENT | | 126 | 11466 | 4 (0 )| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| MYTABLE | 126 | 11466 | 4 (0 )| 00:00:01 | |* 2 | INDEX RANGE SCAN | PROCESSED_IDX | 126 | | 1 (0 )| 00:00:01 |



Predicate Information (identified by operation id):


  2 - access("PROCESSED"="N")

SQL> SQL> SQL> select *

 2    from myTable
 3   where processed = :processed;

Execution Plan


Plan hash value: 1015944200


| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |


| 0 | SELECT STATEMENT | | 6328 | 562K| 47 (3)| 00:00:01 | |* 1 | TABLE ACCESS FULL| MYTABLE | 6328 | 562K| 47 (3)| 00:00:01 |


Predicate Information (identified by operation id):


  1 - filter("PROCESSED"=:PROCESSED)

SQL> SQL> drop table myTable; Table dropped. SQL> SQL> set autotrace off SQL> SQL></source>


analyze table estimate statistics

   <source lang="sql">

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


analyze table students compute statistics

   <source lang="sql">

SQL> SQL> create table students

 2  ( studentID number constraint students_pk primary key,
 3    name varchar2(10) );

Table created. SQL> SQL> create table documentMaster

 2  ( documentId number constraint document_pk primary key,
 3    description varchar2(10) );

Table created. SQL> SQL> create table admission_docs

 2  ( studentID references students,
 3    documentId references documentMaster,
 4    dt date,
 5    constraint admission_pk primary key(studentID, documentId));

Table created. SQL> SQL> SQL> insert into students

 2  select object_id, object_name
 3    from all_objects;

SQL> SQL> SQL> insert into documentMaster

 2  select ROWNUM, "doc " || ROWNUM
 3    from all_users
 4   where ROWNUM <= 5;

5 rows created. SQL> SQL> insert into admission_docs

 2  select object_id, mod(ROWNUM,3)+1, created
 3    from all_objects, (select 1 from all_users where ROWNUM <= 3);

SQL> SQL> SQL> analyze table students compute statistics

 2  for table for all indexes for all indexed columns;

Table analyzed. SQL> SQL> analyze table documentMaster compute statistics

 2  for table for all indexes for all indexed columns;

Table analyzed. SQL> SQL> analyze table admission_docs compute statistics

 2  for table for all indexes for all indexed columns;

Table analyzed. SQL> SQL> SQL> set autotrace on SQL> variable bv number SQL> exec :bv := 1234 PL/SQL procedure successfully completed. SQL> SQL> select a.* , decode(b.dt,null,"No","Yes") submitted, b.dt

 2   from (
 3  select *
 4    from students, documentMaster
 5   where students.studentID = :bv
 6        ) a, admission_docs b
 7   where a.studentID = b.studentID(+)
 8     and a.documentId = b.documentId (+)
 9  /

no rows selected

Execution Plan


Plan hash value: 3775454522


- | Id | Operation | Name | Rows | Bytes | Cost |


- | 0 | SELECT STATEMENT | | 5 | 550 | 2 | | 1 | NESTED LOOPS OUTER | | 5 | 550 | 2 | | 2 | VIEW | | 5 | 330 | 2 | | 3 | NESTED LOOPS | | 5 | 225 | 2 | | 4 | TABLE ACCESS BY INDEX ROWID| STUDENTS | 1 | 33 | 1 | |* 5 | INDEX UNIQUE SCAN | STUDENTS_PK | 1 | | | | 6 | TABLE ACCESS FULL | DOCUMENTMASTER | 5 | 60 | 1 | | 7 | TABLE ACCESS BY INDEX ROWID | ADMISSION_DOCS | 1 | 44 | | |* 8 | INDEX UNIQUE SCAN | ADMISSION_PK | 1 | | |


-

Predicate Information (identified by operation id):


  5 - access("STUDENTS"."STUDENTID"=TO_NUMBER(:BV))
  8 - access("A"."STUDENTID"="B"."STUDENTID"(+) AND
             "A"."DOCUMENTID"="B"."DOCUMENTID"(+))

Note


  - cpu costing is off (consider enabling it)

Statistics


         1  recursive calls
         0  db block gets
         1  consistent gets
         0  physical reads
         0  redo size
       564  bytes sent via SQL*Net to client
       369  bytes received via SQL*Net from client
         1  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         0  rows processed

SQL> SQL> set autotrace off SQL> SQL> drop table students cascade constraints; Table dropped. SQL> SQL> drop table documentMaster cascade constraints; Table dropped. SQL> SQL> drop table admission_docs; Table dropped.</source>


analyze table TABLENAME compute statistics;

   <source lang="sql">

SQL> analyze table TABLENAME compute statistics;

SQL> SQL> select Num_Rows, /*number of rows*/

 2         Blocks,                /*number of blocks used*/
 3         Num_Rows/Blocks        /*number of rows per block*/
 4    from USER_TABLES
 5   where Table_Name="TABLENAME";

SQL></source>


analyze table t compute statistics for table, for all indexes, for all indexed columns

   <source lang="sql">

SQL> SQL> create table t

 2  as
 3  select to_char( to_date("01-jan-1995","dd-mon-yyyy")+rownum, "yyyymmdd" ) str_date,
 4         to_date("01-jan-1995","dd-mon-yyyy")+rownum date_date
 5    from all_objects
 6    where rownum < 20
 7  /

Table created. SQL> SQL> create index i1 on t(str_date); Index created. SQL> SQL> create index t_date_date_idx on t(date_date); Index created. SQL> SQL> analyze table t compute statistics

 2  for table
 3  for all indexes
 4  for all indexed columns;

Table analyzed. SQL> SQL> set autotrace on explain SQL> select * from t

 2  where str_date between "20001231" and "20100101" and rownum < 20;

no rows selected

Execution Plan


Plan hash value: 508354683


| Id | Operation | Name | Rows | Bytes | Cost |


| 0 | SELECT STATEMENT | | 1 | 20 | 1 | |* 1 | COUNT STOPKEY | | | | | |* 2 | TABLE ACCESS FULL| T | 1 | 20 | 1 |


Predicate Information (identified by operation id):


  1 - filter(ROWNUM<20)
  2 - filter("STR_DATE">="20001231" AND "STR_DATE"<="20100101")

Note


  - cpu costing is off (consider enabling it)

SQL> SQL> select * from t where date_date between to_date("20001231","yyyymmdd") and to_date("20100101","yyyymmdd") and rownum<20; no rows selected

Execution Plan


Plan hash value: 508354683


| Id | Operation | Name | Rows | Bytes | Cost |


| 0 | SELECT STATEMENT | | 1 | 20 | 1 | |* 1 | COUNT STOPKEY | | | | | |* 2 | TABLE ACCESS FULL| T | 1 | 20 | 1 |


Predicate Information (identified by operation id):


  1 - filter(ROWNUM<20)
  2 - filter("DATE_DATE">=TO_DATE("2000-12-31 00:00:00", "yyyy-mm-dd
             hh24:mi:ss") AND "DATE_DATE"<=TO_DATE("2010-01-01 00:00:00",
             "yyyy-mm-dd hh24:mi:ss"))

Note


  - cpu costing is off (consider enabling it)

SQL> SQL> SQL> select * from t

 2  where str_date between "20001231" and "20100101" and rownum<20;

no rows selected

Execution Plan


Plan hash value: 508354683


| Id | Operation | Name | Rows | Bytes | Cost |


| 0 | SELECT STATEMENT | | 1 | 20 | 1 | |* 1 | COUNT STOPKEY | | | | | |* 2 | TABLE ACCESS FULL| T | 1 | 20 | 1 |


Predicate Information (identified by operation id):


  1 - filter(ROWNUM<20)
  2 - filter("STR_DATE">="20001231" AND "STR_DATE"<="20100101")

Note


  - cpu costing is off (consider enabling it)

SQL> SQL> select * from t where date_date between to_date("20001231","yyyymmdd") and to_date("20100101","yyyymmdd")

 2  and rownum < 20;

no rows selected

Execution Plan


Plan hash value: 508354683


| Id | Operation | Name | Rows | Bytes | Cost |


| 0 | SELECT STATEMENT | | 1 | 20 | 1 | |* 1 | COUNT STOPKEY | | | | | |* 2 | TABLE ACCESS FULL| T | 1 | 20 | 1 |


Predicate Information (identified by operation id):


  1 - filter(ROWNUM<20)
  2 - filter("DATE_DATE">=TO_DATE("2000-12-31 00:00:00", "yyyy-mm-dd
             hh24:mi:ss") AND "DATE_DATE"<=TO_DATE("2010-01-01 00:00:00",
             "yyyy-mm-dd hh24:mi:ss"))

Note


  - cpu costing is off (consider enabling it)

SQL> SQL> set autotrace off SQL> SQL> drop table t; Table dropped.</source>


analyze table t compute statistics for table for columns id;

   <source lang="sql">

SQL> create table t as select mod(object_id,10) id, a.* from all_objects a; SQL> SQL> analyze table t compute statistics

 2  for table
 3  for columns id;

SQL> SQL> SQL> SQL> alter session set optimizer_max_permutations=80000; SQL> SQL> explain plan for

 2  select count(*)
 3    from t t1, t t2, t t3, t t4, t t5, t t6
 4   where t1.id = t2.id
 5     and t1.id = t3.id
 6     and t1.id = t4.id
 7     and t1.id = t5.id
 8     and t1.id = t6.id;

SQL> SQL> set autotrace off SQL> SQL> SQL> drop table t;</source>


Analyze table with user defined column type

   <source lang="sql">

SQL> SQL> create or replace type address_type as object

 2    ( city    varchar2(30),
 3      street  varchar2(30),
 4      state   varchar2(2),
 5      zip     number
 6    )
 7  /

Type created. SQL> create or replace type person_type as object

 2    ( name             varchar2(30),
 3      dob              date,
 4      home_address     address_type,
 5      work_address     address_type
 6    )
 7  /

Type created. SQL> create table people of person_type

 2  /

Table created. SQL> insert into people values ( "Tom", "15-mar-1965",

 2    address_type( "R", "1 Street", "Va", "45678" ),
 3    address_type( "A", "1 Way", "Ca", "23456" ) );

1 row created. SQL> / 1 row created. SQL> SQL> analyze table people compute statistics; Table analyzed. SQL> SQL> drop table people; Table dropped. SQL> SQL> drop type person_type; Type dropped. SQL> drop type address_type; Type dropped. SQL></source>