-
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.
-
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.
Last edited by PAVB; 06-11-2011 at 04:35 PM.
Reason: typo
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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?
-
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,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
I had done that but still no luck.....
thoughts?
-
generate 10053 trace and see on what basis oracle is selecting the current plan.
Thanks,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|