Oracle PL/SQL/SQL Plus/Analyze
Содержание
- 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
<source lang="sql">
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created. SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2), 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13) 5 );
Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> update emp
2 set deptno = 9 3 where deptno = 10;
3 rows updated. SQL> SQL> SQL> alter table emp add constraint emp_pk primary key(empno); Table altered. SQL> SQL> alter table dept add constraint dept_pk primary key(deptno); Table altered. SQL> SQL> analyze table emp compute statistics; Table analyzed. SQL> SQL> analyze table dept compute statistics; Table analyzed. SQL> SQL> set autotrace on explain SQL> SQL> select empno, ename, dept.deptno, dname
2 from emp, dept
3 where emp.deptno(+) = dept.deptno
4 UNION ALL
5 select empno, ename, emp.deptno, null
6 from emp, dept
7 where emp.deptno = dept.deptno(+)
8 and dept.deptno is null
9 order by 1, 2, 3, 4
10 /
EMPNO ENAME DEPTNO DNAME
---------- ---------- --------------
7369 SMITH 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE SALES
7782 CLARK 9
EMPNO ENAME DEPTNO DNAME
---------- ---------- --------------
7788 SCOTT 20 RESEARCH
7839 KING 9
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES
EMPNO ENAME DEPTNO DNAME
---------- ---------- --------------
7902 FORD 20 RESEARCH
7934 MILLER 9
10 ACCOUNTING
40 OPERATIONS
16 rows selected.
Execution Plan
Plan hash value: 1556511399
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 28 | 1666 | 7 | | 1 | SORT ORDER BY | | 28 | 1666 | 4 | | 2 | UNION-ALL | | | | | |* 3 | HASH JOIN OUTER | | 14 | 784 | 3 | | 4 | TABLE ACCESS FULL | DEPT | 4 | 92 | 1 | | 5 | TABLE ACCESS FULL | EMP | 14 | 462 | 1 | |* 6 | FILTER | | | | | | 7 | NESTED LOOPS OUTER| | 14 | 882 | 1 | | 8 | TABLE ACCESS FULL| EMP | 14 | 560 | 1 | |* 9 | INDEX UNIQUE SCAN| DEPT_PK | 1 | 23 | |
Predicate Information (identified by operation id):
3 - access("EMP"."DEPTNO"(+)="DEPT"."DEPTNO")
6 - filter("DEPT"."DEPTNO" IS NULL)
9 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+))
Note
- cpu costing is off (consider enabling it)
SQL> SQL> select empno, ename, nvl(dept.deptno,emp.deptno), dname
2 from emp FULL OUTER JOIN dept on ( emp.deptno = dept.deptno )
3 order by 1, 2, 3, 4
4 /
EMPNO ENAME NVL(DEPT.DEPTNO,EMP.DEPTNO) DNAME
---------- --------------------------- --------------
7369 SMITH 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7782 CLARK 9
7788 SCOTT 20 RESEARCH
7839 KING 9
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
EMPNO ENAME NVL(DEPT.DEPTNO,EMP.DEPTNO) DNAME
---------- --------------------------- --------------
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 9
10 ACCOUNTING
40 OPERATIONS
16 rows selected.
Execution Plan
Plan hash value: 1591132751
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 15 | 825 | 7 | | 1 | SORT ORDER BY | | 15 | 825 | 7 | | 2 | VIEW | | 15 | 825 | 4 | | 3 | UNION-ALL | | | | | |* 4 | HASH JOIN OUTER | | 14 | 882 | 3 | | 5 | TABLE ACCESS FULL| EMP | 14 | 560 | 1 | | 6 | TABLE ACCESS FULL| DEPT | 4 | 92 | 1 | |* 7 | FILTER | | | | | | 8 | TABLE ACCESS FULL| DEPT | 1 | 23 | 1 | |* 9 | TABLE ACCESS FULL| EMP | 5 | 65 | 1 |
Predicate Information (identified by operation id):
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+))
7 - filter( NOT EXISTS (SELECT /*+ UNNEST */ 0 FROM "EMP" "EMP"
WHERE "EMP"."DEPTNO"=:B1))
9 - filter("EMP"."DEPTNO"=:B1)
Note
- cpu costing is off (consider enabling it)
SQL> SQL> set autotrace off SQL> SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped.
</source>
analyze and autotrace single column key and multi-column key
<source lang="sql">
SQL> SQL> create table t
2 as 3 select * from all_objects;
Table created. SQL> SQL> create index t_idx1 on t(object_id); Index created. SQL> SQL> create index t_idx2 on t(owner,object_type); Index created. SQL> SQL> analyze table t
2 compute statistics 3 for table 4 for all indexes 5 for all indexed columns;
Table analyzed. SQL> set autotrace traceonly explain SQL> select object_id, owner, object_type
2 from t 3 where object_id between 100 and 2000 4 and owner = "SYS" 5 /
Execution Plan
Plan hash value: 1601196873
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 938 | 66598 | 25 | |* 1 | TABLE ACCESS FULL| T | 938 | 66598 | 25 |
Predicate Information (identified by operation id):
1 - filter("OBJECT_ID">=100 AND "OBJECT_ID"<=2000 AND "OWNER"="SYS")
Note
- cpu costing is off (consider enabling it)
SQL> SQL> set autotrace traceonly SQL> select object_id, owner, object_type
2 from t 3 where object_id between 100 and 2000 4 and owner = "SYS" 5 /
1250 rows selected.
Execution Plan
Plan hash value: 1601196873
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 938 | 66598 | 25 | |* 1 | TABLE ACCESS FULL| T | 938 | 66598 | 25 |
Predicate Information (identified by operation id):
1 - filter("OBJECT_ID">=100 AND "OBJECT_ID"<=2000 AND "OWNER"="SYS")
Note
- cpu costing is off (consider enabling it)
Statistics
0 recursive calls
0 db block gets
246 consistent gets
0 physical reads
0 redo size
25173 bytes sent via SQL*Net to client
1293 bytes received via SQL*Net from client
85 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1250 rows processed
SQL> SQL> select /*+ index( t t_idx1 ) */ object_id, owner, object_type
2 from t 3 where object_id between 100 and 2000 4 and owner = "SYS" 5 /
1250 rows selected.
Execution Plan
Plan hash value: 4055207394
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 938 | 66598 | 32 | |* 1 | TABLE ACCESS BY INDEX ROWID| T | 938 | 66598 | 32 | |* 2 | INDEX RANGE SCAN | T_IDX1 | 1810 | | 5 |
Predicate Information (identified by operation id):
1 - filter("OWNER"="SYS")
2 - access("OBJECT_ID">=100 AND "OBJECT_ID"<=2000)
Note
- cpu costing is off (consider enabling it)
Statistics
1 recursive calls
0 db block gets
195 consistent gets
0 physical reads
0 redo size
25173 bytes sent via SQL*Net to client
1293 bytes received via SQL*Net from client
85 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1250 rows processed
SQL> SQL> select /*+ index( t t_idx2 ) */ object_id, owner, object_type
2 from t 3 where object_id between 100 and 2000 4 and owner = "SYS" 5 /
1250 rows selected.
Execution Plan
Plan hash value: 3371054274
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 938 | 66598 | 522 | |* 1 | TABLE ACCESS BY INDEX ROWID| T | 938 | 66598 | 522 | |* 2 | INDEX RANGE SCAN | T_IDX2 | 6520 | | 23 |
Predicate Information (identified by operation id):
1 - filter("OBJECT_ID">=100 AND "OBJECT_ID"<=2000)
2 - access("OWNER"="SYS")
Note
- cpu costing is off (consider enabling it)
Statistics
1 recursive calls
0 db block gets
670 consistent gets
0 physical reads
0 redo size
22392 bytes sent via SQL*Net to client
1293 bytes received via SQL*Net from client
85 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1250 rows processed
SQL> SQL> set autotrace off SQL> SQL> drop table t; Table dropped.
</source>
analyze and autotrace table with primary key
<source lang="sql">
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created. SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2), 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13) 5 );
Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> update emp
2 set deptno = 9 3 where deptno = 10;
3 rows updated. SQL> SQL> SQL> alter table emp add constraint emp_pk primary key(empno); Table altered. SQL> SQL> alter table dept add constraint dept_pk primary key(deptno); Table altered. SQL> SQL> analyze table emp compute statistics; Table analyzed. SQL> SQL> analyze table dept compute statistics; Table analyzed. SQL> SQL> set autotrace on explain SQL> SQL> select empno, ename, dept.deptno, dname
2 from emp, dept
3 where emp.deptno(+) = dept.deptno
4 UNION ALL
5 select empno, ename, emp.deptno, null
6 from emp, dept
7 where emp.deptno = dept.deptno(+)
8 and dept.deptno is null
9 order by 1, 2, 3, 4
10 /
EMPNO ENAME DEPTNO DNAME
---------- ---------- --------------
7369 SMITH 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE SALES
7782 CLARK 9
EMPNO ENAME DEPTNO DNAME
---------- ---------- --------------
7788 SCOTT 20 RESEARCH
7839 KING 9
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES
EMPNO ENAME DEPTNO DNAME
---------- ---------- --------------
7902 FORD 20 RESEARCH
7934 MILLER 9
10 ACCOUNTING
40 OPERATIONS
16 rows selected.
Execution Plan
Plan hash value: 1556511399
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 28 | 1666 | 7 | | 1 | SORT ORDER BY | | 28 | 1666 | 4 | | 2 | UNION-ALL | | | | | |* 3 | HASH JOIN OUTER | | 14 | 784 | 3 | | 4 | TABLE ACCESS FULL | DEPT | 4 | 92 | 1 | | 5 | TABLE ACCESS FULL | EMP | 14 | 462 | 1 | |* 6 | FILTER | | | | | | 7 | NESTED LOOPS OUTER| | 14 | 882 | 1 | | 8 | TABLE ACCESS FULL| EMP | 14 | 560 | 1 | |* 9 | INDEX UNIQUE SCAN| DEPT_PK | 1 | 23 | |
Predicate Information (identified by operation id):
3 - access("EMP"."DEPTNO"(+)="DEPT"."DEPTNO")
6 - filter("DEPT"."DEPTNO" IS NULL)
9 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+))
Note
- cpu costing is off (consider enabling it)
SQL> SQL> select empno, ename, nvl(dept.deptno,emp.deptno), dname
2 from emp FULL OUTER JOIN dept on ( emp.deptno = dept.deptno )
3 order by 1, 2, 3, 4
4 /
EMPNO ENAME NVL(DEPT.DEPTNO,EMP.DEPTNO) DNAME
---------- --------------------------- --------------
7369 SMITH 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7782 CLARK 9
7788 SCOTT 20 RESEARCH
7839 KING 9
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
EMPNO ENAME NVL(DEPT.DEPTNO,EMP.DEPTNO) DNAME
---------- --------------------------- --------------
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 9
10 ACCOUNTING
40 OPERATIONS
16 rows selected.
Execution Plan
Plan hash value: 1591132751
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 15 | 825 | 7 | | 1 | SORT ORDER BY | | 15 | 825 | 7 | | 2 | VIEW | | 15 | 825 | 4 | | 3 | UNION-ALL | | | | | |* 4 | HASH JOIN OUTER | | 14 | 882 | 3 | | 5 | TABLE ACCESS FULL| EMP | 14 | 560 | 1 | | 6 | TABLE ACCESS FULL| DEPT | 4 | 92 | 1 | |* 7 | FILTER | | | | | | 8 | TABLE ACCESS FULL| DEPT | 1 | 23 | 1 | |* 9 | TABLE ACCESS FULL| EMP | 5 | 65 | 1 |
Predicate Information (identified by operation id):
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+))
7 - filter( NOT EXISTS (SELECT /*+ UNNEST */ 0 FROM "EMP" "EMP"
WHERE "EMP"."DEPTNO"=:B1))
9 - filter("EMP"."DEPTNO"=:B1)
Note
- cpu costing is off (consider enabling it)
SQL> SQL> set autotrace off SQL> SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped.
</source>
Analyze a table with/without index
<source lang="sql">
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;
</source>
analyze index
<source lang="sql">
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>
</source>
Analyze index on varchar2 value and date type value
<source lang="sql">
SQL> SQL> create table t
2 as
3 select to_char( to_date("01-jan-1995","dd-mon-yyyy")+rownum, "yyyymmdd" ) str_date,
4 to_date("01-jan-1995","dd-mon-yyyy")+rownum date_date
5 from all_objects
6 where rownum < 20
7 /
Table created. SQL> SQL> create index i1 on t(str_date); Index created. SQL> SQL> create index t_date_date_idx on t(date_date); Index created. SQL> SQL> analyze table t compute statistics
2 for table 3 for all indexes 4 for all indexed columns;
Table analyzed. SQL> SQL> set autotrace on explain SQL> select * from t
2 where str_date between "20001231" and "20100101" and rownum < 20;
no rows selected
Execution Plan
Plan hash value: 508354683
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 20 | 1 | |* 1 | COUNT STOPKEY | | | | | |* 2 | TABLE ACCESS FULL| T | 1 | 20 | 1 |
Predicate Information (identified by operation id):
1 - filter(ROWNUM<20)
2 - filter("STR_DATE">="20001231" AND "STR_DATE"<="20100101")
Note
- cpu costing is off (consider enabling it)
SQL> SQL> select * from t where date_date between to_date("20001231","yyyymmdd") and to_date("20100101","yyyymmdd") and rownum<20; no rows selected
Execution Plan
Plan hash value: 508354683
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 20 | 1 | |* 1 | COUNT STOPKEY | | | | | |* 2 | TABLE ACCESS FULL| T | 1 | 20 | 1 |
Predicate Information (identified by operation id):
1 - filter(ROWNUM<20)
2 - filter("DATE_DATE">=TO_DATE("2000-12-31 00:00:00", "yyyy-mm-dd
hh24:mi:ss") AND "DATE_DATE"<=TO_DATE("2010-01-01 00:00:00",
"yyyy-mm-dd hh24:mi:ss"))
Note
- cpu costing is off (consider enabling it)
SQL> SQL> SQL> select * from t
2 where str_date between "20001231" and "20100101" and rownum<20;
no rows selected
Execution Plan
Plan hash value: 508354683
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 20 | 1 | |* 1 | COUNT STOPKEY | | | | | |* 2 | TABLE ACCESS FULL| T | 1 | 20 | 1 |
Predicate Information (identified by operation id):
1 - filter(ROWNUM<20)
2 - filter("STR_DATE">="20001231" AND "STR_DATE"<="20100101")
Note
- cpu costing is off (consider enabling it)
SQL> SQL> select * from t where date_date between to_date("20001231","yyyymmdd") and to_date("20100101","yyyymmdd")
2 and rownum < 20;
no rows selected
Execution Plan
Plan hash value: 508354683
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 20 | 1 | |* 1 | COUNT STOPKEY | | | | | |* 2 | TABLE ACCESS FULL| T | 1 | 20 | 1 |
Predicate Information (identified by operation id):
1 - filter(ROWNUM<20)
2 - filter("DATE_DATE">=TO_DATE("2000-12-31 00:00:00", "yyyy-mm-dd
hh24:mi:ss") AND "DATE_DATE"<=TO_DATE("2010-01-01 00:00:00",
"yyyy-mm-dd hh24:mi:ss"))
Note
- cpu costing is off (consider enabling it)
SQL> SQL> set autotrace off SQL> SQL> drop table t; Table dropped.
</source>
analyze index t_idx validate structure
<source lang="sql">
SQL> SQL> SQL> create table t ( x int, y int ); Table created. SQL> SQL> create unique index t_idx on t(x,y); Index created. SQL> SQL> insert into t values ( 1, 1 ); 1 row created. SQL> insert into t values ( 1, NULL ); 1 row created. SQL> insert into t values ( NULL, 1 ); 1 row created. SQL> insert into t values ( NULL, NULL ); 1 row created. SQL> SQL> analyze index t_idx validate structure; Index analyzed. SQL> SQL> select name, lf_rows from index_stats; NAME LF_ROWS
----------
T_IDX 3 SQL> SQL> insert into t values ( NULL, NULL ); 1 row created. SQL> insert into t values ( NULL, 1 ); insert into t values ( NULL, 1 )
ERROR at line 1: ORA-00001: unique constraint (sqle.T_IDX) violated
SQL> insert into t values ( 1, NULL ); insert into t values ( 1, NULL )
ERROR at line 1: ORA-00001: unique constraint (sqle.T_IDX) violated
SQL> SQL> select x, y, count(*) from t group by x,y having count(*) > 1;
X Y COUNT(*)
---------- ----------
2
SQL> SQL> drop table t; Table dropped. SQL> SQL> create table t ( x int, y int NOT NULL ); Table created. SQL> create unique index t_idx on t(x,y); Index created. SQL> SQL> insert into t values ( 1, 1 ); 1 row created. SQL> insert into t values ( NULL, 1 ); 1 row created. SQL> SQL> begin
2 dbms_stats.gather_table_stats(user,"T"); 3 end; 4 /
PL/SQL procedure successfully completed. SQL> set autotrace on SQL> SQL> select * from t where x is null;
X Y
----------
1
Execution Plan
Plan hash value: 2946670127
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 5 | 1 | |* 1 | INDEX RANGE SCAN| T_IDX | 1 | 5 | 1 |
Predicate Information (identified by operation id):
1 - access("X" IS NULL)
Note
- cpu costing is off (consider enabling it)
Statistics
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
456 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SQL> set autotrace off SQL> SQL> drop table t; Table dropped. SQL>
</source>
analyze index validate structure
<source lang="sql">
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> --
</source>
analyze materialized view
<source lang="sql">
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>
</source>
analyze table after creating index
<source lang="sql">
SQL> SQL> SQL> SQL> create table myTable1 as select *
2 from all_objects where ROWNUM <=000;
Table created. SQL> SQL> SQL> SQL> SQL> create table myTable2 as select *
2 from all_objects where ROWNUM <= 9950;
Table created. SQL> SQL> SQL> SQL> SQL> create index myTable2_idx on myTable2(object_id); Index created. SQL> SQL> SQL> select count(*) from myTable1 rbo
2 where object_id not in ( select object_id from myTable2 ) 3 / COUNT(*)
0
SQL> SQL> SQL> SQL> select count(*) from myTable1 rbo
2 where NOT EXISTS (select null from myTable2 where myTable2.object_id = rbo.object_id ) 3 / COUNT(*)
0
SQL> SQL> SQL> SQL> SQL> select count(*) from myTable1, myTable2 rbo
2 where myTable1.object_id = rbo.object_id(+) and rbo.object_id IS NULL 3 / COUNT(*)
0
SQL> SQL> SQL> SQL> SQL> analyze table myTable1 compute statistics; Table analyzed. SQL> SQL> SQL> SQL> analyze table myTable2 compute statistics; Table analyzed. SQL> SQL> SQL> SQL> SQL> select count(*) from myTable1 cbo
2 where object_id not in ( select object_id from myTable2 ) 3 / COUNT(*)
0
SQL> SQL> SQL> SQL> SQL> select count(*) from myTable1 cbo
2 where object_id not in ( select object_id from myTable2 ) 3 / COUNT(*)
0
SQL> SQL> SQL> SQL> SQL> select count(*) from myTable1 cbo
2 where object_id not in ( select object_id from myTable2 ) 3 / COUNT(*)
0
SQL> SQL> SQL> SQL> SQL> alter table myTable2 modify object_id null; Table altered. SQL> SQL> SQL> SQL> select count(*)
2 from 3 myTable1 cbo where object_id not in ( select object_id from myTable2 ) 4 / COUNT(*)
0
SQL> SQL> SQL> SQL> SQL> drop table myTable1; Table dropped. SQL> SQL> SQL> SQL> drop table myTable2; Table dropped. SQL> SQL> SQL> SQL>
</source>
analyze table compute statistics
<source lang="sql">
SQL> SQL> 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> --
</source>
analyze table compute statistics for all indexes
<source lang="sql">
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> --
</source>
analyze table compute statistics for a table with two indexes
<source lang="sql">
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> --
</source>
ANALYZE TABLE employee COMPUTE STATISTICS
<source lang="sql">
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> --
</source>
analyze table estimate statistics
<source lang="sql">
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
SQL> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created.
SQL>
SQL> set arraysize 50
SQL> set autotrace on statistics
SQL> select empno, deptno
2 from emp 3 /
Enter...
2 30
3 30
4 20
5 30
7 10
8 20
9 10
10 30
13 20
2 30
3 30
4 20
5 30
7 10
8 20
9 10
10 30
13 20
18 rows selected.
Statistics
64 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
679 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
18 rows processed
SQL> create table IX ( x number primary key ) organization index;
SQL> SQL> analyze table IX estimate statistics; Table analyzed. SQL> SQL> select empno, cursor(select x from ix)
2 from emp 3 /
Enter...
2 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
3 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter... drop table emp;
4 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
5 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
7 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
8 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
9 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
10 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
13 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
2 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected
3 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
4 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
5 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
7 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter... CREATE TABLE EMP(
8 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
9 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
10 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
13 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected
18 rows selected. SQL> set autotrace off statistics
</source>
analyze table students compute statistics
<source lang="sql">
SQL> SQL> create table students
2 ( studentID number constraint students_pk primary key, 3 name varchar2(10) );
Table created. SQL> SQL> create table documentMaster
2 ( documentId number constraint document_pk primary key, 3 description varchar2(10) );
Table created. SQL> SQL> create table admission_docs
2 ( studentID references students, 3 documentId references documentMaster, 4 dt date, 5 constraint admission_pk primary key(studentID, documentId));
Table created. SQL> SQL> SQL> insert into students
2 select object_id, object_name 3 from all_objects;
SQL> SQL> SQL> insert into documentMaster
2 select ROWNUM, "doc " || ROWNUM 3 from all_users 4 where ROWNUM <= 5;
5 rows created. SQL> SQL> insert into admission_docs
2 select object_id, mod(ROWNUM,3)+1, created 3 from all_objects, (select 1 from all_users where ROWNUM <= 3);
SQL> SQL> SQL> analyze table students compute statistics
2 for table for all indexes for all indexed columns;
Table analyzed. SQL> SQL> analyze table documentMaster compute statistics
2 for table for all indexes for all indexed columns;
Table analyzed. SQL> SQL> analyze table admission_docs compute statistics
2 for table for all indexes for all indexed columns;
Table analyzed. SQL> SQL> SQL> set autotrace on SQL> variable bv number SQL> exec :bv := 1234 PL/SQL procedure successfully completed. SQL> SQL> select a.* , decode(b.dt,null,"No","Yes") submitted, b.dt
2 from ( 3 select * 4 from students, documentMaster 5 where students.studentID = :bv 6 ) a, admission_docs b 7 where a.studentID = b.studentID(+) 8 and a.documentId = b.documentId (+) 9 /
no rows selected
Execution Plan
Plan hash value: 3775454522
- | Id | Operation | Name | Rows | Bytes | Cost |
- | 0 | SELECT STATEMENT | | 5 | 550 | 2 | | 1 | NESTED LOOPS OUTER | | 5 | 550 | 2 | | 2 | VIEW | | 5 | 330 | 2 | | 3 | NESTED LOOPS | | 5 | 225 | 2 | | 4 | TABLE ACCESS BY INDEX ROWID| STUDENTS | 1 | 33 | 1 | |* 5 | INDEX UNIQUE SCAN | STUDENTS_PK | 1 | | | | 6 | TABLE ACCESS FULL | DOCUMENTMASTER | 5 | 60 | 1 | | 7 | TABLE ACCESS BY INDEX ROWID | ADMISSION_DOCS | 1 | 44 | | |* 8 | INDEX UNIQUE SCAN | ADMISSION_PK | 1 | | |
-
Predicate Information (identified by operation id):
5 - access("STUDENTS"."STUDENTID"=TO_NUMBER(:BV))
8 - access("A"."STUDENTID"="B"."STUDENTID"(+) AND
"A"."DOCUMENTID"="B"."DOCUMENTID"(+))
Note
- cpu costing is off (consider enabling it)
Statistics
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
564 bytes sent via SQL*Net to client
369 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> SQL> set autotrace off SQL> SQL> drop table students cascade constraints; Table dropped. SQL> SQL> drop table documentMaster cascade constraints; Table dropped. SQL> SQL> drop table admission_docs; Table dropped.
</source>
analyze table TABLENAME compute statistics;
<source lang="sql">
SQL> analyze table TABLENAME compute statistics;
SQL> SQL> select Num_Rows, /*number of rows*/
2 Blocks, /*number of blocks used*/ 3 Num_Rows/Blocks /*number of rows per block*/ 4 from USER_TABLES 5 where Table_Name="TABLENAME";
SQL>
</source>
analyze table tableName compute statistics for table, for all indexes, for all indexed columns;
<source lang="sql">
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> --
</source>
analyze table t compute statistics for table, for all indexes, for all indexed columns
<source lang="sql">
SQL> SQL> create table t
2 as
3 select to_char( to_date("01-jan-1995","dd-mon-yyyy")+rownum, "yyyymmdd" ) str_date,
4 to_date("01-jan-1995","dd-mon-yyyy")+rownum date_date
5 from all_objects
6 where rownum < 20
7 /
Table created. SQL> SQL> create index i1 on t(str_date); Index created. SQL> SQL> create index t_date_date_idx on t(date_date); Index created. SQL> SQL> analyze table t compute statistics
2 for table 3 for all indexes 4 for all indexed columns;
Table analyzed. SQL> SQL> set autotrace on explain SQL> select * from t
2 where str_date between "20001231" and "20100101" and rownum < 20;
no rows selected
Execution Plan
Plan hash value: 508354683
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 20 | 1 | |* 1 | COUNT STOPKEY | | | | | |* 2 | TABLE ACCESS FULL| T | 1 | 20 | 1 |
Predicate Information (identified by operation id):
1 - filter(ROWNUM<20)
2 - filter("STR_DATE">="20001231" AND "STR_DATE"<="20100101")
Note
- cpu costing is off (consider enabling it)
SQL> SQL> select * from t where date_date between to_date("20001231","yyyymmdd") and to_date("20100101","yyyymmdd") and rownum<20; no rows selected
Execution Plan
Plan hash value: 508354683
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 20 | 1 | |* 1 | COUNT STOPKEY | | | | | |* 2 | TABLE ACCESS FULL| T | 1 | 20 | 1 |
Predicate Information (identified by operation id):
1 - filter(ROWNUM<20)
2 - filter("DATE_DATE">=TO_DATE("2000-12-31 00:00:00", "yyyy-mm-dd
hh24:mi:ss") AND "DATE_DATE"<=TO_DATE("2010-01-01 00:00:00",
"yyyy-mm-dd hh24:mi:ss"))
Note
- cpu costing is off (consider enabling it)
SQL> SQL> SQL> select * from t
2 where str_date between "20001231" and "20100101" and rownum<20;
no rows selected
Execution Plan
Plan hash value: 508354683
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 20 | 1 | |* 1 | COUNT STOPKEY | | | | | |* 2 | TABLE ACCESS FULL| T | 1 | 20 | 1 |
Predicate Information (identified by operation id):
1 - filter(ROWNUM<20)
2 - filter("STR_DATE">="20001231" AND "STR_DATE"<="20100101")
Note
- cpu costing is off (consider enabling it)
SQL> SQL> select * from t where date_date between to_date("20001231","yyyymmdd") and to_date("20100101","yyyymmdd")
2 and rownum < 20;
no rows selected
Execution Plan
Plan hash value: 508354683
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 20 | 1 | |* 1 | COUNT STOPKEY | | | | | |* 2 | TABLE ACCESS FULL| T | 1 | 20 | 1 |
Predicate Information (identified by operation id):
1 - filter(ROWNUM<20)
2 - filter("DATE_DATE">=TO_DATE("2000-12-31 00:00:00", "yyyy-mm-dd
hh24:mi:ss") AND "DATE_DATE"<=TO_DATE("2010-01-01 00:00:00",
"yyyy-mm-dd hh24:mi:ss"))
Note
- cpu costing is off (consider enabling it)
SQL> SQL> set autotrace off SQL> SQL> drop table t; Table dropped.
</source>
analyze table t compute statistics for table for columns id;
<source lang="sql">
SQL> create table t as select mod(object_id,10) id, a.* from all_objects a; SQL> SQL> analyze table t compute statistics
2 for table 3 for columns id;
SQL> SQL> SQL> SQL> alter session set optimizer_max_permutations=80000; SQL> SQL> explain plan for
2 select count(*) 3 from t t1, t t2, t t3, t t4, t t5, t t6 4 where t1.id = t2.id 5 and t1.id = t3.id 6 and t1.id = t4.id 7 and t1.id = t5.id 8 and t1.id = t6.id;
SQL> SQL> set autotrace off SQL> SQL> SQL> drop table t;
</source>
Analyze table with primary key and foreign key
<source lang="sql">
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>
</source>
Anayze all objects in the APPOWNER schema.
<source lang="sql">
SQL> SQL> execute DBMS_UTILITY.ANALYZE_SCHEMA("APPOWNER","COMPUTE"); SQL> SQL>
</source>
create or replace outline and analyze table compute statistics
<source lang="sql">
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>
</source>
Demonstrate IN versus EXISTS performance
<source lang="sql">
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>
</source>
