Oracle PL/SQL/SQL Plus/Analyze — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Текущая версия на 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>