SQL tuning

Hi all,

I have questions about this sql query:

Code:

---

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.

Quote:

---

Originally Posted by matthew00

Hi all,

I have questions about this sql query:

Code:

---

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.

Quote:

---

Originally Posted by PAVB

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.

---

PAVB,

No, sorry if I am not clear with my post.

On the table client_sim, there are only 100 rows on the columns ID_EVEN out of 40 million rows. On the column JOB_STATUS, it had the three values from the where clause and they are around 150,000 rows on two values: 'ACTIVE', NACTIVE' and around 20,000 rows on values: 'RUNNING'

I did build index in ID_EVEN and JOB_STATUS seperately and I also update stat using DBMS_STATS.GATHER_TABLE_STATS, but for some reason the index never been used unless I force the hint on the index.

Thoughts?

Quote:

---

I did build index in ID_EVEN and JOB_STATUS seperately and I also update stat using DBMS_STATS.GATHER_TABLE_STATS, but for some reason the index never been used unless I force the hint on the index.

---

how about Index stats? are you using cascade=> 'TRUE' option while gathering stats?

Thanks,

I had done that but still no luck.....

thoughts?

generate 10053 trace and see on what basis oracle is selecting the current plan.

Thanks,