Scribd
Upload
83 views4 pages

SQL Table Indexing Explained

1) The document demonstrates the difference in performance between a non-indexed table and indexed table for a SELECT statement. 2) A SELECT statement using a full table scan on a non-indexed table took 00:11:33.29 to execute. Using an index scan on an indexed table, the same SELECT statement took only 00:00:01.61 to execute. 3) There was a significant performance improvement of over 11 minutes by using an index to satisfy the SELECT statement versus a full table scan.

Uploaded by

methukupally
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
83 views4 pages

SQL Table Indexing Explained

1) The document demonstrates the difference in performance between a non-indexed table and indexed table for a SELECT statement. 2) A SELECT statement using a full table scan on a non-indexed table took 00:11:33.29 to execute. Using an index scan on an indexed table, the same SELECT statement took only 00:00:01.61 to execute. 3) There was a significant performance improvement of over 11 minutes by using an index to satisfy the SELECT statement versus a full table scan.

Uploaded by

methukupally
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
You are on page 1/ 4

DIFFERENCE BETWEEN NON-INDEXED AND INDEXED TABLE

############################################################################################

SCOTT>>select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
13-JAN-11 12.26.30.640314 PM +05:30

SCOTT>>set autotrace on
SCOTT>>set timing on
SCOTT>>desc emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(38)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SCOTT>>desc dept
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

SCOTT>>select a.empno,a.ename,a.job,a.hiredate,a.sal,a.deptno,b.dname,b.loc
2 from emp a,dept b
3 where a.deptno=b.deptno
4* and a.empno=(select max(empno) from emp)

EMPNO ENAME JOB HIREDATE SAL DEPTNO DNAME LOC


---------- -------- ------- ----------- ------ ------- ------------ ----------
92034659 MILLER CLERK 23-JAN-82 1300 10 ACCOUNTING NEW YORK

##############################################
# Elapsed: 00:11:33.29 #
##############################################
Execution Plan
----------------------------------------------------------
Plan hash value: 3269051146

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 91 | 307K (1)| 01:01:26 |
|* 1 | HASH JOIN | | 1 | 91 | 153K (1)| 00:30:44 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 61 | 153K (1)| 00:30:44 |
| 3 | SORT AGGREGATE | | 1 | 13 | | |
| 4 | TABLE ACCESS FULL| EMP | 77M| 957M| 153K (1)| 00:30:42 |
| 5 | TABLE ACCESS FULL | DEPT | 4 | 120 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):


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

1 - access("A"."DEPTNO"="B"."DEPTNO")
2 - filter("A"."EMPNO"= (SELECT MAX("EMPNO") FROM "EMP" "EMP"))

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
292 recursive calls
0 db block gets
2254721 consistent gets
1127491 physical reads
0 redo size
880 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SCOTT>>create index ind_empno on emp(empno);

Index created.

##############################################
# Elapsed: 00:07:41.14 #
##############################################
SCOTT>>conn / as sysdba
Connected.
SYS>>alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.56
SYS>>alter system flush shared_pool;

System altered.

Elapsed: 00:00:03.98
SYS>>conn scott/tiger
Connected.
SCOTT>>set autotrace on
SCOTT>>set timing on
SCOTT>>select a.empno,a.ename,a.job,a.hiredate,a.sal,a.deptno,b.dname,b.loc
2 from emp a,dept b
3 where a.deptno=b.deptno
4 and a.empno=(select max(empno) from emp);

EMPNO ENAME JOB HIREDATE SAL DEPTNO DNAME LOC


---------- ------- ------ ---------- ------ ------- ----------- ----------
92034659 MILLER CLERK 23-JAN-82 1300 10 ACCOUNTING NEW YORK

#############################################
# Elapsed: 00:00:01.61 #
#############################################
Execution Plan
----------------------------------------------------------
Plan hash value: 1735671770

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) Time |

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

| 0 | SELECT STATEMENT | | 1 | 91 | 2273 (1)| 00:00:28 |

|* 1 | HASH JOIN | | 1 | 91 | 2270 (1)| 00:00:28 |

| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 61 | 2268 (1)| 00:00:28 |

|* 3 | INDEX RANGE SCAN | IND_EMPNO | 308K| | 7 (58)| 00:00:01 |

| 4 | SORT AGGREGATE | | 1 | 13 | | |

| 5 | INDEX FULL SCAN (MIN/MAX) | IND_EMPNO | 1 | 13 | 3 (0)| 00:00:01 |

| 6 | TABLE ACCESS FULL | DEPT | 4 | 120 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):


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

1 - access("A"."DEPTNO"="B"."DEPTNO")
3 - access("A"."EMPNO"= (SELECT MAX("EMPNO") FROM "EMP" "EMP"))

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
698 recursive calls
0 db block gets
233 consistent gets
170 physical reads
0 redo size
880 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
1 rows processed

SCOTT>>spool off

OBSERVATION : SELECT STATEMENT EXECUTED ON


NON-INDEXED TABLE AND INDEXED TABLE.

TIME ELAPSED FOR SELECT STATEMENT WHEN USING

FULL TABLE SCAN : 00:11:33.29

INDEX SCAN : 00:00:01.61

DIFFERENCE : 11m 32s

TIP : INDEX SCAN WILL IMPROVE THE PERFORMANCE OF A


SELECT STATEMENT.

You might also like