Thursday, December 12, 2013

why query is get faster after create a index

Index is method, applied on table to access the data faster
If we do not create index on table, and we execute the query and then oracle engine do the full table scan to return the required data.

figure explanation

Branch Blocks and Leaf Blocks

A B-tree index has two types of blocks: branch blocks for searching and leaf blocks that store values. The upper-level branch blocks of a B-tree index contain index data that points to lower-level index blocks. In Figure , the root branch block has an entry 0-40, which points to the leftmost block in the next branch level. This branch block contains entries such as 0-10 and 11-19. Each of these entries points to a leaf block that contains key values that fall in the range.

Branch blocks store the minimum key prefix needed to make a branching decision between two keys. This technique enables the database to fit as much data as possible on each branch block. The branch blocks contain a pointer to the child block containing the key. The number of keys and pointers is limited by the block size.
The leaf blocks contain every indexed data value and a corresponding rowid used to locate the actual row. Each entry is sorted by (key, rowid). Within a leaf block, a key and rowid is linked to its left and right sibling entries. The leaf blocks themselves are also doubly linked. In Figure  the leftmost leaf block (0-10) is linked to the second leaf block (11-19).
 --------------

-> After create a index, Database retrieve the rows by traverse the index , instead of traverse the table. It help to to save the data input output reads and disk loads.

If sql statement only access indexed columns in select clause, then Database will load the index and start the index range scan.( it fetch the data after index scan).
example
select ename from emp where ename = 'smith';
It is assumed that the index is created on emp (ename) , and query contain only ename column in select clause of query, then database will do index scan and return desired result.

If sql statement have the others column instead of indexed columns in select clause of sql statement
then database retrive the other data with help of rowid (as u can see the figure , leaf block store rowid with each indexed item , which point the table rowid).
example
select ename,empno, mgr, sal, job from emp where ename = 'smith';
It is assumed that the index is created on emp (ename) , and query contain other columns in select clause of query, then database will do index scan and use rowid (which point to table data) and return the desired result.

No comments:

Post a Comment

web stats