29 terms

Indexes

STUDY
PLAY

Terms in this set (...)

What is an index?
-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
Primary Index
Based on unique (key) attributes that are used to physically order the data.
Secondary Index
Based on nonordering attributes, but unique and nonunique.
Cluster
Mother for storing more than one table per block.
Clustered index
-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
Non-clustered
-Logical order of rows does not match physical order of rows
-leaf nodes contain index rows
Rooted tree
forms a hierarchy of index records
Leaf nodes
lowest level (terminal) nodes
siblings
nodes that share a common parent
path
set of pointers (branches) from one node to another
degree
number of siblings permitted
depth (height)
number of levels
(root level = 0)
Balanced B-Tree
-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
B-Tree Indices
-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
Bitmap Indices
-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
Composite indexes
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?
OLAP
Proactive
anticipating usage and build accordingly
Reactive
based on optimizer and query implementation plan
Too many indexes on OLTP has what effect?
Slows updates
Too many indexes on OLAP has what effect?
Can slow queries
Oracle Indexing
-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