SQL> select index_name from user_indexes where table_name ='EMP';
no rows selected
SQL> ED
Wrote file afiedt.buf
1* CREATE INDEX EMPNO_IDX ON EMP(EMPNO)
SQL> /
Index created.
SQL> select index_name from user_indexes where table_name ='EMP';
INDEX_NAME
------------------------------
EMPNO_IDX
SQL> SELECT * FROM EMP WHERE EMPNO =7369;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
SQL> SET AUTOTRACE ON
SQL> SELECT * FROM EMP WHERE EMPNO =7369;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
Execution Plan
----------------------------------------------------------
Plan hash value: 3168395047
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 1 | 35 | 2 (0)| 0
0:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 35 | 2 (0)| 0
0:00:01 |
|* 2 | INDEX RANGE SCAN | EMPNO_IDX | 1 | | 1 (0)| 0
0:00:01 |
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7369)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
822 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SET LINESIZE 299
SQL> DROP INDEX EMPNO_IDX;
Index dropped.
SQL> SELECT * FROM EMP WHERE EMPNO =7369;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 35 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7369)
Statistics
----------------------------------------------------------
184 recursive calls
0 db block gets
32 consistent gets
0 physical reads
0 redo size
822 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> -- CONCATEDNATED INDEX
SQL>
SQL> CREATE INDEX EMPNO_DEPTNO_IDX ON EMP ( EMPNO, DEPTNO);
Index created.
SQL> SELECT * FROM EMP WHERE EMPNO=7369;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
Execution Plan
----------------------------------------------------------
Plan hash value: 3913210743
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 35 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMPNO_DEPTNO_IDX | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7369)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
822 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> DROP INDEX EMPNO_DEPTNO_IDX;
Index dropped.
SQL> SELECT * FROM EMP WHERE EMPNO=7369;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 35 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7369)
Statistics
----------------------------------------------------------
184 recursive calls
0 db block gets
32 consistent gets
0 physical reads
0 redo size
822 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> DROP TABLE EMP_UNIQUE;
DROP TABLE EMP_UNIQUE
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE EMP_UNIQUE AS SELECT EMPNO,SAL,DEPTNO FROM EMP WHERE 1=2;
Table created.
SQL> DESC EMP_UNIQUE;
Name
---------------------------------------------------------------------------------------------------
EMPNO
SAL
DEPTNO
SQL> CREATE INDEX EMPNO_UNI ON EMP_UNIQUE(EMPNO);
Index created.
SQL> INSERT INTO EMP_UNIQUE VALUES (1,200,2);
1 row created.
Execution Plan
----------------------------------------------------------
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
Statistics
----------------------------------------------------------
3 recursive calls
24 db block gets
2 consistent gets
0 physical reads
0 redo size
669 bytes sent via SQL*Net to client
568 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
1 row created.
Execution Plan
----------------------------------------------------------
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
5 db block gets
1 consistent gets
0 physical reads
504 redo size
669 bytes sent via SQL*Net to client
568 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT * FROM EMP_UNIQUE;
EMPNO SAL DEPTNO
---------- ---------- ----------
1 200 2
1 200 2
Execution Plan
----------------------------------------------------------
Plan hash value: 662276915
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 78 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP_UNIQUE | 2 | 78 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
48 recursive calls
0 db block gets
20 consistent gets
0 physical reads
0 redo size
559 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> DROP INDEX EMP_UNIQUE;
DROP INDEX EMP_UNIQUE
*
ERROR at line 1:
ORA-01418: specified index does not exist
SQL> DROP INDEX EMP_UNIQUE;
DROP INDEX EMP_UNIQUE
*
ERROR at line 1:
ORA-01418: specified index does not exist
SQL> DROP INDEX EMP_UNI;
DROP INDEX EMP_UNI
*
ERROR at line 1:
ORA-01418: specified index does not exist
SQL> DROP INDEX EMPNO_UNI;
Index dropped.
SQL> SET AUTOTRACE OFF
SQL> CREATE UNIQUE INDEX EMPNO_UNI ON EMP_UNIQUE(EMPNO);
CREATE UNIQUE INDEX EMPNO_UNI ON EMP_UNIQUE(EMPNO)
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
SQL> TRUNCATE TABLE EMP_UNIQUE;
Table truncated.
SQL> CREATE UNIQUE INDEX EMPNO_UNI ON EMP_UNIQUE(EMPNO);
Index created.
SQL> INSERT INTO EMP_UNIQUE VALUES (1,200,2);
1 row created.
SQL> INSERT INTO EMP_UNIQUE VALUES (1,200,2);
INSERT INTO EMP_UNIQUE VALUES (1,200,2)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.EMPNO_UNI) violated
SQL> DROP INDEX EMPNO_UNI;
Index dropped.
SQL> -- FBI
SQL>
SQL> DESC EMP
Name
---------------------------------------------------------------------------------------------------
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
SQL> SELECT * FROM EMP WHERE ENAME ='SCOTT';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
SQL> CREATE INDEX ENAME_IDX ON EMP(ENAME);
Index created.
SQL> SET AUTOTRACE ON
SQL> SELECT * FROM EMP WHERE ENAME ='SCOTT';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
Execution Plan
----------------------------------------------------------
Plan hash value: 2063514001
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 35 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ENAME_IDX | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ENAME"='SCOTT')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
824 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE UPPER(ENAME) ='SCOTT'
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 35 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("ENAME")='SCOTT')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
824 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> DROP INDEX ENAME_IDX;
Index dropped.
SQL> CREATE INDEX ENAME_IDX ON EMP( UPPER(ENAME));
Index created.
SQL> SELECT INDEX_NAME , INDEX_TYPE FROM USER_INDEXES WHERE TABLE_NAME ='EMP';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
ENAME_IDX FUNCTION-BASED NORMAL
Execution Plan
----------------------------------------------------------
Plan hash value: 2949960496
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 390 | 47 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 3 | 390 | 47 (0)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 3 | 357 | 44 (0)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 3 | 348 | 41 (0)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 3 | 339 | 38 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 3 | 315 | 35 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 4 | 280 | 31 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 2 | 70 | 30 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 2 | 64 | 28 (0)| 00:00:01 |
|* 9 | INDEX SKIP SCAN | I_OBJ2 | 2 | | 26 (0)| 00:00:01 |
| 10 | TABLE ACCESS CLUSTER | USER$ | 1 | 3 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |
|* 12 | TABLE ACCESS CLUSTER | IND$ | 2 | 70 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 35 | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 0 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 0 (0)| 00:00:01 |
| 18 | TABLE ACCESS CLUSTER | TS$ | 1 | 3 | 1 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
| 20 | TABLE ACCESS CLUSTER | USER$ | 1 | 3 | 1 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |
| 22 | TABLE ACCESS CLUSTER | SEG$ | 1 | 11 | 1 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("IO"."NAME"='EMP')
filter("IO"."NAME"='EMP')
11 - access("IO"."OWNER#"="IU"."USER#")
12 - filter(BITAND("I"."FLAGS",4096)=0 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR
"I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=8 OR
"I"."TYPE#"=9))
13 - access("I"."BO#"="IO"."OBJ#")
14 - filter("O"."OWNER#"=USERENV('SCHEMAID') AND BITAND("O"."FLAGS",128)=0)
15 - access("O"."OBJ#"="I"."OBJ#")
17 - access("I"."INDMETHOD#"="ITO"."OBJ#"(+))
19 - access("I"."TS#"="TS"."TS#"(+))
21 - access("ITO"."OWNER#"="ITU"."USER#"(+))
23 - access("I"."TS#"="S"."TS#"(+) AND "I"."FILE#"="S"."FILE#"(+) AND
"I"."BLOCK#"="S"."BLOCK#"(+))
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
50 consistent gets
0 physical reads
0 redo size
502 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT * FROM EMP WHERE UPPER(ENAME) = 'SCOTT';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
Execution Plan
----------------------------------------------------------
Plan hash value: 2063514001
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 35 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ENAME_IDX | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("ENAME")='SCOTT')
Statistics
----------------------------------------------------------
24 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
824 bytes sent via SQL*Net to client
396 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>
SQL>
SQL> CREATE INDEX EMPNO_REV ON EMP(EMPNO) REVERSE;
Index created.
SQL> SET AUTOTRACE OFF
SQL> SELECT INDEX_NAME, INDEX_TYPE FROM USER_INDEXES WHERE TABLE_NAME ='EMP';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
ENAME_IDX FUNCTION-BASED NORMAL
EMPNO_REV NORMAL/REV
SQL> -- BITMAP INDEX
SQL>
SQL> CREATE BITMAP INDEX ON EMP(DEPTNO)
2 .
SQL> ED
Wrote file afiedt.buf
1* CREATE BITMAP INDEX DEPTNO_BMP ON EMP(DEPTNO)
SQL> /
Index created.
SQL> SELECT INDEX_NAME, INDEX_TYPE FROM USER_INDEXES WHERE TABLE_NAME ='EMP';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
ENAME_IDX FUNCTION-BASED NORMAL
Comments