SQL tuning

[PAVB]

Hi all,

I have questions about this sql query:

select start, medication, id_event, JOB_status from client_sim
where id_even='ATC' and JOB_status in ('ACTIVE', NACTIVE', 'RUNNING')

there are around 150,000 rows on two values and less then 20,000 rows on one value of job_status out out 40 mil. There are only less than 100 rows on id_even.

I did two tests: the first test I created two indexes: one index for each column and the second test I created one index for both columns. The index NEVER get picked up from both tests. When I added the INDEX hint on both tests, then the indexes got picked up; however, I noticed the cost is even higher then FTS. From these two tests, I can possibly conclude that the index is being used in a FULL SCAN and and it's better to do FTS.

1. is it correct analysis?
2. I am thiking about partition the table, what's best method to deal with this table?
3. Any other advises that can optimize the query?


Thanks so much for your inputs.

Shall we understand there are about 100 rows that match condition (id_even='ATC') out of 150,000 rows in the table?

If this is the case an index on id_even should be used - provided you have gathered performance statistics on table and index. Did you "analyzed" tablle and index? how old are those stats?

When no stats are present Oracle assumes normal distribution then FTS is most likely - build a non-unique index on ID_EVEN, gather starts, try again.