Table and Index Partitioning

Oracle Partitioning Quick Primer

Partitioned Tables and Indexes

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 ratio BR_BLK_LEN/ LF_BLK_LEN+BR_BLK_LEN is nearing 0.3.

 

analyze index i1 validate structure;
select lf_blks from index_stats;

 

 

One opinion:

index rebuilds are justified only if there are regular deletes and inserts of more than 20-30% of the records in the table.

 

select ‘analyze index ‘||owner||’.’||index_name||’ validate structure;’
from dba_indexes
where owner = ‘HR’;

 

CREATE OR REPLACE PROCEDURE analyze_index (owner IN VARCHAR2)
IS
   index_nam         VARCHAR2 (50);
   stmt              VARCHAR (100);
   index_ratio       NUMBER (3, 2);
   idx_rebuild_cnt   NUMBER        := 0;
   idx_valid_cnt     NUMBER        := 0;

   CURSOR dyn_cur
   IS
      SELECT index_name
        FROM dba_indexes
       WHERE owner = ‘HR’;
BEGIN
   FOR rec IN dyn_cur
   LOOP
      index_nam := rec.index_name;
      stmt :=
         ‘analyze index ‘ || owner || ‘.’ || index_nam
         || ‘ validate structure’;
      DBMS_OUTPUT.put_line (stmt);

      EXECUTE IMMEDIATE stmt;

      EXECUTE IMMEDIATE ‘insert into index_stats_log select * from index_stats’;

      SELECT lf_blk_len / (lf_blk_len + br_blk_len)
        INTO index_ratio
        FROM index_stats;

      IF (index_ratio < 0.7)
      THEN
         DBMS_OUTPUT.put_line (   ‘Index ratio of’
                               || index_nam
                               || ‘ is greater than 0.7: = ‘
                               || index_ratio
                              );
         idx_rebuild_cnt := idx_rebuild_cnt + 1;
      ELSE
         DBMS_OUTPUT.put_line (   ‘Index ratio of’
                               || index_nam
                               || ‘ is less than 0.7: = ‘
                               || index_ratio
                              );
         idx_valid_cnt := idx_valid_cnt + 1;
      END IF;
   END LOOP;

   DBMS_OUTPUT.put_line (‘Index Rebuild Count is ‘ || idx_rebuild_cnt);
   DBMS_OUTPUT.put_line (‘Index Without Rebuild Count is ‘ || idx_valid_cnt);
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
      — Consider logging the error and then re-raise
      RAISE;
END analyze_index;

 

select s.name,s.del_lf_rows*100/decode(s.lf_rows, 0, 1, s.lf_rows) pct_deleted, (s.lf_rows-s.distinct_keys)*100/ decode(s.lf_rows,0,1,s.lf_rows) distinctiveness, i.blevel blevel

from index_stats s, dba_indexes i

where i.index_name = s.name ;

 

One More Approach

– Find out indexes having height(blevel+1) > 4 i.e. Indexes having BLEVEL > 3
How:
SQL> select owner,index_name,table_name,blevel from dba_indexes where BLEVEL>3
– Analyze indexes and find out ratio of (DEL_LF_ROWS/LF_ROWS*100) is > 20
How:
First "Analyze the index with validate structure option" and then,

SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio FROM INDEX_STATS;

 

 

Rebuilding Indexes

Avoids writing to redo logs and speeds up the index rebuild.

Alter index <index_name> rebuild unrecoverable;

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

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 column values are of high cardinality due to its indexing and retrieving mechanisms.

In summary, bitmap indexes are best suited for DSS regardless of cardinality for these reasons:

• With bitmap indexes, the optimizer can efficiently answer queries that include AND, OR, or XOR. (Oracle supports dynamic B-tree-to-bitmap conversion, but it can be inefficient.)
• With bitmaps, the optimizer can answer queries when searching or counting for nulls. Null values are also indexed in bitmap indexes (unlike B-tree indexes).
• Most important, bitmap indexes in DSS systems support ad hoc queries, whereas B-tree indexes do not. More specifically, if you have a table with 50 columns and users frequently query on 10 of them—either the combination of all 10 columns or sometimes a single column—creating a B-tree index will be very difficult. If you create 10 bitmap indexes on all these columns, all the queries can be answered by these indexes, whether they are queries on all 10 columns, on 4 or 6 columns out of the 10, or on a single column. This limit is not imposed with bitmap indexes.

In contrast, B-tree indexes are well suited for OLTP applications in which users’ queries are relatively routine (and well tuned before deployment in production), as opposed to ad hoc queries, which are much less frequent and executed during nonpeak business hours. Because data is frequently updated in and deleted from OLTP applications, bitmap indexes can cause a serious locking problem in these situations.

Structure of a B-Tree Index

At the top of the index is the root, which contains entries that point to the next level in the index. At the next level are branch blocks, which in turn point to blocks at the next level in the index. At the lowest level are the leaf nodes, which contain the index entries that point to rows in the table. The leaf blocks are doubly linked to facilitate scanning the index in an ascending as well as descending order of key values.

Format of Index Leaf Entries
An index entry is made up of the following components:

• An entry header, which stores number of columns and locking information
• Key values consisting of length and value pairs for each key column
• ROWID of a row, which contains the key values

Index Leaf Entry Characteristics
In a B-tree index on a nonpartitioned table:
• Key values are repeated if there are multiple rows that have same key value.
• There is no index entry corresponding to a row that has all key columns that are NULL. Therefore a WHERE clause specifying NULL will always result in a full table scan.
• Restricted ROWID is used to point to the rows of the table, since all rows belong to the same segment.

Effect of DML Operations on an Index
The Oracle server maintains all the indexes when DML operations are carried out on the table. Here is an explanation of the effect of a DML command on an index:
• Insert operations result in the insertion of index entry in appropriate block.
• Deleting a row results only in a logical deletion of the index entry. The space used by the deleted row is not available for new entries until all the entries in the block are deleted.
• Updates to the key columns result in a logical delete and an insert to the index. The PCTFREE setting has no effect on the index except at the time of creation. A new entry may be added to an index block even if it has less space than that specified by PCTFREE.

Structure of a Bitmap Index

A bitmap index is also organized as a B-tree, but the leaf node stores a bitmap for each key value instead of a list of ROWIDs. Each bit in the bitmap corresponds to a possible ROWID, and if the bit is set, it means that the row with the corresponding ROWID contains the key value.

The leaf node of a bitmap index contains the following:
• An entry header, containing the number of columns and lock information
• Key values consisting of length and value pairs for each key column
• Start ROWID and End ROWID

• A bitmap segment consisting of a string of bits (The bit is set when the corresponding row contains the key value and is unset when the row does not contain the key value. The Oracle server uses a patented compression technique to store bitmap segments.)

The start ROWID is the ROWID of the first row pointed to by the bitmap segment of the bitmap—that is, the first bit of the bitmap corresponds to that ROWID, the second bit of the bitmap corresponds to the next row in the block, and the end ROWID is a pointer to the last row in the table covered by the bitmap segment. Bitmap indexes use restricted ROWIDs.

Using a Bitmap Index
The B-tree is used to locate the leaf nodes that contain bitmap segments for a given value of the key. Start ROWID and the bitmap segments are used to locate the rows that contain the key value.
When changes are made to the key column in the table, bitmaps must be modified. This results in locking of the relevant bitmap segments. Because locks are acquired on the whole bitmap segment, a row that is covered by the bitmap cannot be updated by other transactions until the first transaction ends.

B-Tree Index	Bitmap Index
Suitable for high-cardinality columns	Suitable for low-cardinality columns
Updates on Keys relatively easy	Updates on Key columns very expensive
Inefficient for queries using OR predicates	Efficient for queries using OR predicates
Useful for OLTP	Useful for datawarehousing