| index advises [message #508672] |
Mon, 23 May 2011 21:31  |
hannah00
Messages: 37
Registered: March 2005
|
Member |
|
|
below is my query
SELECT * FROM EMPLOYEES
WHERE EMP_NAME='JOHN' AND EMP_VALUE IN ('PCODE','CVALUE','JOF')
right now, SQL does full table scan and I am thiking to add an index, my question is should I create an index on EMP_NAME and EMP_VALUE together and just EMP_NAME in one index and EMP_VALUE in another index?
Thanks,
|
|
|
|
|
|
| Re: index advises [message #508675 is a reply to message #508672] |
Mon, 23 May 2011 22:17  |
hannah00
Messages: 37
Registered: March 2005
|
Member |
|
|
Thanks Blackswan,
I created two seperated indexes for two columns and for some reasons, the indexes never got picked up and it is still doing FTS. Any thoughts of why?
Thanks again,
|
|
|
|
| Re: index advises [message #508678 is a reply to message #508672] |
Tue, 24 May 2011 00:12 |
Yasir Hashmi
Messages: 304
Registered: April 2006
|
Senior Member |
|
|
Hi,
How many rows are there in the table?
How many are there with the condition emp_name='JOHN'
How many with EMP_VALUE IN ('PCODE','CVALUE','JOF')?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: index advises [message #509421 is a reply to message #509417] |
Fri, 27 May 2011 09:39 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
It opens the possibility index combines//bitmap conversions (Can't remember proper term) on the given query and still allows a single index scan if any other queries hit the individual columns?
I'm guessing but it's the first thing I thought of.
|
|
|
|
| Re: index advises [message #509423 is a reply to message #509420] |
Fri, 27 May 2011 09:43 |
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
BlackSwan wrote on Fri, 27 May 2011 15:36>But I am confused as to why someone would create two indexes on each column given the OP's query.
Rarely are indexes created to accommodate a single query & should be generalized to support whole application, IMO.
Don't see why that should mean 2 seperate indexes.
In a real system there will probably be other queries that use both columns, and those that only use one can still use the index.
And those that use both will perform better with a concatenated index than two single ones.
|
|
|
|
|
|
|
|
| Re: index advises [message #509428 is a reply to message #509427] |
Fri, 27 May 2011 09:56 |
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Unless index skip scan kicks in.
And if it doesn't and it's a problem I'd create an additional index on the other column alone.
Not sure what the advantage of two concatenated indexes is Kevin.
|
|
|
|
| Re: index advises [message #509431 is a reply to message #509428] |
Fri, 27 May 2011 10:11 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
The two indexes put to bed the question of leading columns. It is the older method before skip scan and since I have no experience with skip scan I can't make judgments about how good it is. If the query used a single column index on EMP_VALUE then you are loosing access efficiency based on EMP_NAME. Thus a concatenated index is better because it removes more rows before going to the table and this is the point of an index either by not visting them, or by filtering them before table access.
As for individual column indexes, I hate them. I see this so often it kills me. People just create an index on every column and figure they are good. I am dumb-founded every time I go to another area in my company that is having performance issues only to find that they have created 30 single column indexes on a table because no one wanted to take the time to see what was actually needed.
Yes it is very true that indexing should take into consideration as much of the code in the system as possible.
However, the question from OP only gave the one statement so I took it to mean how to index best for this query. To that end the first index is best.
Also, to re-iterate what was said before. FTS may be the right plan anyway given the size of the table and/or distribution of data.
Kevin
[Updated on: Fri, 27 May 2011 10:14] Report message to a moderator |
|
|
|
| Re: index advises [message #509432 is a reply to message #509431] |
Fri, 27 May 2011 10:16 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Kevin Meade wrote on Fri, 27 May 2011 16:11As for individual column indexes, I hate them. I see this so often it kills me. People just create an index on every column and figure they are good. I am dumb-founded every time I go to another area in my company that is having performance issues only to find that they have created 30 single column indexes on a table because no one wanted to take the time to see what was actually needed.
Setting DML issues aside for a moment and looking at indexes for selects in isolation (I'm aware of the limited scope here!).
In later versions (I'm not sure when it came in but 11G certainly can do it) the optimizer can convert b-trees across single columns into bitmaps and merge them. I nodded to it in my earlier post above.
I couldn't comment on performance being good or bad, but it certainly made me blink when I saw it happen in the plans.
Strikes me as the optimizer trying to make the best of a bad lot, at least in the example where I forced it, the indexes were inappropriate. But in a real environment, I guess it's nice to have, especially if those indexes are there *anyway* and it can find a cost effective way to merge them...why not.
ymmv of course.
Which brings me to my post before this one: It's a one-way ticket to "it depends" 
As Mr Kyte said (and I paraphrase badly)- if one thing was always true and a rule could be made - the optimiser would do it. There are no silver bullets
[Updated on: Fri, 27 May 2011 10:18] Report message to a moderator |
|
|
|
|
|
|
|
| Re: index advises [message #509437 is a reply to message #509431] |
Fri, 27 May 2011 10:25 |
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Kevin Meade wrote on Fri, 27 May 2011 16:11The two indexes put to bed the question of leading columns.
Indexes on (a,b) and (b) do that.
Any query that references a and b or just a will use the first index.
Any query that just references b will use the 2nd.
If you have an index on (a,b) then I can't see any advantage to index on (b,a) as opposed to just (b).
|
|
|
|
| Re: index advises [message #509438 is a reply to message #509437] |
Fri, 27 May 2011 10:37 |
|
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
I agree.
But also consider that an index on just b presumes the existance of an index on a,b; otherwise as I indicated earlier you miss out on efficiency of the index that only contains b given the OP's query. Sometimes we have to choose between making indexes in isolation vs. making indexes in the context of other indexes. b,a is independent of a,b because regardless of the existence of a,b, your query will be most efficient. What harm does adding a to the end of the index do? Besides, you don't really know which index it will use till it uses one.
Also, if we are going to consider that other queries may exist, then depending upon the query, b,a might be used over a,b. How about his one (assume a is a date column)
select *
from sometable
where a > somedate-30
and b in (1,2,3)
/
which index will be used? a,b or b,a? Depends upon cardinality (as always) but I am betting b,a is a real contender.
Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
