Home » RDBMS Server » Performance Tuning » Index followup decision in case of composite b-tree index
Index followup decision in case of composite b-tree index [message #291572] Sat, 05 January 2008 01:10 Go to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Hello Experts,

I am newbee in DBA field.
I come to read the following thing in the book of oracle press named "oracle 9i Perfomance Tuning tips & techniques" by richard j. Niemiec.
I read a tip that
Quote:

Prior to oracle 9i, a concatednated index is used only if the leading edge of index is being used

(page no 37)
What I understood from this is
"from 9i, now, it is not necessary to include all leading eged fields unnecesarily in select statement's where clause to let the optimizer chose that index to follow (assuming all other conditions met for that)." Only the condition with required column is enough for that."
I tried this on sample tables, which is giving the result which satisfies above statement.
But still , before reaching to any conclusion , i would like to share this with you, and to get your suggestions and ideas on this.

Please, tell, if i am wrong any where.

Thanking you all..

Dipali


[Updated on: Sat, 05 January 2008 01:21] by Moderator

Report message to a moderator

Re: Index followup decision in case of composite b-tree index [message #291574 is a reply to message #291572] Sat, 05 January 2008 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, in 9i introduced INDEX SKIP SCAN operation that allows the optimizer to choose an index even if the leading columns are not present in the where clause.
This does not mean it will do it each time but just it has the ability to choose it if this is cheaper than other access paths.

Database Performance Tuning Guide
Chapter 13 The Query Optimizer
Section 13.5 Understanding Access Paths for the Query Optimizer
Subsection 13.5.3 Index Scans
Paragraph 13.5.3.5 Index Skip Scans

Regards
Michel
Re: Index followup decision in case of composite b-tree index [message #291759 is a reply to message #291572] Sun, 06 January 2008 11:51 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
NO, do not be mislead.

Michel is 100% correct (as usual). But this is also a dangerous question. It suggests there is something new that people can do to tune their queries. In fact though technically true, from a real use perspective this is not true. I would empahsis this way:

INDEX_SKIP_SCAN be dammed. You should not be designing indexes with INDEX_SKIP_SCAN in mind. You should be doing the same indexing analysis that you always did in the past when it comes time to decide what indexes to create. Let me put it to you this way:

Before INDEX_SKIP_SCAN the BASIC rules of what to make for indexes was essentially this:

1) index primary keys
2) index unique keys
3) index foreign keys
4) index whatever else is an opportunity for app performance


Now that Oracle does INDEX_SKIP_SCAN the new set of BASIC rules of what to make for indexes is this:

1) index primary keys
2) index unique keys
3) index foreign keys
4) index whatever else is an opportunity for app performance


If Oracle decides some query can take advantage of INDEX_SKIP_SCAN then bonus score...

Or you could say you are missing an index you maybe should have had. Either perspecitive is valid.

anyone have comments? Kevin
Re: Index followup decision in case of composite b-tree index [message #291762 is a reply to message #291759] Sun, 06 January 2008 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

anyone have comments?

I agree with you.
You should have advantage of ISS only if you can't create a new index because of slow DML (or lack of space) or maybe you already have an efficient compressed index.

Regards
Michel
Re: Index followup decision in case of composite b-tree index [message #291763 is a reply to message #291759] Sun, 06 January 2008 12:10 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Thanks a lot michel and kevin.. Smile

And the other thing, i am confused about is:
I read at many places (books, sites) that,
"we should try to follow the sequence of fields in query's where clause as specified in composite index"
But no proper reason for that.

But whenever i tried practically,
I see that, whether the coditions follow the sequence as in index or not, it gives same throughput (explain plan's reuslt)
It was giving the same cost....

I am quite confused about this concept that whether the sequence of conditions containing fields which are indexed compositely matters or not..

for example,
say i have composte index of field f1,f2 and f3.
and in query conditions specified in order f3,f1,f2..
then does it metter to give different performance than what if i specify as
f1, f2 and f3 sequence in query..

(note: i am very sorry, i am not able to give demo of what i had tried pratically)

Hoping for your help to understand this fundamentals..

Thanking you a lot for replies..
Re: Index followup decision in case of composite b-tree index [message #291765 is a reply to message #291763] Sun, 06 January 2008 12:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

we should try to follow the sequence of fields in query's where clause as specified in composite index

This is completly useless. Hopefully the optimizer (even the old RBO) is smart enough to don't care about these conditions order (this does not mean this is the case for all types of condition).

Regards
Michel
Re: Index followup decision in case of composite b-tree index [message #291766 is a reply to message #291765] Sun, 06 January 2008 12:21 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

okay michel....
Thanks for the opinion...

and the other thing i understand yesterday was :
I was thinking about the performance of query in following situation..
in a query, there are multiple conditions, falling in two categories.
1) filtering conditions..
2) join conditions
Question is, what will give the better performance..
to specify filtering conditins first to join..
I study and think a lot and came to decision that..
if index support availble for any one, that one would b good to specify first..
if no one have index support available or both do have, then, specifying filtering conditins first would give good performance..

Am i correct..??

and i have some questions about Index orgaized tables also..
should i ask them here..
or should i make other thread..?
Re: Index followup decision in case of composite b-tree index [message #291767 is a reply to message #291766] Sun, 06 January 2008 12:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

what will give the better performance..
to specify filtering conditins first to join..

It does not and did never have any importance.
This is not a criteria to determine the execution plan.
Don't think about all that you are not the optimizer, the optimizer is inside the rdbms.

Quote:

i have some questions about Index orgaized tables also..
should i ask them here..
or should i make other thread..?

Before posting your question read
Database Concepts
Chapter 5 Schema Objects
Section Overview of Index-Organized Tables
Better read the whole chapter.
Better read the whole book, 90% of your questions are answered in this one and slighty less than 10% in Performance Tuning Guide

Regards
Michel


[Updated on: Sun, 06 January 2008 12:38]

Report message to a moderator

Re: Index followup decision in case of composite b-tree index [message #291770 is a reply to message #291572] Sun, 06 January 2008 13:19 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
consider this:

create table temp1 (e number,d number,c number,b number,a number);

select * from temp1 where d = 1 and e = 1;
select * from temp1 where e = 1 and d = 1;


what indexes do you need?

create index temp1i1 on temp1 (e,d);

or

create index temp1i1 on temp1 (d,e);


either index will work just as well in this situation. Order of tests in the where clause is irrelevant; which column is more restrictive than the other also irrelevant. what is important is the existence of these two tests, and the types of tests (equality in this case).

what we don't do is create two indexes

create index temp1i1 on temp1 (e,d);
create index temp1i2 on temp1 (d,e);


For the select statements above, these two indexes are redundant. You only need one, not both.

does this clarify what you had read?

Kevin
Re: Index followup decision in case of composite b-tree index [message #292036 is a reply to message #291770] Mon, 07 January 2008 09:20 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Hello michel and kevin..

Michel.
Thankyou very much for that link..
Actually i searched a lot on google and other sites and documentation (9i) for IOT, but everywhere i was getting just the introduction. But before suggesting it to my company, i need to know many details about it.
The link given by you is proving most of the things i was looking for. Now i am quite clear about concepts.. Thanks.



Re: Index followup decision in case of composite b-tree index [message #292038 is a reply to message #292036] Mon, 07 January 2008 09:30 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Kevin..
Thanks for the explaination with demo.
But what i was asking is little bit different.

Our actual productin database' tables are having millions of records. It's primary keys are composite made up of more than 3 fields.
Queries for fetching data from them are having many conditions in where clause.Specially, reporting queries are quite lenghty, fetching data by joining tables..

In this queries, some conditions are filtering, which will decide resultset (may be of few hundreds records from millions).
And some conditions are joining coditions.

So my question was:
"if i would specify filtering conditions first(if it's having index support, ofcourse) and then joining conditions, then it would give somewhat faster performance."

I reached to this statment according to following logic:
Filtering coditions will filter records. So the resultset on which other coditions would be applied would be less, needing less records to be processed (compared), resulting in less processing time.

So, i was needing guidance from you seniors, whether i am going in proper directions or not..

Re: Index followup decision in case of composite b-tree index [message #292079 is a reply to message #291572] Mon, 07 January 2008 13:29 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
OK so lets say this then:

the order of tests in the where clause is irrelevant.

where a = 1 and b = 2 and c = 3

is same as

where c = 3 and b = 2 and a = 1

is same as

where b = 3 and a = 1 and c = 3

is same as ... (for all other variations)

what indexes get used, and what joins get done are not determined by order of tests in the where clause.

This question is asked many times (on OraFAQ too). I did some commenting last year about it so if you can find my replys here on OraFAQ you can get more details. But in the end a developer should not really be worrying about order of tests in the where clause.

Is this your questions and thus the answer you needed?

Kevin
Previous Topic: A question on Interpreting 10046 trace and the waits
Next Topic: Performance tuning of DDL?
Goto Forum:
  


Current Time: Sat Dec 10 10:56:53 CST 2016

Total time taken to generate the page: 0.08222 seconds