Oracle Partitioning Quick Primer Partitioned Tables and Indexes
Archive for the ‘Indexes’ Category
29 Apr
When to Rebuild Indexes
How can you determine if an index needs to be dropped and rebuilt? Expected answer: Run the ANALYZE INDEX command on the index to validate its structure and then calculate the ratio of LF_BLK_LEN/LF_BLK_LEN+BR_BLK_LEN and if it isn?t near 1.0 (i.e. greater than 0.7 or so) then the index should be rebuilt. Or if the [...]
15 Apr
Managing Indexes
BTree Index Creation CREATE INDEX hr.employees_last_name_idx ON hr.employees(last_name) PCTFREE 30 STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) TABLESPACE indx; Bitmap Index Creation CREATE BITMAP INDEX orders_region_id_idx ON orders(region_id) PCTFREE 30 STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) TABLESPACE indx; Index Guidelines • Balance query and DML needs • Place in separate tablespace [...]
15 Apr
Bitmap and BTree Indexes
According to conventional wisdom, Bitmap index is a preferred indexing technique for cases where the indexed attributes have few distinct values (i.e., low cardinality). The query response time is expected to degrade as the cardinality of indexed columns increase due to a larger index size. On the other hand, B-tree index is good if the [...]
