Indexing the correct ways? in oracle...
729412Oct 29 2009 — edited Oct 29 2009hi 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