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
• Use uniform extent sizes: Multiples of five blocks or MINIMUM EXTENT size for  tablespace
• Consider NOLOGGING for large indexes
• INITRANS should generally be higher on indexes than on the corresponding tables.

Altering Index

ALTER INDEX employees_last_name_idx
STORAGE(NEXT 400K  MAXEXTENTS 100);

ALTER INDEX orders_region_id_idx
ALLOCATE EXTENT (SIZE 200K DATAFILE ‘/DISK6/indx01.dbf’);

ALTER INDEX orders_id_idx    DEALLOCATE UNUSED;

ALTER INDEX orders_region_id_idx REBUILD
TABLESPACE indx02;

     ALTER INDEX orders_region_id_idx REBUILD ONLINE;

ALTER INDEX orders_id_idx COALESCE;

Use the ALTER INDEX command to:
• Move an index to a different tablespace
• Improve space utilization by removing deleted entries
• Change a reverse key index to a normal B-tree index and vice versa

 

Analyzing and Gathering Index STATS

analyze index HR.LOC_CITY_IX validate structure

After running this command, query INDEX_STATS to obtain information about the index as shown in the following example:

SELECT blocks, pct_used, distinct_keys,
lf_rows, del_lf_rows
FROM index_stats

EXEC DBMS_STATS.gather_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’);EXEC DBMS_STATS.gather_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’, estimate_percent => 15);

Dictionary Views

DBA_INDEXES
DBA_IND_COLUMNS
DBA_OBJECTS
IND$
INDEX_STATS

 

LINKS

CBO Stats

DBASUPPORT CBO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: