•B*Tree Indexes
- These are the common indexes in Oracle. They are simular construct to a binary tree,they provide fast access by key, to an individual row or range of rows, normally requiring very fewreads to find the correct row. The B*Tree index has several subtypes
oIndex Organised Tables
- A table stored in a B*Tree structure (see Oracle Index organisedTables.doc)
oB*Tree Cluster Indexes
- They are used to index the cluster keys (see Oracle Cluster Indexes.doc)
oReverse Key Indexes
- The bytes in the key are reversed.This is used to stop sequencal keys being on the same block like 999001, 999002, 999003 would be reversed to 100999, 200999,300999 thus these would be located on different blocks.
oDescending Indexes
- They allow data to be sorted from big to small (decending) instead of small to big (ascending).
•Bitmap Indexes
- With a bitmap index , a single index entry uses a bitmap to point to many rowssimultaneously, they are used with low data that is mostly read-only. They should not be used withOLTP systems.
•Function Based Indexes
- These are B*Tree or bitmap indexes that store the computed result of afunction on a row(s) - not the column data itself.
•Application Domain Indexes
- These are indexes you build and store yuorself, either in Oracle or outside of Oracle
•interMedia Text Indexes
- This is a specialised index built into Oracle to allow for keyword searchingof large bodies of text.
When to use a
B*Tree Index
In general B*Tree index would be placed on columns that were frequently used in the predicate of a query andexpect some small fraction of the data from the table to be returned. its is purely a function on how large of a percentage of the table you will need to access via the index and how hte data happens to be laid out. If you canuse the index to answer the question accessing a large percentage of the rows makes sense, since you areavoiding the extra scattered I/O to read the table. If you use the index to access the table you will need to ensureyou are processing a small percentage of the total table.
Bitmap Indexes
Bitmap indexes are structures that store pointers to many rows with a single index key entry. In a bitmap indexthere will be a very small number of index entries, each of which point to many rows. Bitmap indexes are bestused on low cardinality data, this is were the number of distinct ietms in the set of rows divided by the number of rows is a small number for example a gender column may have MF, F and NULL. If you had a table with20000 rows you would find that 3/20000 = 0.00015, this would be an ideal candidate for a bitmap index.Remember also that a single bitmap entry key points to many rows. If a session modifies the index then all of the rows that the index points to are effectively locked. Oracle cannot lock an individual bit in a bitmap indexentry; it locks the entire bitmap, this will seriously inhibit concurrency as each update will appear to lock potentially hundreds of rows preventing their bitmap columns from being concurrently updated.An Oracle bitmap index would look like
Function Based Index
Function-Based indexes give the ability to index computed columns and use theses indexes in a query, it allowsyou to have case insensitive searchs or sorts, search complex equations and to extends the SQL language byimplementing your own functions and operators and then searching on them. The main reason to use them are:
• They are easy to implement and provide immediate value
• They can be used to speed up existing applications without changing the application code or queries.There is some work that needs to be done before you can use Function-based indexes
• You must have the privilege QUERY REWRITE to create function-based indexes on tables in your ownschema
• You must have the privilege GLOBAL QUERY REWRITE to create function-based indexes on tablesin your own schema
• Function-based indexes only use the cost based optimiser (CBO) and never the rule base optimiser (RBO)
• Use SUBSTR to constrain return values from user written functions that return VARCHAR2 or RAWtypes. Optionally hide the SUBSTR in a view (preferred).
• For the optimiser to use function-based indexes, the following session or system variables must be set:
o QUERY_REWRITE_ENABLED=TRUE (means allow optimiser to rewrite the query to usefunction-based index)
oQUERY_REWRITE_INTEGRITY=TRUSTED (means that the optimiser should trust the codeof the programmer)You can enable the above with the ALTER SESSION or at the system level via ALTER SYSTEM or by settingthem in in the init.ora file.Function-based indexes will affect the performance of inserts and updates, if you insert or update frequently thismay not be an option for you.
Application Domain Indexes
Application domain indexing gives the user the ability to supply new indexing technology. Most people willnever make use of it this particular API to build a new index type. The inetMedia set of functionality,implemented using the Application Domain indexing feature will provide indexing on text, XML documentsand images.
Hint One
if you believe that a child table is getting locked via an un-indexed foreign key and you would like to prove ityou can issue:alter table
Hint Two
You will only see index blocks on the FREELIST when the index block is totally empty, only then can the block be reused. This is different to table blocks as table blocks can be on the FREELIST if they contain data