| Index followup decision in case of composite b-tree index [message #291572] |
Sat, 05 January 2008 01:10  |
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 #291763 is a reply to message #291759] |
Sun, 06 January 2008 12:10  |
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
|
Senior Member |
|
|
Thanks a lot michel and kevin.. 
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 #291766 is a reply to message #291765] |
Sun, 06 January 2008 12:21 |
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 |
 |
Michel Cadot
Messages: 68665
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 |
 |
Kevin Meade
Messages: 2103
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 |
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 |
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 |
|
Kevin Meade
Messages: 2103
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
