Choice to create composite index

Whenever you create index on multiple columns in a table then that index is called composite index.

Advantage of Using Composite Index:
----------------------------------------------------------------
Improved selectivity: As we all know index is created for the column that has higher selectivity means there are a few duplicate column values of the indexing columns. Sometimes two or more columns each with poor selectivity, can be combined to form a composite index with higher selectivity.

Reduced I/O: If composite index is created with all the columns selected by the query, then oracle can return the values from the index itself instead of accessing values from the table using ROWID scan unlike single key index.

To know more about ROWID scan and explain plan search within my blog.

Composite Key Usage in a Query
----------------------------------------------------------
It is important to maintain order of the index while creating composite key index. For example create index on column(x,y) is not same as of create index on column(y,x). So while creating composite index take special care of the order of the columns inside index. More specifically, a SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index. Order of columns inside index creation make the leading constructs.

An example will make you clear.
If I create index as

CREATE INDEX comp_indx ON tabl(a, b, c);
Then,
a, ab, and abc combinations of columns in where clause will use the index.
bc, b, and c combinations of columns in where clause will not use the composite index.

Th ordering of keys of composite index is given below.

Ordering Keys for Composite Indexes
-----------------------------------------------------------
•Create the composite index so the keys used in WHERE clauses make up a leading portion.

•If some keys are used in WHERE clauses more frequently, then while creating composite index make sure that the more frequently selected keys make up a leading portion to allow the statements that use only these keys to use the index.

•If all keys are used in the WHERE clauses equally often but the data is physically ordered on one of the keys, then place that key first in the composite index.


Choosing Keys for Composite Indexes
----------------------------------------------------------------------
•Consider to create of composite index on columns that is used frequently in WHERE clause condition combined with AND operation.

•If several queries select a combination of columns based on one or more columns then consider to create composite index based on these columns.

Comments

Post a Comment

Popular posts from this blog

ORA-04062: timestamp of procedure has been changed

Problem Description In the database I have created one procedure named a as below. create or replace procedure a(a number) as begin insert into t1 values(1); commit; end; / Now after creating database link using remote database machine whenever I access this procedure "A" it executes successfully and I get value "1" in table t1. Like below in example where orastdby_m is the database link, shaik is the schema name and value 1 is the argument value though argument value is not used in the procedure. SQL> exec shaik.a@orastdby_m(1); PL/SQL procedure successfully completed. Now in the source database machine I changed the procedure as below. Though you can change anything like any literal; adding space or remove space. I changed value to be inserted from 1 to 2. create or replace procedure a(a number) as begin insert into t1 values(2); commit; end; / Now in the other database whenever I execute the procedure using database link it throws error ORA-04062. But subseq...
Read more

ORA-00939: too many arguments -when case arguments exceed 255

Problem Description In all Oracle 10.1g and oracle 10.2.0.1, 10.2.0.2, 10.2.0.3 whenever there is more than 255 arguments inside CASE statements then following error is returned. ORA-00939: too many arguments for function But surprisingly on Oracle 9.2 database the same code does not return any error. Cause of the Problem As we see the same code return error in 10g but it is well on 9.2. In fact this is problem of oracle 9.2 that it does not return any error. It should return error if there is more than 255 arguments inside CASE statements. In oracle 9.2 this is bug number 2909927 and this bug is fixed in oracle 10.1 and 10.2. That's why in 10.2 and 10.1 error returns if arguments exceed 255. Up to Oracle 10.2.0.3 and earlier version, the arguments of CASE statement is limit to 255 and it does not support exceed over 255 and hence error return if it exceed. However in Oracle 10.2.0.4 and later the arguments limit of CASE statement has increased to 32767. Solution of the Problem Opt...
Read more

ORA-31655: no data or metadata objects selected for job

Problem Description You are going to do data pump export operation in order to export objects based on filtering via EXCLUDE or INCLUDE parameter of expdp. In this example you wanted tables 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP' and all the tables starting with word CV(like 'CV', 'CV_EXPERIENCE', 'CV_EDUCATION' etc) Your parameter file is like below. userid=smilebd/a directory=d dumpfile=b.dmp include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')" include =TABLE:"LIKE 'CV%' " And you invoke expdp as expdp parfile=d:\parfile.txt from command line. But it fails with below message on my windows PC. C:\>expdp parfile=d:\parfile.txt Export: Release 10.1.0.4.2 - Production on Thursday, 29 January, 2009 14:53 Copyright (c) 2003, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc tion With the Partitioning, OLAP and Data Mining ...
Read more