In continuation with the last chapter, B*Tree with unique index, here are some thoughts on B*Tree with non unique index.
DEFINITION:
B*TREE non-unique index is one of the index types that is supported by oracle. If this index is imposed on a column, then all the non-null values don’t need to be unique. It means this index allows NULL value to be stored in the column of the table and all the non-NULL values can be same as well as different too. But all the index values in the index table would be unique. This is achieved by grouping all the ROWIDs of a single index value in a single index record. It means if 3 table records are there for a single value in a column on which this non-unique index is created, then in the index table, you will find only one index record but all these 3 ROWIDS would be mapped.
This non-unique index can be created on a single column or more than one column. More than one non-unique index can be created for the same data table.
HOW TO VERIFY:
How to verify whether the created index is non-unique and this non-unique index is referred by the sql query. If query uses, then you will find similar execution plan like this,
-------------------------------------------------------------------------------------------------------- |
| Id | Operation | Name | Rows | |
-------------------------------------------------------------------------------------------------------- |
| 0 | SELECT STATEMENT | | | |
| 1 | TABLE ACCESS (BY INDEX ROWID) | EMP | | |
| 2 | INDEX (RANGE SCAN) | DEPT_ID_INDX (NON-UNIQUE) | | |
In the explain plan, whenever it displays the non-unique index, it displays the keyword (NON-UNIQUE) immediately after the index name.
LITTLE-KNOWN FACTS TO BE REMEMBERED:
· For a single value, if more than one table records are available, then in the index table, all the ROWIDs of the table records would be maintained in the ascending order against this index value.
· Null can be stored(or understand) only in data table but not in index table. It means, suppose, I create a non-unique index on column-a of table-a, and after I insert 10 records. If out of these 10 records, suppose 8 records have some unique values and the remaining 2 records have NULL for this column-a. After creating this unique index, only 8 index records would be created in index table and the remaining 2 records (which has NULL in column-a) won’t be available in the index table.
EXAMPLE:
Create an employee table and inserts 10 records. The table will look like this,
DATA TABLE:
ROWID | empid | column2 | column3 | deptid | column4 | column5 |
AAAAA1 | 1 | .. | .. | SALES | .. | .. |
AAAAA2 | 2 | .. | .. | HR | .. | .. |
AAAAA3 | 3 | .. | .. | SALES | .. | .. |
AAAAA4 | 4 | .. | .. | ADMIN | .. | .. |
AAAAA5 | 5 | .. | .. | HR | .. | .. |
AAAAA6 | 6 | .. | .. | .. | .. | |
AAAAA7 | 7 | .. | .. | ADMIN | .. | .. |
AAAAA8 | 8 | .. | .. | SALES | .. | .. |
AAAAA9 | 9 | .. | .. | .. | .. | |
AAAAA10 | 10 | .. | .. | ADMIN | .. | .. |
Fire this query against this table where the requirement is to display the all the attributes of an employee whose deptid is “HR”
Select * from emp where deptid = ‘HR’;
Here, we are yet to create the non unique-index on deptid column. Now, to execute this query, Oracle will take 10 seconds (assume oracle takes 1 second for single data table record search).
Why it is taking 10 seconds? Reason is, Oracle follows the sequential search when it looks for the data in the data table. Here, we are looking for the deptid, ‘HR’. Since oracle is searching in the data table, it can’t directly go to 2nd and 5th records. Oracle starts from first record and traverse thro all the records till it reaches the last one. So in b/w, whichever table record has this deptid would be displayed in the output. Explain plan will look like this,
-------------------------------------------------------------------------------------------------------- |
| Id | Operation | Name | Rows | |
-------------------------------------------------------------------------------------------------------- |
| 0 | SELECT STATEMENT | | 2 | |
| 1 | TABLE ACCESS FULL | EMP | 10 | |
Problem here is, oracle has unnecessarily referred the unwanted 8 table records which are not meant to be referred for this sql.
In order to overcome this issue, we have to create the non-unique index on deptid column.
Why is deptid column the ideal candidate to impose B*TREE non-unique index? Because each department can have single employee or more than one employee and there will be a case where employee is yet to assigned to a particular department (In this case, NULL would be stored against this type of employees). Since in the WHERE clause, we refer this deptid column, it becomes the ideal column to impose this non-unique index
Create a non-unique index on this table for deptid column. (create index dept_id_indx on emp(deptid)).
Index table will logically look like this,
NON-UNIQUE INDEX TABLE:
INDEX | ROWID |
ADMIN | AAAAA4 |
AAAAA7 | |
AAAAA10 | |
HR | AAAAA2 |
AAAAA5 | |
SALES | AAAAA1 |
AAAAA3 | |
AAAAA8 |
First column (INDEX) : it stores all the distinct values of deptid column in the ascending order but omits 2 records (AAAAA6,AAAAA9) since it doesn’t have any value in deptid column.
Second column (ROWID) : it stores the ROWID of the corresponding records but all the ROWIDs for a single index value are grouped together.
After creating this index table, fire the same query again,
Select * from emp where deptid = ‘HR’;
When oracle executes this sql, first it looks for any index which has already been created on this “deptid” column. It comes to know that the index, “dept_id_indx” has already been created on “deptid” column. So, oracle refers the index table first before hitting the actual data table. Since we are looking for the deptid:’HR’, oracle hits index table first and get the corresponding ROWIDs (AAAAA2,AAAAA5). When oracle searches for the value in this index table, it follows random search (i.e. binary search) to go to the corresponding index record. Using this binary search, Oracle is intelligent enough to go to the 2nd index record directly in the index table. So, it won’t touch the remaining 2 index records.
After the getting the required ROWIDs (AAAAA2,AAAAA5), oracle directly refers the 2nd and 5th records of the data table since it knows the exact location (ROWID of 2nd record is AAAAA2 and ROWID of 5th record is AAAAA5). With this, it doesn’t refer the remaining 8 table records. Now, explain table will look like this,
-------------------------------------------------------------------------------------------------------- |
| Id | Operation | Name | Rows | |
-------------------------------------------------------------------------------------------------------- |
| 0 | SELECT STATEMENT | | 2 | |
| 1 | TABLE ACCESS (BY INDEX ROWID) | EMP | 2 | |
| 2 | INDEX (RANGE SCAN) | DEPT_ID_INDX (NON-UNIQUE) | 2 | |
Since this index table is used, oracle will take only 3 seconds (assume 1 second to get the ROWIDs (AAAAA2,AAAAA5) from 2nd index record since it follows binary search + 2 seconds to retrieve the actual table data records since we know the exact ROWIDs which are retrieved in the previous step)
ADVANTAGE:
· Query retrieval will be fast if the column (on which this non0unique index is created) is referred in the WHERE clause of the sql query
· In this non-unique index, random search is made to look for the indexed values instead of sequential search. Performance gain is achieved since the random search (which is used while searching for the data in this unique index table) is much faster than the sequential search (which is used while searching for the data in data table directly)
· In some cases, if all the required columns to be displayed available in the index table itself, then Oracle don’t need to refer the actual data table at all.
DISADVANTAGE:
· It consumes considerable amount of memory since it needs the space to store this unique index table.
· DML operations (Insert, Update, Delete, Merge) will be affected and this operations take addition time to execute the DML statements. This additional time is consumed by Oracle in order to update the index table when user tries to do any DML activity in the actual table since index table should be in sync with the actual table.
Comments
Post a Comment