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