Oracle PL/SQL/SQL Plus/Analyze — различия между версиями

Текущая версия на 09:58, 26 мая 2010

Содержание

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

analyze and autotrace full outer join and union

  
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.

analyze and autotrace single column key and multi-column key

  
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.

analyze and autotrace table with primary key

  
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.

Analyze a table with/without index

  

create table indextest as 
select * from dba_objects where owner in ("OUTLN","PUBLIC","SCOTT","SYS","SYSTEM");

analyze table indextest compute statistics;
set autotrace trace explain
select owner, object_name from indextest where object_name = "DBA_INDEXES";
create index indxtest_objname_idx on indextest (object_name);
select owner, object_name from indextest where object_name = "DBA_INDEXES";
set autotrace off;
drop table indextest;

analyze index

  
SQL>
SQL> create table indextest as select * from dba_objects
  2  where owner in ("OUTLN","PUBLIC","SCOTT","SYS","SYSTEM");
Table created.
SQL>
SQL>
SQL>  create index indxtest_objname_idx
  2   on indextest (object_name)
  3   pctfree 0;
Index created.
SQL>
SQL>  analyze table indextest compute statistics;
Table analyzed.
SQL>
SQL>  analyze index indxtest_objname_idx validate structure;
Index analyzed.
SQL>
SQL>  select name, height, lf_blks, pct_used
  2   from index_stats;
NAME                               HEIGHT    LF_BLKS   PCT_USED
------------------------------ ---------- ---------- ----------
INDXTEST_OBJNAME_IDX                    2         36         97
SQL>
SQL>
SQL>  insert into indextest (owner, object_name)
  2   values ("a","a");
1 row created.
SQL>
SQL>  commit;
Commit complete.
SQL>
SQL>  analyze index indxtest_objname_idx validate structure;
Index analyzed.
SQL>
SQL>
SQL>  select name, height, lf_blks, pct_used
  2   from index_stats;
NAME                               HEIGHT    LF_BLKS   PCT_USED
------------------------------ ---------- ---------- ----------
INDXTEST_OBJNAME_IDX                    2         36         97
SQL>
SQL>  insert into indextest (owner, object_name) values ("ZZZZZ","_ZZZZZZZZZZZ");
1 row created.
SQL>
SQL>  commit;
Commit complete.
SQL>
SQL>
SQL>  analyze index indxtest_objname_idx validate structure;
Index analyzed.
SQL>
SQL>
SQL>  select name, height, lf_blks, pct_used
  2   from index_stats;
NAME                               HEIGHT    LF_BLKS   PCT_USED
------------------------------ ---------- ---------- ----------
INDXTEST_OBJNAME_IDX                    2         36         97
SQL>
SQL>  alter index indxtest_objname_idx rebuild pctfree 10;
Index altered.
SQL>
SQL>  analyze index indxtest_objname_idx validate structure;
Index analyzed.
SQL>
SQL> drop table indextest;
Table dropped.
SQL>
SQL>

Analyze index on varchar2 value and date type value

  
  
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.

analyze index t_idx validate structure

  
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>

analyze index validate structure

  
SQL>
SQL>
SQL> set serveroutput on
SQL> set echo on
SQL> create table t ( x int, y int );
Table created.
SQL> create unique index t_idx on t(x,y);
Index created.
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> analyze index t_idx validate structure;
Index analyzed.
SQL> select name, lf_rows from index_stats;
NAME                    LF_ROWS
-------------------- ----------
T_IDX                         3
1 row selected.
SQL> insert into t values ( NULL, NULL );
1 row created.
SQL> insert into t values ( NULL, 1 );
SQL> insert into t values ( 1, NULL );

SQL> select x, y, count(*) from t group by x,y having count(*) > 1;
         X          Y   COUNT(*)
---------- ---------- ----------
                               2
1 row selected.
SQL>
SQL> drop table t;
Table dropped.
SQL> drop index t_idx;
SQL>
SQL> --

analyze materialized view

  
SQL>
SQL> create table my_all_objects
  2  nologging
  3  as
  4  select * from all_objects
  5  union all
  6  select * from all_objects
  7  union all
  8  select * from all_objects
  9  /
Table created.
SQL>
SQL>
SQL> alter session set query_rewrite_enabled=true;
Session altered.
SQL>
SQL> alter session set query_rewrite_integrity=enforced;
Session altered.
SQL>
SQL> create materialized view my_all_objects_aggs
  2  build immediate
  3  refresh on commit
  4  enable query rewrite
  5  as
  6  select owner, count(*)
  7    from my_all_objects
  8   group by owner
  9  /

SQL>
SQL> analyze table my_all_objects_aggs compute statistics;
SQL>
SQL> drop table my_all_objects;
Table dropped.
SQL>
SQL>

analyze table after creating index

  
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>

analyze table compute statistics

  
SQL>
SQL> set serveroutput on
SQL> set echo on
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> insert into t values ( 1, 1 );
1 row created.
SQL> insert into t values ( NULL, 1 );
1 row created.
SQL> analyze table t compute statistics;
Table analyzed.
SQL> set autotrace on
SQL> drop table t;
Table dropped.

SQL>
SQL> set autotrace off
SQL> --

analyze table compute statistics for all indexes

  
SQL>  set serveroutput on
SQL>  set echo on
SQL>
SQL>
SQL>  create table t
  2     ( x, y null, primary key (x) )
  3     as
  4     select rownum x, username
  5      from all_users
  6      where rownum <= 100;
Table created.
SQL>
SQL>  analyze table t compute statistics;
Table analyzed.
SQL>
SQL>
SQL>  analyze table t compute statistics for all indexes;
Table analyzed.
SQL>
SQL>
SQL>  set autotrace on explain
SQL>
SQL>  select count(y) from t where x < 50;
  COUNT(Y)
----------
        14
1 row selected.

Execution Plan
--------------------------------------------------
Plan hash value: 2966233522
--------------------------------------------------
-------------------------
| Id  | Operation          | Name | Rows  | Bytes
| Cost (%CPU)| Time     |
--------------------------------------------------
-------------------------
|   0 | SELECT STATEMENT   |      |     1 |     8
|     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     8
|            |          |
|*  2 |   TABLE ACCESS FULL| T    |    14 |   112
|     2   (0)| 00:00:01 |
--------------------------------------------------
-------------------------

Predicate Information (identified by operation id)
:
--------------------------------------------------
-

   2 - filter("X"<50)
SQL>
SQL>  drop table t;
Table dropped.
SQL>
SQL>  set autotrace off
SQL> --

analyze table compute statistics for a table with two indexes

  
SQL>
SQL> set echo on
SQL>
SQL> create table t as
  2  select * from all_objects;
Table created.
SQL>
SQL> create index t_idx1 on t(object_name);
Index created.
SQL>
SQL> create index t_idx2 on t(object_type);
Index created.
SQL>
SQL> analyze table t compute statistics
  2  for all indexed columns
  3  for table;
Table analyzed.
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL> --

ANALYZE TABLE employee COMPUTE STATISTICS

  
SQL>
SQL> create table employee(
  2           emp_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          ,salary                 number(3)
 14          ,birthdate              date
 15          ,hiredate               date
 16          ,title                  varchar2(20)
 17          ,dept_no                integer
 18          ,mgr                    integer
 19          ,region                 number
 20          ,division               number
 21          ,total_sales            number
 22  );
Table created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (1,"Anderson","Nancy","N","33 Ave","London","NY","11111","1111","212","234-1111",3.75,"21-mar-1927","1-feb-1947","Sales Manager",2,null,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (2,"Last","First","F","12 Ave","Paris","CA","22222","2222","221","867-2222",7.75,"14-feb-1976","15-mar-1985","Sales Clerk",2,1,100,10,10000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (3,"Wash","Georgia","G","1 Street14","Barton","NJ","33333","3333","214","340-3333",11.50,"2-jul-1977","21-apr-2004","Designer",1,2,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (4,"Bush","Dave","D","56 Street","Island","RI","44444","4444","215","777-4444",21.65,"15-may-1945","2-aug-1975","Designer",1,2,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (5,"Will","Robin","W","56 Street","Island","MA","55555","5555","216","777-5555",24.65,"10-dec-1980","2-aug-2007","Designer",1,5,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (6,"Pete","Mona","M","13 Ave","York","MO","66666","6666","217","111-6666",9,"14-feb-1966","15-mar-1985","Sales Clerk",2,5,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (7,"Roke","John","J","67 Ave","New York","BC","77777","7777","218","122-7777",10.00,"14-jun-1955","15-mar-1975","Accountant",3,2,100,10,40000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (8,"Horry","Tedi","T","1236 Lane","Newton","NY","88888","8888","219","222-8888",13.00,"10-jun-1955","15-aug-1985","Sales Representative",3,2,100,10,50000);
1 row created.
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2                values (9,"Bar","Candi","C","400 East Street","Yorken","NY","99999","9999","220","321-9999",12.00,"10-oct-1933","15-jan-1969","Sales Representative",3,5,100,10,35000);
1 row created.
SQL>
SQL>
SQL> Set autotrace on
SQL>
SQL> SELECT region, SUM(total_sales)
  2  FROM employee
  3  GROUP BY region;
    REGION SUM(TOTAL_SALES)
---------- ----------------
       100           335000
1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1624656943
-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     9 |   234 |     3  (34)| 00:00:01 |
|   1 |  HASH GROUP BY     |          |     9 |   234 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEE |     9 |   234 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
         48  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        479  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> ANALYZE TABLE employee COMPUTE STATISTICS;
Table analyzed.
SQL>
SQL> drop table employee;
Table dropped.
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

analyze table students compute statistics

  
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.

analyze table TABLENAME compute statistics;

  

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>

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

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

  
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.

analyze table t compute statistics for table for columns id;

  
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;

Analyze table with primary key and foreign key

  
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));
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 (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
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> analyze table emp compute statistics;
Table analyzed.
SQL> analyze table dept compute statistics;
Table analyzed.
SQL>
SQL> alter table emp  add constraint emp_pk      primary key(empno);
Table altered.
SQL> alter table dept add constraint dept_pk     primary key(deptno);
Table altered.
SQL> alter table emp  add constraint emp_fk_dept foreign key (deptno) references dept;
Table altered.
SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> select * from emp, dept where emp.deptno = dept.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 3487251775
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   700 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |    14 |   700 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | EMP     |    14 |   448 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    18 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | DEPT_PK |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
SQL>
SQL> analyze table emp compute statistics;
Table analyzed.
SQL> analyze table dept compute statistics;
Table analyzed.
SQL>
SQL> select * from emp, dept where emp.deptno = dept.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 3487251775
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   700 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |    14 |   700 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | EMP     |    14 |   448 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    18 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | DEPT_PK |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
SQL>
SQL> set autotrace off
SQL>
SQL> drop table emp cascade constraints;
Table dropped.
SQL> drop table dept cascade constraints;
Table dropped.
SQL>

Anayze all objects in the APPOWNER schema.

  

SQL>
SQL> execute DBMS_UTILITY.ANALYZE_SCHEMA("APPOWNER","COMPUTE");
SQL>
SQL>

create or replace outline and analyze table compute statistics

  
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));
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>
SQL>
SQL> create or replace outline MyOutline
  2  for category mycategory
  3  ON
  4  select empno, ename from emp where empno > 0
  5  /
Outline created.
SQL>
SQL>
SQL> analyze table emp compute statistics
  2  /
Table analyzed.
SQL>
SQL> drop table emp;
Table dropped.
SQL>

Demonstrate IN versus EXISTS performance

  
SQL>
SQL> create table big as select * from all_objects;
Table created.
SQL>
SQL> insert into big select * from all_objects;
12217 rows created.
SQL> insert into big select * from all_objects;
12217 rows created.
SQL> insert into big select * from all_objects;
12217 rows created.
SQL> insert into big select * from all_objects;
12217 rows created.
SQL> insert into big select * from all_objects;
12217 rows created.
SQL> insert into big select * from all_objects;
12217 rows created.
SQL>
SQL> create index big_idx on big(object_id);
Index created.
SQL>
SQL> create table small as select * from all_objects where rownum < 100;
Table created.
SQL> create index small_idx on small(object_id);
Index created.
SQL>
SQL> analyze table big compute statistics
  2   for table
  3   for all indexes
  4   for all indexed columns;
Table analyzed.
SQL>
SQL> analyze table small compute statistics
  2   for table
  3   for all indexes
  4   for all indexed columns;
Table analyzed.
SQL>
SQL> drop table big;
Table dropped.
SQL> drop table small;
Table dropped.
SQL>

Oracle PL/SQL/SQL Plus/Analyze — различия между версиями

Текущая версия на 09:58, 26 мая 2010

Содержание

analyze and autotrace full outer join and union

analyze and autotrace single column key and multi-column key

analyze and autotrace table with primary key

Analyze a table with/without index

analyze index

Analyze index on varchar2 value and date type value

analyze index t_idx validate structure

analyze index validate structure

analyze materialized view

analyze table after creating index

analyze table compute statistics

analyze table compute statistics for all indexes

analyze table compute statistics for a table with two indexes

ANALYZE TABLE employee COMPUTE STATISTICS

analyze table estimate statistics

analyze table students compute statistics

analyze table TABLENAME compute statistics;

analyze table tableName compute statistics for table, for all indexes, for all indexed columns;

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

analyze table t compute statistics for table for columns id;

Analyze table with primary key and foreign key

Anayze all objects in the APPOWNER schema.

create or replace outline and analyze table compute statistics

Demonstrate IN versus EXISTS performance

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты

Текущая версия на 09:58, 26 мая 2010 (просмотреть исходный код) Admin (обсуждение \| вклад) м (1 версия)	Текущая версия на 09:58, 26 мая 2010 (просмотреть исходный код) Admin (обсуждение \| вклад) м (1 версия)
(нет различий)