This post will use words “pl sql index” or “oracle index” very often. though it has been written for indices made on oracle. A Quick checklist on how to choose the right pl sql index, index type and what are the different points to consider while working on Indices. Target audience: Professionals with 2-5 years or more experience in ETL, DW, Data modelling, PROD sustain , performance engineering etc.
What is an oracle Index (pl sql index) ?
Index is a database structure built (optionally) on top of a table for faster retrieval of data.
Why an Index?
– Speeds the access of the data.
– Improve the performance of a SQL.
– Avoid Table Full scans while retrieving smaller sets of data.
pl sql Index – Characteristics
– Index is built on one or more columns of a table
– Index does not store the data physically
– Index may or may not be unique in nature as per your requirement
– Dropping an index does not drop the table. Application will continue to run without any impact on data.
– Index slow down the data loading into a table.
– Primary key and Unique key constraints always create an index by enforcing the uniqueness
– Generally while building an index, table data will not be allowed to undergo any changes. A quick exception is possible by building an index online. Building an Index online will allow you to perform parallel insert / update / delete.
– Binary Tree Index. A default index in Oracle. They serve good with primary key columns. In addition, there reverse key index and Descending Index under BTree category. Reverse key preserve data reversing the key value wherein Oracle preserve Descending Index data in descending order.
– Bitmap Index. Bitmap indices are for those columns those have non unique values / have low cardinality. Most suitable index for Datawarehouse Environments
– Functional based index. Index is created based on the results of the function., Analyze the table after creating Functional based index.
– Unique Index. To enforce uniqueness in the data set(s)
How to pick pl sql index ?
Points to remember to work on Indices –
– Choose the right kind of index you require. Follow the flowchart attached.
– Review indices periodically and drop the unwanted indices
– Some times more number of indices become an overhead to the performance. Keep a checkpoint on number of indices.
– While creating compound index, carefully choose the order of the columns in the index. Column which is most used / queries should come first
– Ensure you drop your indices before bulk data Loading and rebuild them after loading data.
– Know your application’s / project’s tablespace for indices as you want to create the new indices in the Index tablespace rather on a default system tablespace.
Your suggestions are welcome !!!!!! you circling us on google plus can help us grow.