| SQL tuning [message #511146] |
Thu, 09 June 2011 13:43  |
hannah00
Messages: 37
Registered: March 2005
|
Member |
|
|
Hi all,
select start, medication, id_event, JOB_status from client_sim
where id_even='ATC' and JOB_status in ('ACTIVE', NACTIVE', 'RUNNING')
above the query, oracle will do FTS on the table and the table itself had 40 million row.
I already did two trial tests:
1. create on index for both columns: id_even and job_status
2. on different test I created two indexes: one for id_even and one for job_status.
run both scenerios but neither of them picked up the indexes. In other word, it still do FTS.
Any thoughts or advises.
Thanks,
|
|
|
|
|
|
| Re: SQL tuning [message #511150 is a reply to message #511146] |
Thu, 09 June 2011 15:17  |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
hannah00 wrote on Thu, 09 June 2011 14:43Hi all,
select start, medication, id_event, JOB_status from client_sim
where id_even='ATC' and JOB_status in ('ACTIVE', NACTIVE', 'RUNNING')
above the query, oracle will do FTS on the table and the table itself had 40 million row.
I already did two trial tests:
1. create on index for both columns: id_even and job_status
2. on different test I created two indexes: one for id_even and one for job_status.
run both scenerios but neither of them picked up the indexes. In other word, it still do FTS.
Sometimes a FTS is more efficient than using the index. How many rows out of all 40 million have those 3 values for job_status? How many rows have that value for id_even?
Read the links by BlackSwan. They will help to understand.
[edit] typo
[Updated on: Thu, 09 June 2011 15:17] Report message to a moderator |
|
|
|
| Re: SQL tuning [message #511160 is a reply to message #511146] |
Thu, 09 June 2011 22:30 |
hannah00
Messages: 37
Registered: March 2005
|
Member |
|
|
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 all and have a great weekend everyone.
|
|
|
|
|
|
| Re: SQL tuning [message #511249 is a reply to message #511146] |
Fri, 10 June 2011 07:33 |
Yasir Hashmi
Messages: 304
Registered: April 2006
|
Senior Member |
|
|
The usage of indexes not only depend on the cardinality of the rows alone. It also depends on the cardinality of the blocks those rows are contained in.
It could be that the rows returned by the query are scattered throughout the blocks.As goal of tuning is to minimize block read ,It might be a possibility that though the rows are few but distributed throughout the blocks.So index access would mean single I/o for many blocks resulting in larger block visit hence the cost is high.With full table scans,oracle would do multiple I/Os hence might be efficient.
The defining factor for using the index is the clustering factor.
If the clustering factor is closer to the number of rows,oracle won't use the index.
|
|
|
|
|
|
| Re: SQL tuning [message #511498 is a reply to message #511146] |
Mon, 13 June 2011 08:34 |
hannah00
Messages: 37
Registered: March 2005
|
Member |
|
|
I read many places and "atler table table_name compute statistics" is NOT a proper way to update stat. should use DBMS_STATS.
In any case, my index still didn't get pick up, any thoughts?
|
|
|
|
|
|
|
|
| Re: SQL tuning [message #511556 is a reply to message #511146] |
Mon, 13 June 2011 21:46 |
hannah00
Messages: 37
Registered: March 2005
|
Member |
|
|
Thanks so much for your input......forgot to mention, unfortunately, this is standard edition so we can't use bitmap index.
Can I just create all three indexes?
|
|
|
|
|
|
| Re: SQL tuning [message #511558 is a reply to message #511160] |
Mon, 13 June 2011 21:54 |
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
|
Senior Member |
|
|
>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
An Explain Plan will show how many rows the Optimizer *expects* to have to delete. Get an Explain Plan and compare that with your known counts -- particularly if you know that the id_even predicate would qualify only 100 rows for deletion.
Hemant K Chitale
|
|
|
|
|
|
|
|
| Re: SQL tuning [message #511621 is a reply to message #511564] |
Tue, 14 June 2011 05:22 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
@msol25 - based on the available information there is no way you can be sure adding those indexes will solve anything, especially since the OP has already tried adding indexes.
@hannah00 - If you want help with this post the explain plan, it'll help us see what is happening.
|
|
|
|
| Re: SQL tuning [message #511661 is a reply to message #511557] |
Tue, 14 June 2011 08:02 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
msol25 wrote on Tue, 14 June 2011 03:53
But we shd use bitmap index where cardinality is high.
I assume that was meant to be SHOULDN'T use a bitmap on high cardinality?
|
|
|
|
| Re: SQL tuning [message #511670 is a reply to message #511661] |
Tue, 14 June 2011 09:07 |
 |
msol25
Messages: 396
Registered: June 2011
|
Senior Member |
|
|
Hi Roachcoach,
We should have very few values for job status as per assumtion so i told regarding bitmap index creation.We should always give choice to optimizer,so we need to create composite index and column based index.
begin
dbms_stats.gather_table_stats(
ownname => 'schema_name', --Give schema name
tabname => 'emp',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size skewonly',
cascade => true,
degree => 7
);
end;
Surely index will use in right way.
* < code tags > added by BlackSwan please do so yourself in the future
[Updated on: Tue, 14 June 2011 09:12] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: SQL tuning [message #514172 is a reply to message #511688] |
Sat, 02 July 2011 01:25 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Forget bitmap indexes. Everybody thinks they know what they are for but in reality few do. You don't need them here. Your issue is likely one of the following (which others have already touched upon):
1) your stats have not been collected correctly
2) you are looking at way more rows that you think
3) your indexes do not align well to the needs of your query
Please post the following for us to review:
1) the exact command you use to collect statistics
2) table create statement
3) index create statements
4) query you want to go fast
5) select count(*) from <table>
6) select count(*) from (<your query goes here>)
Once we have this we can offer some relevant advice.
It seems these days that there is a group espousing the advice to create lots of single column indexes. This is usually stupid and this trend annoys me. I do not mean to suggest that msol25 is stupid, only that he is premature is suggesting these indexes before we have the kind of information I have asked for. Indeed, to his credit, msol25 has given you an example DBMS_STATS that you can use to collect statistics which should be helpful to you.
Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
