Indexing the correct ways? in oracle...

729412

hi guys,

I need a suggestion on indexing the correct ways...

I have a table : PERSONAL_TASK.
The unique field for that table is STAFFID,MONTH and TASKID --- PARAMETER FOR A SINGLE ROW UPDATE
All the FIELD type is VARCHAR2

The data is about 20 million row.

My question is on indexing method.

should i index the table like this?

1. creating 3 INDEX
a. CREATE INDEX ix_emp_01 ON PERSONAL_TASK (STAFFID)
b. CREATE INDEX ix_emp_02 ON PERSONAL_TASK (MONTH)
c. CREATE INDEX ix_emp_03 ON PERSONAL_TASK (TASKID)

OR

2. just creating a concatenated index like this ?
CREATE INDEX ix_emp_01 ON PERSONAL_TASK (STAFFID,MONTH , TASKID)


My query usually select the table using the 3 field name
such as
1. select * PERSONAL_TASK where STAFFID = '111'
2. select * PERSONAL_TASK where STAFFID = '111' and MONTH = 'JAN'
3. select * PERSONAL_TASK where STAFFID = '111' and MONTH = 'JAN' AND TASKID = 'TASKID' - OR for update single row

so, which one is better in the performance issues when selecting and updating in a 20 million row of data?

thank you for the comment and suggestion.

Haire Kahfi
http://haire-family.blogspot.com/

Edited by: user12096581 on Oct 29, 2009 2:02 PM

Deepak_DBA

hi,


instead of creating concadinating indexes go for the first one.. it will be better..

you are going to use AND,OR operators in your where condition..
so the jobs are seperated and oracle will retrive data faster than concadinate index;.


regards,
Deepak

Hemant K Chitale

If the three columns together define uniqueness, then you must create a concatenated UNIQUE Index.

(This is better defined as a Primary Key if the columns are NOT NULL).


If your queries always specify STAFFID and optionallly additionally specify MONTH and TASKID the index might as well be in that order.

It doesn't seem as if MONTH and TASKID would have many distinct values, so single column indexes on these two may not be helpful.

Hemant K Chitale

EdStevens

hairekahfi wrote:
hi guys,

I need a suggestion on indexing the correct ways...

I have a table : PERSONAL_TASK.
The unique field for that table is STAFFID,MONTH and TASKID --- PARAMETER FOR A SINGLE ROW UPDATE
All the FIELD type is VARCHAR2

The data is about 20 million row.

My question is on indexing method.

should i index the table like this?

1. creating 3 INDEX
a. CREATE INDEX ix_emp_01 ON PERSONAL_TASK (STAFFID)
b. CREATE INDEX ix_emp_02 ON PERSONAL_TASK (MONTH)
c. CREATE INDEX ix_emp_03 ON PERSONAL_TASK (TASKID)

OR

2. just creating a concatenated index like this ?
CREATE INDEX ix_emp_01 ON PERSONAL_TASK (STAFFID,MONTH , TASKID)


My query usually select the table using the 3 field name
such as
1. select * PERSONAL_TASK where STAFFID = '111'
2. select * PERSONAL_TASK where STAFFID = '111' and MONTH = 'JAN'
3. select * PERSONAL_TASK where STAFFID = '111' and MONTH = 'JAN' AND TASKID = 'TASKID' - OR for update single row

so, which one is better in the performance issues when selecting and updating in a 20 million row of data?

thank you for the comment and suggestion.

Haire Kahfi
http://haire-family.blogspot.com/

Edited by: user12096581 on Oct 29, 2009 2:02 PM

I would first suggest you make sure you review the basic rules of data normalization and your table design to make sure you have appropriately identified and defined your primary key.

Aman....

Deepak_DBA wrote:
hi,


instead of creating concadinating indexes go for the first one.. it will be better..

you are going to use AND,OR operators in your where condition..
so the jobs are seperated and oracle will retrive data faster than concadinate index;.

This is interesting. Can you demonstrate it that using a single index over a composite where all three columns will be used together is going to get more benefit from individual indexes?

Aman....