Oracle PL/SQL Tutorial/SQL PLUS Session Environment/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 compute statistics on table with index
- 5 analyze index t_idx validate structure
- 6 analyze table after creating index
- 7 analyze table compute statistics
- 8 analyze table compute statistics for table for all indexes for all indexed columns
- 9 analyze table estimate statistics
- 10 analyze table students compute statistics
- 11 analyze table TABLENAME compute statistics;
- 12 analyze table t compute statistics for table, for all indexes, for all indexed columns
- 13 analyze table t compute statistics for table for columns id;
- 14 Analyze table with user defined column type
analyze and autotrace full outer join and union
<source lang="sql">
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created. SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2), 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13) 5 );
Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> update emp
2 set deptno = 9 3 where deptno = 10;
3 rows updated. SQL> SQL> SQL> alter table emp add constraint emp_pk primary key(empno); Table altered. SQL> SQL> alter table dept add constraint dept_pk primary key(deptno); Table altered. SQL> SQL> analyze table emp compute statistics; Table analyzed. SQL> SQL> analyze table dept compute statistics; Table analyzed. SQL> SQL> set autotrace on explain SQL> SQL> select empno, ename, dept.deptno, dname
2 from emp, dept 3 where emp.deptno(+) = dept.deptno 4 UNION ALL 5 select empno, ename, emp.deptno, null 6 from emp, dept 7 where emp.deptno = dept.deptno(+) 8 and dept.deptno is null 9 order by 1, 2, 3, 4 10 / EMPNO ENAME DEPTNO DNAME
---------- ---------- --------------
7369 SMITH 20 RESEARCH 7499 ALLEN 30 SALES 7521 WARD SALES 7566 JONES 20 RESEARCH 7654 MARTIN 30 SALES 7698 BLAKE SALES 7782 CLARK 9 EMPNO ENAME DEPTNO DNAME
---------- ---------- --------------
7788 SCOTT 20 RESEARCH 7839 KING 9 7844 TURNER 30 SALES 7876 ADAMS 20 RESEARCH 7900 JAMES 30 SALES
EMPNO ENAME DEPTNO DNAME
---------- ---------- --------------
7902 FORD 20 RESEARCH 7934 MILLER 9 10 ACCOUNTING 40 OPERATIONS
16 rows selected.
Execution Plan
Plan hash value: 1556511399
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 28 | 1666 | 7 | | 1 | SORT ORDER BY | | 28 | 1666 | 4 | | 2 | UNION-ALL | | | | | |* 3 | HASH JOIN OUTER | | 14 | 784 | 3 | | 4 | TABLE ACCESS FULL | DEPT | 4 | 92 | 1 | | 5 | TABLE ACCESS FULL | EMP | 14 | 462 | 1 | |* 6 | FILTER | | | | | | 7 | NESTED LOOPS OUTER| | 14 | 882 | 1 | | 8 | TABLE ACCESS FULL| EMP | 14 | 560 | 1 | |* 9 | INDEX UNIQUE SCAN| DEPT_PK | 1 | 23 | |
Predicate Information (identified by operation id):
3 - access("EMP"."DEPTNO"(+)="DEPT"."DEPTNO") 6 - filter("DEPT"."DEPTNO" IS NULL) 9 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+))
Note
- cpu costing is off (consider enabling it)
SQL> SQL> select empno, ename, nvl(dept.deptno,emp.deptno), dname
2 from emp FULL OUTER JOIN dept on ( emp.deptno = dept.deptno ) 3 order by 1, 2, 3, 4 4 / EMPNO ENAME NVL(DEPT.DEPTNO,EMP.DEPTNO) DNAME
---------- --------------------------- --------------
7369 SMITH 20 RESEARCH 7499 ALLEN 30 SALES 7521 WARD 30 SALES 7566 JONES 20 RESEARCH 7654 MARTIN 30 SALES 7698 BLAKE 30 SALES 7782 CLARK 9 7788 SCOTT 20 RESEARCH 7839 KING 9 7844 TURNER 30 SALES 7876 ADAMS 20 RESEARCH EMPNO ENAME NVL(DEPT.DEPTNO,EMP.DEPTNO) DNAME
---------- --------------------------- --------------
7900 JAMES 30 SALES 7902 FORD 20 RESEARCH 7934 MILLER 9 10 ACCOUNTING 40 OPERATIONS
16 rows selected.
Execution Plan
Plan hash value: 1591132751
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 15 | 825 | 7 | | 1 | SORT ORDER BY | | 15 | 825 | 7 | | 2 | VIEW | | 15 | 825 | 4 | | 3 | UNION-ALL | | | | | |* 4 | HASH JOIN OUTER | | 14 | 882 | 3 | | 5 | TABLE ACCESS FULL| EMP | 14 | 560 | 1 | | 6 | TABLE ACCESS FULL| DEPT | 4 | 92 | 1 | |* 7 | FILTER | | | | | | 8 | TABLE ACCESS FULL| DEPT | 1 | 23 | 1 | |* 9 | TABLE ACCESS FULL| EMP | 5 | 65 | 1 |
Predicate Information (identified by operation id):
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+)) 7 - filter( NOT EXISTS (SELECT /*+ UNNEST */ 0 FROM "EMP" "EMP" WHERE "EMP"."DEPTNO"=:B1)) 9 - filter("EMP"."DEPTNO"=:B1)
Note
- cpu costing is off (consider enabling it)
SQL> SQL> set autotrace off SQL> SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped.</source>
analyze and autotrace single column key and multi-column key
<source lang="sql">
SQL> SQL> create table t
2 as 3 select * from all_objects;
Table created. SQL> SQL> create index t_idx1 on t(object_id); Index created. SQL> SQL> create index t_idx2 on t(owner,object_type); Index created. SQL> SQL> analyze table t
2 compute statistics 3 for table 4 for all indexes 5 for all indexed columns;
Table analyzed. SQL> set autotrace traceonly explain SQL> select object_id, owner, object_type
2 from t 3 where object_id between 100 and 2000 4 and owner = "SYS" 5 /
Execution Plan
Plan hash value: 1601196873
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 938 | 66598 | 25 | |* 1 | TABLE ACCESS FULL| T | 938 | 66598 | 25 |
Predicate Information (identified by operation id):
1 - filter("OBJECT_ID">=100 AND "OBJECT_ID"<=2000 AND "OWNER"="SYS")
Note
- cpu costing is off (consider enabling it)
SQL> SQL> set autotrace traceonly SQL> select object_id, owner, object_type
2 from t 3 where object_id between 100 and 2000 4 and owner = "SYS" 5 /
1250 rows selected.
Execution Plan
Plan hash value: 1601196873
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 938 | 66598 | 25 | |* 1 | TABLE ACCESS FULL| T | 938 | 66598 | 25 |
Predicate Information (identified by operation id):
1 - filter("OBJECT_ID">=100 AND "OBJECT_ID"<=2000 AND "OWNER"="SYS")
Note
- cpu costing is off (consider enabling it)
Statistics
0 recursive calls 0 db block gets 246 consistent gets 0 physical reads 0 redo size 25173 bytes sent via SQL*Net to client 1293 bytes received via SQL*Net from client 85 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1250 rows processed
SQL> SQL> select /*+ index( t t_idx1 ) */ object_id, owner, object_type
2 from t 3 where object_id between 100 and 2000 4 and owner = "SYS" 5 /
1250 rows selected.
Execution Plan
Plan hash value: 4055207394
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 938 | 66598 | 32 | |* 1 | TABLE ACCESS BY INDEX ROWID| T | 938 | 66598 | 32 | |* 2 | INDEX RANGE SCAN | T_IDX1 | 1810 | | 5 |
Predicate Information (identified by operation id):
1 - filter("OWNER"="SYS") 2 - access("OBJECT_ID">=100 AND "OBJECT_ID"<=2000)
Note
- cpu costing is off (consider enabling it)
Statistics
1 recursive calls 0 db block gets 195 consistent gets 0 physical reads 0 redo size 25173 bytes sent via SQL*Net to client 1293 bytes received via SQL*Net from client 85 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1250 rows processed
SQL> SQL> select /*+ index( t t_idx2 ) */ object_id, owner, object_type
2 from t 3 where object_id between 100 and 2000 4 and owner = "SYS" 5 /
1250 rows selected.
Execution Plan
Plan hash value: 3371054274
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 938 | 66598 | 522 | |* 1 | TABLE ACCESS BY INDEX ROWID| T | 938 | 66598 | 522 | |* 2 | INDEX RANGE SCAN | T_IDX2 | 6520 | | 23 |
Predicate Information (identified by operation id):
1 - filter("OBJECT_ID">=100 AND "OBJECT_ID"<=2000) 2 - access("OWNER"="SYS")
Note
- cpu costing is off (consider enabling it)
Statistics
1 recursive calls 0 db block gets 670 consistent gets 0 physical reads 0 redo size 22392 bytes sent via SQL*Net to client 1293 bytes received via SQL*Net from client 85 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1250 rows processed
SQL> SQL> set autotrace off SQL> SQL> drop table t; Table dropped.</source>
analyze and autotrace table with primary key
<source lang="sql">
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created. SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2), 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13) 5 );
Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> update emp
2 set deptno = 9 3 where deptno = 10;
3 rows updated. SQL> SQL> SQL> alter table emp add constraint emp_pk primary key(empno); Table altered. SQL> SQL> alter table dept add constraint dept_pk primary key(deptno); Table altered. SQL> SQL> analyze table emp compute statistics; Table analyzed. SQL> SQL> analyze table dept compute statistics; Table analyzed. SQL> SQL> set autotrace on explain SQL> SQL> select empno, ename, dept.deptno, dname
2 from emp, dept 3 where emp.deptno(+) = dept.deptno 4 UNION ALL 5 select empno, ename, emp.deptno, null 6 from emp, dept 7 where emp.deptno = dept.deptno(+) 8 and dept.deptno is null 9 order by 1, 2, 3, 4 10 / EMPNO ENAME DEPTNO DNAME
---------- ---------- --------------
7369 SMITH 20 RESEARCH 7499 ALLEN 30 SALES 7521 WARD SALES 7566 JONES 20 RESEARCH 7654 MARTIN 30 SALES 7698 BLAKE SALES 7782 CLARK 9 EMPNO ENAME DEPTNO DNAME
---------- ---------- --------------
7788 SCOTT 20 RESEARCH 7839 KING 9 7844 TURNER 30 SALES 7876 ADAMS 20 RESEARCH 7900 JAMES 30 SALES
EMPNO ENAME DEPTNO DNAME
---------- ---------- --------------
7902 FORD 20 RESEARCH 7934 MILLER 9 10 ACCOUNTING 40 OPERATIONS
16 rows selected.
Execution Plan
Plan hash value: 1556511399
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 28 | 1666 | 7 | | 1 | SORT ORDER BY | | 28 | 1666 | 4 | | 2 | UNION-ALL | | | | | |* 3 | HASH JOIN OUTER | | 14 | 784 | 3 | | 4 | TABLE ACCESS FULL | DEPT | 4 | 92 | 1 | | 5 | TABLE ACCESS FULL | EMP | 14 | 462 | 1 | |* 6 | FILTER | | | | | | 7 | NESTED LOOPS OUTER| | 14 | 882 | 1 | | 8 | TABLE ACCESS FULL| EMP | 14 | 560 | 1 | |* 9 | INDEX UNIQUE SCAN| DEPT_PK | 1 | 23 | |
Predicate Information (identified by operation id):
3 - access("EMP"."DEPTNO"(+)="DEPT"."DEPTNO") 6 - filter("DEPT"."DEPTNO" IS NULL) 9 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+))
Note
- cpu costing is off (consider enabling it)
SQL> SQL> select empno, ename, nvl(dept.deptno,emp.deptno), dname
2 from emp FULL OUTER JOIN dept on ( emp.deptno = dept.deptno ) 3 order by 1, 2, 3, 4 4 / EMPNO ENAME NVL(DEPT.DEPTNO,EMP.DEPTNO) DNAME
---------- --------------------------- --------------
7369 SMITH 20 RESEARCH 7499 ALLEN 30 SALES 7521 WARD 30 SALES 7566 JONES 20 RESEARCH 7654 MARTIN 30 SALES 7698 BLAKE 30 SALES 7782 CLARK 9 7788 SCOTT 20 RESEARCH 7839 KING 9 7844 TURNER 30 SALES 7876 ADAMS 20 RESEARCH EMPNO ENAME NVL(DEPT.DEPTNO,EMP.DEPTNO) DNAME
---------- --------------------------- --------------
7900 JAMES 30 SALES 7902 FORD 20 RESEARCH 7934 MILLER 9 10 ACCOUNTING 40 OPERATIONS
16 rows selected.
Execution Plan
Plan hash value: 1591132751
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 15 | 825 | 7 | | 1 | SORT ORDER BY | | 15 | 825 | 7 | | 2 | VIEW | | 15 | 825 | 4 | | 3 | UNION-ALL | | | | | |* 4 | HASH JOIN OUTER | | 14 | 882 | 3 | | 5 | TABLE ACCESS FULL| EMP | 14 | 560 | 1 | | 6 | TABLE ACCESS FULL| DEPT | 4 | 92 | 1 | |* 7 | FILTER | | | | | | 8 | TABLE ACCESS FULL| DEPT | 1 | 23 | 1 | |* 9 | TABLE ACCESS FULL| EMP | 5 | 65 | 1 |
Predicate Information (identified by operation id):
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+)) 7 - filter( NOT EXISTS (SELECT /*+ UNNEST */ 0 FROM "EMP" "EMP" WHERE "EMP"."DEPTNO"=:B1)) 9 - filter("EMP"."DEPTNO"=:B1)
Note
- cpu costing is off (consider enabling it)
SQL> SQL> set autotrace off SQL> SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped.</source>
analyze compute statistics on table with index
<source lang="sql">
SQL> SQL> create table I1(n number primary key, v varchar2(10)); Table created. SQL> create table I2(n number primary key, v varchar2(10)); Table created. SQL> SQL> create table MAP
2 (n number primary key, 3 i1 number referencing I1(n), 4 i2 number referencing I2(n));
Table created. SQL> SQL> create unique index IDX_MAP on MAP(i1, i2); Index created. SQL> set autotrace traceonly SQL> SQL> select * from i1,map,i2
2 where i1.n = map.i1 3 and i2.n = map.i2 4 and i1.v = "x" 5 and i2.v = "y";
no rows selected
Execution Plan
Plan hash value: 3070822050
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 79 | 4 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 79 | 4 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 1 | 59 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | MAP | 1 | 39 | 2 (0)| 00:00:01 | |* 4 | TABLE ACCESS BY INDEX ROWID| I1 | 1 | 20 | 1 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | SYS_C008275 | 1 | | 1 (0)| 00:00:01 | |* 6 | TABLE ACCESS BY INDEX ROWID | I2 | 1 | 20 | 1 (0)| 00:00:01 | |* 7 | INDEX UNIQUE SCAN | SYS_C008276 | 1 | | 1 (0)| 00:00:01 |
Predicate Information (identified by operation id):
4 - filter("I1"."V"="x") 5 - access("I1"."N"="MAP"."I1") 6 - filter("I2"."V"="y") 7 - access("I2"."N"="MAP"."I2")
Note
- dynamic sampling used for this statement
Statistics
44 recursive calls 0 db block gets 13 consistent gets 0 physical reads 0 redo size 578 bytes sent via SQL*Net to client 369 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
SQL> SQL> analyze table i1 compute statistics; Table analyzed. SQL> SQL> analyze table i2 compute statistics; Table analyzed. SQL> SQL> analyze table map compute statistics; Table analyzed. SQL> SQL> select * from i1,map,i2
2 where i1.n = map.i1 3 and i2.n = map.i2 4 and i1.v = "x" 5 and i2.v = "y";
no rows selected
Execution Plan
Plan hash value: 1158434662
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 79 | 4 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 79 | 4 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 1 | 59 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL | I1 | 1 | 20 | 2 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| MAP | 1 | 39 | 1 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | IDX_MAP | 1 | | 1 (0)| 00:00:01 | |* 6 | TABLE ACCESS BY INDEX ROWID | I2 | 1 | 20 | 1 (0)| 00:00:01 | |* 7 | INDEX UNIQUE SCAN | SYS_C008276 | 1 | | 1 (0)| 00:00:01 |
Predicate Information (identified by operation id):
3 - filter("I1"."V"="x") 5 - access("I1"."N"="MAP"."I1") 6 - filter("I2"."V"="y") 7 - access("I2"."N"="MAP"."I2")
Statistics
1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 578 bytes sent via SQL*Net to client 369 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
SQL> SQL> set autotrace off SQL> SQL> create index i1_idx on i1(v); Index created. SQL> SQL> analyze table i1 compute statistics; Table analyzed. SQL> SQL> set autotrace traceonly SQL> SQL> select * from i1,map,i2
2 where i1.n = map.i1 3 and i2.n = map.i2 4 and i1.v = "x" 5 and i2.v = "y";
no rows selected
Execution Plan
Plan hash value: 1388106715
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 79 | 3 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 79 | 3 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 1 | 59 | 2 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| I1 | 1 | 20 | 1 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | I1_IDX | 1 | | 1 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| MAP | 1 | 39 | 1 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | IDX_MAP | 1 | | 1 (0)| 00:00:01 | |* 7 | TABLE ACCESS BY INDEX ROWID | I2 | 1 | 20 | 1 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | SYS_C008276 | 1 | | 1 (0)| 00:00:01 |
Predicate Information (identified by operation id):
4 - access("I1"."V"="x") 6 - access("I1"."N"="MAP"."I1") 7 - filter("I2"."V"="y") 8 - access("I2"."N"="MAP"."I2")
Statistics
1 recursive calls 0 db block gets 1 consistent gets 0 physical reads 0 redo size 578 bytes sent via SQL*Net to client 369 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
SQL> SQL> set autotrace off</source>
analyze index t_idx validate structure
<source lang="sql">
SQL> SQL> SQL> create table t ( x int, y int ); Table created. SQL> SQL> create unique index t_idx on t(x,y); Index created. SQL> SQL> insert into t values ( 1, 1 ); 1 row created. SQL> insert into t values ( 1, NULL ); 1 row created. SQL> insert into t values ( NULL, 1 ); 1 row created. SQL> insert into t values ( NULL, NULL ); 1 row created. SQL> SQL> analyze index t_idx validate structure; Index analyzed. SQL> SQL> select name, lf_rows from index_stats; NAME LF_ROWS
----------
T_IDX 3 SQL> SQL> insert into t values ( NULL, NULL ); 1 row created. SQL> insert into t values ( NULL, 1 ); insert into t values ( NULL, 1 )
ERROR at line 1: ORA-00001: unique constraint (sqle.T_IDX) violated
SQL> insert into t values ( 1, NULL ); insert into t values ( 1, NULL )
ERROR at line 1: ORA-00001: unique constraint (sqle.T_IDX) violated
SQL> SQL> select x, y, count(*) from t group by x,y having count(*) > 1;
X Y COUNT(*)
---------- ----------
2
SQL> SQL> drop table t; Table dropped. SQL> SQL> create table t ( x int, y int NOT NULL ); Table created. SQL> create unique index t_idx on t(x,y); Index created. SQL> SQL> insert into t values ( 1, 1 ); 1 row created. SQL> insert into t values ( NULL, 1 ); 1 row created. SQL> SQL> begin
2 dbms_stats.gather_table_stats(user,"T"); 3 end; 4 /
PL/SQL procedure successfully completed. SQL> set autotrace on SQL> SQL> select * from t where x is null;
X Y
----------
1
Execution Plan
Plan hash value: 2946670127
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 5 | 1 | |* 1 | INDEX RANGE SCAN| T_IDX | 1 | 5 | 1 |
Predicate Information (identified by operation id):
1 - access("X" IS NULL)
Note
- cpu costing is off (consider enabling it)
Statistics
1 recursive calls 0 db block gets 1 consistent gets 0 physical reads 0 redo size 456 bytes sent via SQL*Net to client 380 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
SQL> SQL> set autotrace off SQL> SQL> drop table t; Table dropped. SQL></source>
analyze table after creating index
<source lang="sql">
SQL> SQL> SQL> SQL> create table myTable1 as select *
2 from all_objects where ROWNUM <=000;
Table created. SQL> SQL> SQL> SQL> SQL> create table myTable2 as select *
2 from all_objects where ROWNUM <= 9950;
Table created. SQL> SQL> SQL> SQL> SQL> create index myTable2_idx on myTable2(object_id); Index created. SQL> SQL> SQL> select count(*) from myTable1 rbo
2 where object_id not in ( select object_id from myTable2 ) 3 / COUNT(*)
0
SQL> SQL> SQL> SQL> select count(*) from myTable1 rbo
2 where NOT EXISTS (select null from myTable2 where myTable2.object_id = rbo.object_id ) 3 / COUNT(*)
0
SQL> SQL> SQL> SQL> SQL> select count(*) from myTable1, myTable2 rbo
2 where myTable1.object_id = rbo.object_id(+) and rbo.object_id IS NULL 3 / COUNT(*)
0
SQL> SQL> SQL> SQL> SQL> analyze table myTable1 compute statistics; Table analyzed. SQL> SQL> SQL> SQL> analyze table myTable2 compute statistics; Table analyzed. SQL> SQL> SQL> SQL> SQL> select count(*) from myTable1 cbo
2 where object_id not in ( select object_id from myTable2 ) 3 / COUNT(*)
0
SQL> SQL> SQL> SQL> SQL> select count(*) from myTable1 cbo
2 where object_id not in ( select object_id from myTable2 ) 3 / COUNT(*)
0
SQL> SQL> SQL> SQL> SQL> select count(*) from myTable1 cbo
2 where object_id not in ( select object_id from myTable2 ) 3 / COUNT(*)
0
SQL> SQL> SQL> SQL> SQL> alter table myTable2 modify object_id null; Table altered. SQL> SQL> SQL> SQL> select count(*)
2 from 3 myTable1 cbo where object_id not in ( select object_id from myTable2 ) 4 / COUNT(*)
0
SQL> SQL> SQL> SQL> SQL> drop table myTable1; Table dropped. SQL> SQL> SQL> SQL> drop table myTable2; Table dropped. SQL> SQL> SQL> SQL></source>
analyze table compute statistics
<source lang="sql">
SQL> SQL> SQL> create table myTable1 as select * from all_objects where rownum < 50; Table created. SQL> create table myTable2 as select * from all_objects where rownum <= 50; Table created. SQL> SQL> alter table myTable1 add constraint myTable1_pk primary key(object_id); Table altered. SQL> alter table myTable2 add constraint myTable2_pk primary key(object_id); Table altered. SQL> SQL> analyze table myTable1 compute statistics
2 for table for all indexes for all indexed columns;
Table analyzed. SQL> SQL> analyze table myTable2 compute statistics
2 for table for all indexes for all indexed columns;
Table analyzed. SQL> SQL> create or replace function get_data( p_object_id in number ) return varchar2
2 is 3 l_object_name myTable2.object_name%type; 4 begin 5 select object_name into l_object_name 6 from myTable2 7 where object_id = p_object_id; 8 return l_object_name; 9 exception 10 when no_data_found then 11 return NULL; 12 end; 13 /
Function created. SQL> SQL> select a.object_id, a.object_name oname1, b.object_name oname2
2 from myTable1 a, myTable2 b 3 where a.object_id = b.object_id(+); OBJECT_ID ONAME1 ONAME2
------------------------------ ------------------------------
20 ICOL$ ICOL$ 44 I_USER1 I_USER1 28 CON$ CON$ 15 UNDO$ UNDO$ 29 C_COBJ# C_COBJ# 3 I_OBJ# I_OBJ# 25 PROXY_ROLE_DATA$ PROXY_ROLE_DATA$ 39 I_IND1 I_IND1 51 I_CDEF2 I_CDEF2 26 I_PROXY_ROLE_DATA$_1 I_PROXY_ROLE_DATA$_1 17 FILE$ FILE$ OBJECT_ID ONAME1 ONAME2
------------------------------ ------------------------------
13 UET$ UET$ 9 I_FILE#_BLOCK# I_FILE#_BLOCK# 41 I_FILE1 I_FILE1 48 I_CON1 I_CON1 38 I_OBJ3 I_OBJ3 7 I_TS# I_TS# 53 I_CDEF4 I_CDEF4 19 IND$ IND$ 14 SEG$ SEG$ 6 C_TS# C_TS# 42 I_FILE2 I_FILE2 OBJECT_ID ONAME1 ONAME2
------------------------------ ------------------------------
21 COL$ COL$ 43 I_TS1 I_TS1 35 I_UNDO2 I_UNDO2 5 CLU$ CLU$ 23 PROXY_DATA$ PROXY_DATA$ 24 I_PROXY_DATA$ I_PROXY_DATA$ 36 I_OBJ1 I_OBJ1 46 I_COL2 I_COL2 37 I_OBJ2 I_OBJ2 54 I_CCOL1 I_CCOL1 16 TS$ TS$ OBJECT_ID ONAME1 ONAME2
------------------------------ ------------------------------
8 C_FILE#_BLOCK# C_FILE#_BLOCK# 10 C_USER# C_USER# 34 I_UNDO1 I_UNDO1 56 BOOTSTRAP$ BOOTSTRAP$ 12 FET$ FET$ 33 I_TAB1 I_TAB1 32 CCOL$ CCOL$ 22 USER$ USER$ 49 I_CON2 I_CON2 30 I_COBJ# I_COBJ# 18 OBJ$ OBJ$ OBJECT_ID ONAME1 ONAME2
------------------------------ ------------------------------
47 I_COL3 I_COL3 2 C_OBJ# C_OBJ# 4 TAB$ TAB$ 31 CDEF$ CDEF$ 50 I_CDEF1 I_CDEF1
49 rows selected. SQL> SQL> select object_id, object_name oname1, get_data(object_id) oname2
2 from myTable1; OBJECT_ID ONAME1
------------------------------
ONAME2
20 ICOL$
ICOL$
44 I_USER1
I_USER1
28 CON$
CON$
OBJECT_ID ONAME1
------------------------------
ONAME2
15 UNDO$
UNDO$
29 C_COBJ#
C_COBJ#
3 I_OBJ#
I_OBJ#
OBJECT_ID ONAME1
------------------------------
ONAME2
25 PROXY_ROLE_DATA$
PROXY_ROLE_DATA$
39 I_IND1
I_IND1
51 I_CDEF2
I_CDEF2
OBJECT_ID ONAME1
------------------------------
ONAME2
26 I_PROXY_ROLE_DATA$_1
I_PROXY_ROLE_DATA$_1
17 FILE$
FILE$
13 UET$
UET$
OBJECT_ID ONAME1
------------------------------
ONAME2
9 I_FILE#_BLOCK#
I_FILE#_BLOCK#
41 I_FILE1
I_FILE1
48 I_CON1
I_CON1
OBJECT_ID ONAME1
------------------------------
ONAME2
38 I_OBJ3
I_OBJ3
7 I_TS#
I_TS#
53 I_CDEF4
I_CDEF4
OBJECT_ID ONAME1
------------------------------
ONAME2
19 IND$
IND$
14 SEG$
SEG$
6 C_TS#
C_TS#
OBJECT_ID ONAME1
------------------------------
ONAME2
42 I_FILE2
I_FILE2
21 COL$
COL$
43 I_TS1
I_TS1
OBJECT_ID ONAME1
------------------------------
ONAME2
35 I_UNDO2
I_UNDO2
5 CLU$
CLU$
23 PROXY_DATA$
PROXY_DATA$
OBJECT_ID ONAME1
------------------------------
ONAME2
24 I_PROXY_DATA$
I_PROXY_DATA$
36 I_OBJ1
I_OBJ1
46 I_COL2
I_COL2
OBJECT_ID ONAME1
------------------------------
ONAME2
37 I_OBJ2
I_OBJ2
54 I_CCOL1
I_CCOL1
16 TS$
TS$
OBJECT_ID ONAME1
------------------------------
ONAME2
8 C_FILE#_BLOCK#
C_FILE#_BLOCK#
10 C_USER#
C_USER#
34 I_UNDO1
I_UNDO1
OBJECT_ID ONAME1
------------------------------
ONAME2
56 BOOTSTRAP$
BOOTSTRAP$
12 FET$
FET$
33 I_TAB1
I_TAB1
OBJECT_ID ONAME1
------------------------------
ONAME2
32 CCOL$
CCOL$
22 USER$
USER$
49 I_CON2
I_CON2
OBJECT_ID ONAME1
------------------------------
ONAME2
30 I_COBJ#
I_COBJ#
18 OBJ$
OBJ$
47 I_COL3
I_COL3
OBJECT_ID ONAME1
------------------------------
ONAME2
2 C_OBJ#
C_OBJ#
4 TAB$
TAB$
31 CDEF$
CDEF$
OBJECT_ID ONAME1
------------------------------
ONAME2
50 I_CDEF1
I_CDEF1
49 rows selected. SQL> SQL> begin
2 3 for x in ( select a.object_id, 4 a.object_name oname1, 5 b.object_name oname2 6 from myTable1 a, myTable2 b 7 where a.object_id = b.object_id(+) ) 8 loop 9 null; 10 end loop; 11 12 for x in ( select object_id, 13 object_name oname1, 14 get_data(object_id) oname2 15 from myTable1 ) 16 loop 17 null; 18 end loop; 19 20 end; 21 /
PL/SQL procedure successfully completed. SQL> SQL> drop table myTable1; Table dropped. SQL> drop table myTable2; Table dropped. SQL></source>
analyze table compute statistics for table for all indexes for all indexed columns
<source lang="sql">
SQL> SQL> SQL> create table myTable as
2 select decode( mod(rownum,100), 0, "N", "Y" ) processed, a.* 3 from all_objects a;
Table created. SQL> SQL> create index processed_idx on myTable(processed); Index created. SQL> SQL> select * from myTable where status = "N"; no rows selected SQL> SQL> SQL> analyze table myTable compute statistics
2 for table 3 for all indexes 4 for all indexed columns 5 /
Table analyzed. SQL> SQL> variable processed varchar2(1); SQL> exec :processed := "N" PL/SQL procedure successfully completed. SQL> SQL> set autotrace traceonly explain SQL> SQL> select *
2 from myTable 3 where processed = "N";
Execution Plan
Plan hash value: 362789735
| Id | Operation | Name | Rows | Bytes | Cost (%CPU )| Time |
| 0 | SELECT STATEMENT | | 126 | 11466 | 4 (0 )| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| MYTABLE | 126 | 11466 | 4 (0 )| 00:00:01 | |* 2 | INDEX RANGE SCAN | PROCESSED_IDX | 126 | | 1 (0 )| 00:00:01 |
Predicate Information (identified by operation id):
2 - access("PROCESSED"="N")
SQL> SQL> SQL> select *
2 from myTable 3 where processed = :processed;
Execution Plan
Plan hash value: 1015944200
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 6328 | 562K| 47 (3)| 00:00:01 | |* 1 | TABLE ACCESS FULL| MYTABLE | 6328 | 562K| 47 (3)| 00:00:01 |
Predicate Information (identified by operation id):
1 - filter("PROCESSED"=:PROCESSED)
SQL> SQL> drop table myTable; Table dropped. SQL> SQL> set autotrace off SQL> SQL></source>
analyze table estimate statistics
<source lang="sql">
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
SQL> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created.
SQL>
SQL> set arraysize 50
SQL> set autotrace on statistics
SQL> select empno, deptno
2 from emp 3 /
Enter...
2 30 3 30 4 20 5 30 7 10 8 20 9 10 10 30 13 20 2 30 3 30 4 20 5 30 7 10 8 20 9 10 10 30 13 20
18 rows selected.
Statistics
64 recursive calls 0 db block gets 16 consistent gets 0 physical reads 0 redo size 679 bytes sent via SQL*Net to client 380 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 18 rows processed
SQL> create table IX ( x number primary key ) organization index;
SQL> SQL> analyze table IX estimate statistics; Table analyzed. SQL> SQL> select empno, cursor(select x from ix)
2 from emp 3 /
Enter...
2 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
3 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter... drop table emp;
4 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
5 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
7 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
8 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
9 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
10 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
13 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
2 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected
3 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
4 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
5 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
7 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter... CREATE TABLE EMP(
8 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
9 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
10 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected Enter...
13 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2 no rows selected
18 rows selected. SQL> set autotrace off statistics</source>
analyze table students compute statistics
<source lang="sql">
SQL> SQL> create table students
2 ( studentID number constraint students_pk primary key, 3 name varchar2(10) );
Table created. SQL> SQL> create table documentMaster
2 ( documentId number constraint document_pk primary key, 3 description varchar2(10) );
Table created. SQL> SQL> create table admission_docs
2 ( studentID references students, 3 documentId references documentMaster, 4 dt date, 5 constraint admission_pk primary key(studentID, documentId));
Table created. SQL> SQL> SQL> insert into students
2 select object_id, object_name 3 from all_objects;
SQL> SQL> SQL> insert into documentMaster
2 select ROWNUM, "doc " || ROWNUM 3 from all_users 4 where ROWNUM <= 5;
5 rows created. SQL> SQL> insert into admission_docs
2 select object_id, mod(ROWNUM,3)+1, created 3 from all_objects, (select 1 from all_users where ROWNUM <= 3);
SQL> SQL> SQL> analyze table students compute statistics
2 for table for all indexes for all indexed columns;
Table analyzed. SQL> SQL> analyze table documentMaster compute statistics
2 for table for all indexes for all indexed columns;
Table analyzed. SQL> SQL> analyze table admission_docs compute statistics
2 for table for all indexes for all indexed columns;
Table analyzed. SQL> SQL> SQL> set autotrace on SQL> variable bv number SQL> exec :bv := 1234 PL/SQL procedure successfully completed. SQL> SQL> select a.* , decode(b.dt,null,"No","Yes") submitted, b.dt
2 from ( 3 select * 4 from students, documentMaster 5 where students.studentID = :bv 6 ) a, admission_docs b 7 where a.studentID = b.studentID(+) 8 and a.documentId = b.documentId (+) 9 /
no rows selected
Execution Plan
Plan hash value: 3775454522
- | Id | Operation | Name | Rows | Bytes | Cost |
- | 0 | SELECT STATEMENT | | 5 | 550 | 2 | | 1 | NESTED LOOPS OUTER | | 5 | 550 | 2 | | 2 | VIEW | | 5 | 330 | 2 | | 3 | NESTED LOOPS | | 5 | 225 | 2 | | 4 | TABLE ACCESS BY INDEX ROWID| STUDENTS | 1 | 33 | 1 | |* 5 | INDEX UNIQUE SCAN | STUDENTS_PK | 1 | | | | 6 | TABLE ACCESS FULL | DOCUMENTMASTER | 5 | 60 | 1 | | 7 | TABLE ACCESS BY INDEX ROWID | ADMISSION_DOCS | 1 | 44 | | |* 8 | INDEX UNIQUE SCAN | ADMISSION_PK | 1 | | |
-
Predicate Information (identified by operation id):
5 - access("STUDENTS"."STUDENTID"=TO_NUMBER(:BV)) 8 - access("A"."STUDENTID"="B"."STUDENTID"(+) AND "A"."DOCUMENTID"="B"."DOCUMENTID"(+))
Note
- cpu costing is off (consider enabling it)
Statistics
1 recursive calls 0 db block gets 1 consistent gets 0 physical reads 0 redo size 564 bytes sent via SQL*Net to client 369 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
SQL> SQL> set autotrace off SQL> SQL> drop table students cascade constraints; Table dropped. SQL> SQL> drop table documentMaster cascade constraints; Table dropped. SQL> SQL> drop table admission_docs; Table dropped.</source>
analyze table TABLENAME compute statistics;
<source lang="sql">
SQL> analyze table TABLENAME compute statistics;
SQL> SQL> select Num_Rows, /*number of rows*/
2 Blocks, /*number of blocks used*/ 3 Num_Rows/Blocks /*number of rows per block*/ 4 from USER_TABLES 5 where Table_Name="TABLENAME";
SQL></source>
analyze table t compute statistics for table, for all indexes, for all indexed columns
<source lang="sql">
SQL> SQL> create table t
2 as 3 select to_char( to_date("01-jan-1995","dd-mon-yyyy")+rownum, "yyyymmdd" ) str_date, 4 to_date("01-jan-1995","dd-mon-yyyy")+rownum date_date 5 from all_objects 6 where rownum < 20 7 /
Table created. SQL> SQL> create index i1 on t(str_date); Index created. SQL> SQL> create index t_date_date_idx on t(date_date); Index created. SQL> SQL> analyze table t compute statistics
2 for table 3 for all indexes 4 for all indexed columns;
Table analyzed. SQL> SQL> set autotrace on explain SQL> select * from t
2 where str_date between "20001231" and "20100101" and rownum < 20;
no rows selected
Execution Plan
Plan hash value: 508354683
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 20 | 1 | |* 1 | COUNT STOPKEY | | | | | |* 2 | TABLE ACCESS FULL| T | 1 | 20 | 1 |
Predicate Information (identified by operation id):
1 - filter(ROWNUM<20) 2 - filter("STR_DATE">="20001231" AND "STR_DATE"<="20100101")
Note
- cpu costing is off (consider enabling it)
SQL> SQL> select * from t where date_date between to_date("20001231","yyyymmdd") and to_date("20100101","yyyymmdd") and rownum<20; no rows selected
Execution Plan
Plan hash value: 508354683
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 20 | 1 | |* 1 | COUNT STOPKEY | | | | | |* 2 | TABLE ACCESS FULL| T | 1 | 20 | 1 |
Predicate Information (identified by operation id):
1 - filter(ROWNUM<20) 2 - filter("DATE_DATE">=TO_DATE("2000-12-31 00:00:00", "yyyy-mm-dd hh24:mi:ss") AND "DATE_DATE"<=TO_DATE("2010-01-01 00:00:00", "yyyy-mm-dd hh24:mi:ss"))
Note
- cpu costing is off (consider enabling it)
SQL> SQL> SQL> select * from t
2 where str_date between "20001231" and "20100101" and rownum<20;
no rows selected
Execution Plan
Plan hash value: 508354683
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 20 | 1 | |* 1 | COUNT STOPKEY | | | | | |* 2 | TABLE ACCESS FULL| T | 1 | 20 | 1 |
Predicate Information (identified by operation id):
1 - filter(ROWNUM<20) 2 - filter("STR_DATE">="20001231" AND "STR_DATE"<="20100101")
Note
- cpu costing is off (consider enabling it)
SQL> SQL> select * from t where date_date between to_date("20001231","yyyymmdd") and to_date("20100101","yyyymmdd")
2 and rownum < 20;
no rows selected
Execution Plan
Plan hash value: 508354683
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 20 | 1 | |* 1 | COUNT STOPKEY | | | | | |* 2 | TABLE ACCESS FULL| T | 1 | 20 | 1 |
Predicate Information (identified by operation id):
1 - filter(ROWNUM<20) 2 - filter("DATE_DATE">=TO_DATE("2000-12-31 00:00:00", "yyyy-mm-dd hh24:mi:ss") AND "DATE_DATE"<=TO_DATE("2010-01-01 00:00:00", "yyyy-mm-dd hh24:mi:ss"))
Note
- cpu costing is off (consider enabling it)
SQL> SQL> set autotrace off SQL> SQL> drop table t; Table dropped.</source>
analyze table t compute statistics for table for columns id;
<source lang="sql">
SQL> create table t as select mod(object_id,10) id, a.* from all_objects a; SQL> SQL> analyze table t compute statistics
2 for table 3 for columns id;
SQL> SQL> SQL> SQL> alter session set optimizer_max_permutations=80000; SQL> SQL> explain plan for
2 select count(*) 3 from t t1, t t2, t t3, t t4, t t5, t t6 4 where t1.id = t2.id 5 and t1.id = t3.id 6 and t1.id = t4.id 7 and t1.id = t5.id 8 and t1.id = t6.id;
SQL> SQL> set autotrace off SQL> SQL> SQL> drop table t;</source>
Analyze table with user defined column type
<source lang="sql">
SQL> SQL> create or replace type address_type as object
2 ( city varchar2(30), 3 street varchar2(30), 4 state varchar2(2), 5 zip number 6 ) 7 /
Type created. SQL> create or replace type person_type as object
2 ( name varchar2(30), 3 dob date, 4 home_address address_type, 5 work_address address_type 6 ) 7 /
Type created. SQL> create table people of person_type
2 /
Table created. SQL> insert into people values ( "Tom", "15-mar-1965",
2 address_type( "R", "1 Street", "Va", "45678" ), 3 address_type( "A", "1 Way", "Ca", "23456" ) );
1 row created. SQL> / 1 row created. SQL> SQL> analyze table people compute statistics; Table analyzed. SQL> SQL> drop table people; Table dropped. SQL> SQL> drop type person_type; Type dropped. SQL> drop type address_type; Type dropped. SQL></source>