Home » RDBMS Server » Performance Tuning » index advises
index advises [message #508672] Mon, 23 May 2011 21:31 Go to next message
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 #508673 is a reply to message #508672] Mon, 23 May 2011 21:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>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?
separate indexes
Re: index advises [message #508675 is a reply to message #508672] Mon, 23 May 2011 22:17 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #508682 is a reply to message #508678] Tue, 24 May 2011 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
...Are statistics up to date?

In short: read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel
Re: index advises [message #508697 is a reply to message #508682] Tue, 24 May 2011 02:16 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Is your table big enough to merit using an index at all?
Re: index advises [message #508748 is a reply to message #508697] Tue, 24 May 2011 06:49 Go to previous messageGo to next message
jitendra.prakash
Messages: 8
Registered: October 2009
Junior Member
U should analyze yr table first....with analyze command.
Re: index advises [message #508750 is a reply to message #508748] Tue, 24 May 2011 06:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ No you MUST not use ANALYZE command
2/ Please read OraFAQ Forum Guide and do not use IM speak.

Regards
Michel
Re: index advises [message #509417 is a reply to message #508673] Fri, 27 May 2011 09:31 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
why two indexes on each column? I would have created a concatenated index.

create index I1 on employees (name,emp_value);

If you are worried about emp_value not being leading then do this too.

create index I2 on employees (emp_value,name);

But I am confused as to why someone would create two indexes on each column given the OP's query.

BlackSwan, can you clarify your response please. Why would you prefer these?

create index I1 on employees (name);
create index I2 on employees (emp_value);

Kevin
Re: index advises [message #509420 is a reply to message #509417] Fri, 27 May 2011 09:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>BlackSwan, can you clarify your response please. Why would you prefer these?

two independent indexes consume less space than two concatenated indexes.

>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.
Re: index advises [message #509421 is a reply to message #509417] Fri, 27 May 2011 09:39 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 #509426 is a reply to message #509423] Fri, 27 May 2011 09:47 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
I think we're approaching the universal generic tuning conclusion/advice at flank speed here: "It depends".

[Updated on: Fri, 27 May 2011 09:47]

Report message to a moderator

Re: index advises [message #509427 is a reply to message #509423] Fri, 27 May 2011 09:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>and those that only use one can still use the index.
Not if the one column is *NOT* the leading column.
Re: index advises [message #509428 is a reply to message #509427] Fri, 27 May 2011 09:56 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Kevin Meade wrote on Fri, 27 May 2011 16:11
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.


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" Wink

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 Wink

[Updated on: Fri, 27 May 2011 10:18]

Report message to a moderator

Re: index advises [message #509434 is a reply to message #509432] Fri, 27 May 2011 10:19 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
All true.

My experience with conversion to bitmap... is terrible. I have yet to see a plan where this actually improved anything. I have spent my time tring to remove it from plans where ever it shows up because it has been so bad for us, and this almost always came down to either collecting statistics correctly, or creating new indexes, or more usually both.

Thanks, Kevin
Re: index advises [message #509436 is a reply to message #509434] Fri, 27 May 2011 10:22 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
A a point of clarification, in a true STAR Schema, single column indexes are very likely the correct solution (single column bitmaps). But this is a special case situation. In fact I can count on my left hand the number of real STAR MODELS in my company and still have fingers left over.

Kevin
Re: index advises [message #509437 is a reply to message #509431] Fri, 27 May 2011 10:25 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Kevin Meade wrote on Fri, 27 May 2011 16:11
The 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 Go to previous message
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
Previous Topic: Statistics gathering approach
Next Topic: Slow query with distinct
Goto Forum:
  


Current Time: Fri Mar 29 10:34:50 CDT 2024