-Optional data structure that allows us to map a key value to a physical location
What does an index require?
-additional storage -additional accesses for retrieval -must be updated with database
When is performance improved with an index?
If the index fits into main memory.
Types of indexes (2)
-primary index -secondary index -B-Tree -Bitmap -Sparse Index -Dense Index
Based on unique (key) attributes that are used to physically order the data.
Based on nonordering attributes, but unique and nonunique.
Mother for storing more than one table per block.
-physically reorders the way the records are stored in the table -one per table -leaf nodes contain the data pages PK creates if no other clustered index exists
-Logical order of rows does not match physical order of rows -leaf nodes contain index rows
forms a hierarchy of index records
lowest level (terminal) nodes
nodes that share a common parent
set of pointers (branches) from one node to another
number of siblings permitted
number of levels (root level = 0)
-All paths from root to leaf node are the same length -Each node that is not a leaf has least n/2 and at most n children, where n is the order of the B-tree -Leaf nodes contain at least (n-1)/2 and at most n-1 pointers to data record locations
-suited to high-cardinality attributes -default in most cases -for each PK and unique contraint, oracle automatically creates a B-tree index. -uses a large amount of space -updates on keys relatively inexpensive -useful for OLTP
-appropriate for data warehouses -appropriate for attributes with low cardinality (gender, Y/N, categorical) -updates to key columns very expensive -efficient for queries using OR predicates -useful for DSS
Given 2 attributes, if one has an index and the other does not, we can access the index first then search the selected rows for a specified value for the second attribute
Composite index when both attributes have individual indexes
Can use each index to retrieve the appropriate rows and compute the intersection
Composite index with both attributes
Makes an index on both attributes which can be used to directly retrieve required rows, but is more query specific
Rebuilding Indices - why would you do so?
-move index to a different tablespace -improve space utilization by removing deleted entries -change a reverse key index to a normal b-tree index, vice-versa
Dropping indices - why would you do this?
-drop & recreate before bulk load -drop indexes that are infrequently needed and build when necessary -drop and recreate invalid indexes
Which is indexed more, OLTP or OLAP?
anticipating usage and build accordingly
based on optimizer and query implementation plan
Too many indexes on OLTP has what effect?
Too many indexes on OLAP has what effect?
Can slow queries
-Balance query and DML needs -Place in separate tablespace (maximizes i/o while minimizing disk access -Use uniform extend sizes -Consider NOLOGGING for large indices -set high PCTFREE if new key values are likely to be within the current range