May I know that how index will give the performance in Oracle?
Kindly share your views here, so I can proceed further.
Thanks in advance.
Index is a huge topic in oracle. Based on the table data cardinality, index will work. Basically we have two types of indexes, 1. Bit map Index, 2. B-tree index. (balanced tree). and there are so many index attributes are there. unique index, clustered index, reverse key index, function based index, IOT, etc...!! based on the column data , we have to create particular index. First of all need to know what is simple index, what is composite index. Simple index will create on a only one column in a table. once you create index . then it will stores rowid for each row in index segment. when you fire a query, it will search for that particular column values and their corresponding rowid in index segment. so, process bit fast. There are some rules when you want to create index, when you don't want to create index. Sometimes index will give more burden for us . if a transaction table frequently facing DML operations, index health will spoiled. Then data is very less, and we have huge null values in a column index is not recommended. composite index, if you are creating index more than one column in a table is called composite index. But u should care when these column are joining in where clause. if it is mismatch, index will not work properly. what is balanced tree index, what is the mechanism inside oracle? it is a 8 hours class if a person known pretty well about index in oracle. coming to the bitmap index, very useful in DW environment. maintain null values also. how to know whether my index is working or not? select * from v$object_usage apply this command, you will know, your index is using or not? what is clustering factor? When function based index will work? What is IOT ?
When we have to create IOT ? so many facts we have to discuss. This is not that much easy. Please get minimum knowledge about index. then go through each index and scenarios. This is just base line I have given about index. if you need more info. read " Expert indexing in oracle databse 11g" Apress company - authors sam R. Alapati . you can download freely in the Net.
It works like the index of a book. You look for the chapter or topic you want to read in the index, and it tells you exactly at what page you can find it.
We can not say, indexing will always boosts the performance. It depends on selectivity. Like if we are indexing on a unique column it will boost performance to even 100 %. But, if we are indexing a column which are getting frequently updated or too many duplicate values then there may be chance of littlle or no performance boost.
Sign up for STechies
All the site contents are Copyright © www.stechies.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies. The site www.stechies.com is in no way affiliated with SAP AG.
Every effort is made to ensure the content integrity. Information used on this site is at your own risk.
The content on this site may not be reproduced or redistributed without the express written permission of
www.stechies.com or the content authors.