Composite index vs normal index

oracleoracle-11g-r2

I need to index some tables and for that i found that there are different kinds of indexing. I am kind of clueless on their proper place of usage.
Should i create index on all the fields that want to retrieve quickly or Should i create composite index on couple of fields?
Is it OK to create composite indexes along with the normal indexes? suppose i have 3 fields :

FirstName 
LastName 
State

Can i individually index each field and then have different composit indexes on these fields as well?
For example by writing :

Create index indx_name on tblTest(FirstName);
Create index indx_lastname on tblTest(Last name); 
Create index indx_state on tblTest(state);
create index indx_cmp_fls on tblTest(FirstName,LastName,state); 
create index indx_cmp_lfs on tblTest(LastName,FirstName,state);
create index indx_cmp_sfl on tblTest(state,FirstName,LastName);
create index indx_cmp_slf on tblTest(state,LastName,FirstName);

Is it OK?
Is it suggested? if not what is the best way ?
Is it also OK to have index on all fields ?

Best Answer

Your question amounts to, "How do I use indexes?" You should read some Oracle documentation to get up to speed on how to use indexes and how to determine what columns to index. Oracle has an entire library of free online books and documentation for their products. A few good links to start:

The chapter on Indexes and Index-organized Tables from Oracle Database Concepts (the entire book is definitely worth reading)
The Oracle Database Performance Tuning Guide on Choosing Columns and Expressions to Index
Using Indexes in Database Applications from the Oracle Database Advanced Application Developer's Guide

Related Solutions

DROP PARTITION strategy using LIST-REF composite partitioning

global indexes are made unusable when you drop or move a partition. This is because there are entries pointing to physical addresses in your partition, that are no longer valid. If you can, try to avoid global indexes. You can do this by making them local.

create index idx_fk_opt_run_case on opt_run (case_id) local;

Should do the trick. If you can use reference partitioning, you don't have to add the redundant data in the table because the relation is already defined in the partition definition.

an example:

RONR SQL>create table parent
(id number not null
, x varchar2(10)
)
partition by list (id) (
partition par_1 values (1),
partition par_2 values (2)
);  2    3    4    5    6    7    8  

Table created.

RONR SQL>create unique index pk_parent
on parent (id) local;
  2  
Index created.

RONR SQL>alter table parent
add CONSTRAINT pk_parent
PRIMARY KEY (id)
USING INDEX;  2    3    4  

Table altered.

RONR SQL>create table child
( parent number not null
, y      varchar2(10)
,
constraint fk_p_c 
foreign key (parent) references parent(id))
partition by reference (fk_p_c);  2    3    4    5    6    7  

Table created.

RONR SQL>insert into parent (id, x) values (1,'boe');
insert into parent (id, x) values (2,'oeps');
1 row created.

RONR SQL>

1 row created.

RONR SQL>select * from parent;

    ID X
---------- ----------
     1 boe
     2 oeps

RONR SQL>insert into child (parent, y) values (1,'ggg');

1 row created.

RONR SQL>insert into child (parent, y) values (1,'ggg');

1 row created.

RONR SQL>insert into child (parent, y) values (2,'ppp');

1 row created.

RONR SQL>select * from child;

    PARENT Y
---------- ----------
     1 ggg
     1 ggg
     2 ppp

RONR SQL>select table_name, partition_name from user_tab_partitions;

TABLE_NAME             PARTITION_NAME
------------------------------ ------------------------------
PARENT                 PAR_1
PARENT                 PAR_2
CHILD                  PAR_1
CHILD                  PAR_2

RONR SQL>select index_name, partition_name, status from user_ind_partitions;

INDEX_NAME             PARTITION_NAME             STATUS
------------------------------ ------------------------------ --------
PK_PARENT              PAR_1                  USABLE
PK_PARENT              PAR_2                  USABLE

RONR SQL>alter table parent drop partition par_1;

Table altered.

RONR SQL>select * from parent;

    ID X
---------- ----------
     2 oeps

RONR SQL>select * from child;

    PARENT Y
---------- ----------
     2 ppp

RONR SQL>select table_name, partition_name from user_tab_partitions;

TABLE_NAME             PARTITION_NAME
------------------------------ ------------------------------
PARENT                 PAR_2
CHILD                  PAR_2

RONR SQL>select index_name, partition_name, status from user_ind_partitions;

INDEX_NAME             PARTITION_NAME             STATUS
------------------------------ ------------------------------ --------
PK_PARENT              PAR_2                  USABLE

I see no unusable index. What I do see is that I can drop a parent partition and this cascades to the child partition. does this help?

How does Oracle handle composite index lookups

Loosely speaking, the CBO may choose to:

build up a list of all possible values for the 'missing' leading columns (this can be done fairly efficiently from the index structure itself)
iteratively perform range scans for each combination of missing columns and the column provided
union the whole lot together in one result set

This is what is called a 'skip scan' in Oracle terminology. Skip scans work best when the number of possible values in step (1) is relatively small (that is small compared to the size of the index)

Under what circumstances can Oracle (at least in 11g) do a lookup without the left-most prefix columns existing in the query?

Oracle will use statistics to get an estimate of the cardinality of step (1) before weighing up if performing that many range scans will cost more than just scanning the whole index sequentially

Best Answer

Related Solutions

DROP PARTITION strategy using LIST-REF composite partitioning

How does Oracle handle composite index lookups

Related Question