11.INDEX

1.Bitmap Index

Database stores a bitmap for each index key. Indexes are  designed for data warehousing.   Indexed columns have low cardinality.The indexed table is either read-only or not subject to significant modification by DML statements.For a data warehouse example, the customer table has a cust_gender column with only two possible values: M and F. 

Bitmap index on a single table

}  SQL> SELECT cust_id, cust_last_name, cust_marital_status, cust_gender  FROM customers  WHERE ROWNUM < 8 ORDER BY cust_id;

 CUST_ID   CUST_LAST_    CUST_MAR     CUST_GEND

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

    1               Kessel                                                      M

    2               Koch                                                        F

    3               Emmerson                                               M

    4               Hardy                                                      M

    5               Gowen                                                     M

 6               Charlessingle      single                            F

  7               Ingram                single                            F

Sample Bitmap Index

•       A mapping function converts each bit in the bitmap to a rowid of customer table.

•       Each bit value depends on the values of the corresponding row in the table. 

2.Function Based Index

A function-based index computes the value of a function  or expression involving one or more columns and stores it in the index.

For Eg:SELECT           employee_id, last_name, first_name,    12*salary*commission_pct AS "ANNUAL SAL" FROM            employees    WHERE  (12 * salary * commission_pct) < 30000    ORDER BY "ANNUAL SAL" DESC;

}  A function-based index can be either a B-tree or a bitmap index.

}  The function used for building the index can be an arithmetic expression or an expression that contains a SQL function, user-defined PL/SQL function.

Uses of function based index

Evaluating statements that contain functions in their WHERE clauses.The database only uses the function-based index when the function is included in a query.

For Eg: SELECT    employee_id, last_name, first_name,

            12*salary*commission_pct AS "ANNUAL SAL" FROM        employees       WHERE           (12 * salary * commission_pct) < 30000       ORDER BY "ANNUAL SAL" DESC;

Eg: for function base index

CREATE INDEX emp_total_sal_idx ON employees (12 * salary * commission_pct, salary, commission_pct);

 Optimization with function based index

}  The optimizer can use an index range scan on a function-based index for queries with expressions in  WHERE clause.

}  The range scan access path is especially beneficial when the predicate (WHERE clause) has low selectivity.

}  A virtual column is useful for speeding access to data derived from expressions.

}  column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

Eg:salary2 NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL

create table empsal
(
emp number(2),
sal1 number(3),
sal2 number(4),
sal3 NUMBER GENERATED ALWAYS AS (ROUND(sal1*(1+sal2/100),2)) VIRTUAL
);

3.Application Domain Index

Application Domain Index are also called extensible indexing.They allow you to create your own index structures that work just like indexes supplied by Oracle. (just like primary key index)

}  A good example of an application domain index is the way Oracle Database itself implements text indexes, which are commonly used to facilitate searching on large text items.

SQL> create index test_idx2 on test_tab(desc)
2*         indextype is ctxsys.context;