INDEXES

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

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

Execution Plan

----------------------------------------------------------

Plan hash value: 3168395047

--------------------------------------------------------------------------------

---------

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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

----------------------------------------------------------

-------------------------------------------------------------------------

-------------------------------------------------------------------------

| 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

----------------------------------------------------------

-------------------------------------------------------------------------

-------------------------------------------------------------------------

| 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

Execution Plan

----------------------------------------------------------

Plan hash value: 662276915

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 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

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

| 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

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 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> 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

OracleByAjay

Search This Blog

INDEXES

Comments

Popular posts from this blog

RDBMS MINI PROJECT

PL / SQL practice programs

sample tables