OCP - Oracle9i Database Administrator Fundamentals 1 Exam Prep - Module 12: Managing Indexes

Practice 28: List the different types of indexes and their uses.

This practice covers determining the types of indexes in use in your database.

INSTRUCTIONS

1.      Determine the various types of indexes available in your database.  Note, you may not have all of the types listed here.

 

SQL> CONNECT system/manager

Connected.

SQL> select unique(index_type) from dba_indexes;

 

INDEX_TYPE

---------------------------

BITMAP

CLUSTER

FUNCTION-BASED NORMAL

IOT - TOP

LOB

NORMAL

NORMAL/REV

 

7 rows selected.

 

 

Practice: Create various types of indexes.

 

This practice covers the creation of normal and bitmap indexes.

 

Instructions

 

1.      You are considering creating indexes on the NAME and REGION columns of the CUSTOMERS table. What types of index are appropriate for the two columns? Create two indexes, naming them CUST_NAME_IDX and CUST_REGION_IDX, respectively, and placing them in the INDEX01 tablespace.

 

Hint

 

 A B-tree index is suitable for a column with many distinct values, and a bitmap index is suitable for columns with only a few distinct values. CUSTOMERS table is in the SYSTEM schema.

 

 

SQL> CONNECT system/manager

Connected.

 

SQL> CREATE INDEX cust_name_idx

  2    ON customers(name)

  3    TABLESPACE index01;

 

Index created.

 

SQL> CREATE BITMAP INDEX cust_region_idx

  2    ON system.customers(region)

  3    TABLESPACE index01;

 

 Index created.

 

 

 

2.      Determine the files and blocks used by the extents by CUST_REGION_IDX index.

       Hint: Query data dictionary view DBA_EXTENTS.

 

 

SQL> SELECT file_id, block_id, blocks

  2  FROM   dba_extents

  3  WHERE  owner = 'SYSTEM'

  4  AND    segment_name = ' CUST_NAME_IDX'

  5  AND    segment_type = 'INDEX';

 

   FILE_ID   BLOCK_ID     BLOCKS

---------- ---------- ----------

         14         17         16

 

 

 

3.      Use the ALTER INDEX command to allocate a second extent to the CUST_NAME_IDX index.

 

 

SQL> alter index cust_name_idx allocate extent;

Index altered.

 

 

 

4.      Validate that the new extent got allocated to the  CUST_NAME_IDX index. 

 

 

SQL> SELECT file_id, block_id, blocks

  2  FROM   dba_extents

  3  WHERE  owner = 'SYSTEM'

  4  AND    segment_name = ' CUST_NAME_IDX'

  5  AND    segment_type = 'INDEX';

 

   FILE_ID   BLOCK_ID     BLOCKS

---------- ---------- ----------

         14         17         16

         14         33         16

 

 

 

5.      You determined that the new extent just allocated to the CUST_NAME_IDX index is not needed so de-allocate the unused portion of the index using the ALTER INDEX command.

 

 

SQL> alter index cust_name_idx deallocate unused;

 

Index altered.

 

 

 

6.      Validate that the new extent got de-allocated to the  CUST_NAME_IDX index. 

 

 

SQL> SELECT file_id, block_id, blocks

  2     FROM   dba_extents

  3     WHERE  segment_name='CUST_NAME_IDX'

  4     AND    owner='SYSTEM';

 

   FILE_ID   BLOCK_ID     BLOCKS

---------- ---------- ----------

        14         17         16

 

 

7.      Create the following index.

 

 

SQL> create unique index cust_idx_cust on

2         customers(cust_code);

 

 Index created.

 

 

 

8.      Determine the various type of indexes that were created on the CUSTOMERS table and if they are unique or not.

 

 

SQL> select index_name, table_name, index_type, uniqueness

  2  from dba_indexes where table_name = 'CUSTOMERS'

  3  AND OWNER = 'SYSTEM';

 

INDEX_NAME         TABLE_NAME  INDEX_TYPE  UNIQUENES

------------------ ----------- ----------- ---------

CUST_IDX_CUST      CUSTOMERS    NORMAL       UNIQUE

CUST_NAME_IDX      CUSTOMERS    NORMAL       NONUNIQUE

 CUST_REGION_IDX    CUSTOMERS    BITMAP     NONUNIQUE

 

 

Practice: Reorganize indexes.

 

This practice covers the reorganization of regular and bitmap indexes.

 

Instructions

 

1.       Move the CUST_REGION_IDX index created in the last exercise to another tablespace.  Validate that the index did get moved.

 

       Hint: The index can be rebuilt specifying a different tablespace.

 

 

SQL> CONNECT system/manager

Connected.

SQL> ALTER INDEX cust_region_idx REBUILD

  2    TABLESPACE indx;

 

 Index altered.

 

 SQL> select tablespace_name

   2  from dba_indexes where index_name = 'CUST_REGION_IDX';

 

 TABLESPACE_NAME

 ------------------------------

 INDX

 

 

 

2.      Find the files and blocks used by the extents by CUST_REGION_IDX index.

 

      Hint: Use the view DBA_EXTENTS to get this information.

      

 

SQL> SELECT file_id, block_id, blocks

  2  FROM   dba_extents

  3  WHERE  owner = 'SYSTEM'

  4  AND    segment_name = 'CUST_REGION_IDX'

  5  AND    segment_type = 'TABLE';

 

FILE_ID   BLOCK_ID     BLOCKS

---------- ---------- ----------

         6         17         16

 

 

 

3.      Re-create the CUST_REGION_IDX index without dropping and re-creating it using the same tablespace as before. 

       Hint: Rebuild the index.

 

 

SQL> ALTER INDEX cust_region_idx REBUILD

  2    TABLESPACE indx;        

 

 

 

4.      Does the new index use the same blocks that were used previously? 

 

 

SQL> SELECT file_id, block_id, blocks

  2  FROM   dba_extents

  3  WHERE  owner = 'SYSTEM'

  4  AND    segment_name = 'CUST_REGION_IDX'

  5  AND    segment_type = 'TABLE';

 

FILE_ID      BLOCK_ID     BLOCKS

---------- ---------- ----------

        6         33         16

 

 

Note:  The new index does not reuse the same space as seen from the location of the extent after rebuild. This is because Oracle server builds a temporary index, drops the old one, and renames the temporary index.

1