| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Composite Index Order VS Query Order ?? , Tuning Docs URL ?.
On question #1. Presuming you're CBO and you have decent stats and don't run
out of permutations, text editting of the sql other than hints and getting
your query to exactly match an already parsed query in the pool *should*
have no influence on the final plan.
Now *unproven theory* I posit that Oracle could-should interrupt the cost compilation of various alternative plans as soon as the cost gets to a tie or worse with the existing best plan, so *if parse-permutation time is significant in your use*, then if we knew how to influence Oracle to try plans in some order without hamstringing the result, we might shave a bit off. Then we'll go through an age of new mythology on how to influence plans and ultimately Oracle will start allowing us to save outlines as optimizer starting points to beat so that the mythology will evaporate. This might even happen automatically. Maybe they'll even swap permutations for permute time. I have no known situations where this would actually improve database operational efficiency, but I'd bet a dozen donuts there are.
In short, the query text should not influence the plan you get, but it could possibly change how long it takes to get the winning plan. If you use hints or too few permutations, you may get a different plan.
IF you're RBO, all sorts of things can affect the plan you get.
Maybe Wolfgang has thought about this? Or others?
On question #2 I hesitate to re-start a thread on all those good URLs and I suggest you seach the very recent BOOKS thread (which was mispelled BOO).
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Ranjeesh K R.
Sent: Tuesday, July 13, 2004 10:54 AM
To: undisclosed-recipients:
Subject: Composite Index Order VS Query Order ?? , Tuning Docs URL ?.
Hi,
I was working on optimising a piece of code which was taking 10hrs + to
execute and all.So my queries are..
#1) Saw a table where the "order of query" on the table and the "order of
key"
are different . For faster results shouldn't they be in the same order.
for eg:
Select ... where A.ID = ... and A.Name = ...
The Key order on this table A NOW is Name & ID Order . Shouldn't they be in the order ID , Name ?..
#2) Can anybody recommend me any site , where I will get reliable information on Tuning PL/SQL ?.
With thanks in advance
Ranjeesh
 |
 |