Home » RDBMS Server » Performance Tuning » Why This Execution Plan
Why This Execution Plan [message #349304] |
Fri, 19 September 2008 15:24 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
scottwmackey
Messages: 515 Registered: March 2005
|
Senior Member |
|
|
Can anybody explain why the query below is using a full table scan on t1 instead of the index t1_t2_id_idx. The index has all the information it needs to return. The index is local so it should be able to account for t1_type = 2. I understand that the number of rows returned by the sub-select will determine if a hash join or nested loop is more appropriate. But I don't understand why the CBO thinks it will be faster to query the table. Is the CBO just not sophisticated enough to see that the type_id = 2, so just do a full index scan an on that partition of the index? Or is this one of those cases where Oracle can read faster from tables than from indexes despite the extra data that it would have to read from the table?
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set autotrace on
SQL> SELECT t1_id
2 FROM t1
3 WHERE t1_type = 2
4 AND t2_id IN (SELECT t2_id
5 FROM t2
6 WHERE col1 LIKE 'abc%');
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=809 Card=44435 Bytes=2488360)
1 0 HASH JOIN (Cost=809 Card=44435 Bytes=2488360)
2 1 PARTITION LIST (SINGLE) (Cost=656 Card=44435 Bytes=577655)
3 2 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=656 Card=444 35 Bytes=577655)
4 1 INDEX (RANGE SCAN) OF 'T2_COL1_IDX' (INDEX) (Cost=152 Card=100509 Bytes=4321887) Here's the full setup to replicate the testCREATE TABLE t1 PARTITION BY LIST (t1_type)(PARTITION p1 VALUES (1) ,PARTITION p2 VALUES (2), PARTITION p3 VALUES (3)) AS
SELECT lvl t1_id
,CASE
WHEN mod_lvl < 45 THEN 1
WHEN mod_lvl < 90 THEN 2
ELSE 3
END t1_type
,'a bunch of gibberish to take up space' col1
,'a bunch of gibberish to take up space' col2
,'a bunch of gibberish to take up space' col3
,'a bunch of gibberish to take up space' col4
,'a bunch of gibberish to take up space' col5
,'a bunch of gibberish to take up space' col6
,'a bunch of gibberish to take up space' col7
,'a bunch of gibberish to take up space' col8
,'a bunch of gibberish to take up space' col9
,lvl t2_id
,systimestamp + INTERVAL '1' SECOND insert_ts
FROM (SELECT LEVEL lvl
,MOD(LEVEL, 100) mod_lvl
FROM dual
CONNECT BY LEVEL <= 100000);
ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (t1_id);
CREATE UNIQUE INDEX t1_t2_id_udx ON t1(t2_id);
CREATE INDEX t1_t2_id_idx ON t1(t2_id, t1_id) LOCAL;
CREATE TABLE t2 AS
SELECT LEVEL t2_id
,'a bunch of gibberish to take up space' col1
,'a bunch of gibberish to take up space' col2
,'a bunch of gibberish to take up space' col3
,'a bunch of gibberish to take up space' col4
,'a bunch of gibberish to take up space' col5
,'a bunch of gibberish to take up space' col6
,'a bunch of gibberish to take up space' col7
,'a bunch of gibberish to take up space' col8
,'a bunch of gibberish to take up space' col9
FROM dual
CONNECT BY LEVEL <= 100000;
ALTER TABLE t2 ADD CONSTRAINT t2_pk PRIMARY KEY (t2_id);
CREATE INDEX t2_col1_idx ON t2 (col1, t2_id);
BEGIN
dbms_stats.unlock_table_stats(ownname => USER
,tabname => 't1');
dbms_stats.unlock_table_stats(ownname => USER
,tabname => 't2');
dbms_stats.gather_table_stats(ownname => USER
,tabname => 't1'
,cascade => TRUE);
dbms_stats.gather_table_stats(ownname => USER
,tabname => 't2'
,cascade => TRUE);
dbms_stats.lock_table_stats(ownname => USER
,tabname => 't1');
dbms_stats.lock_table_stats(ownname => USER
,tabname => 't2');
END;
/
|
|
|
|
Re: Why This Execution Plan [message #349316 is a reply to message #349304] |
Sat, 20 September 2008 00:19 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
the answer is simpl.
as you show, t1 is partitioned table by list.
partition key is t1_type.
in one partition all t1_type is the same (1 partition t1_type cardinality is 1)
in list partitioning you do not need index on t1_type clumn.
CBO gathering histograms to partition, not to table.
so it is a lot faster to get all rows from 1 partition using full table scan (one partition full scan in this case), then use index to get all rows from one partition.
[Updated on: Sat, 20 September 2008 00:20] Report message to a moderator
|
|
|
Re: Why This Execution Plan [message #349367 is a reply to message #349305] |
Sat, 20 September 2008 13:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
scottwmackey
Messages: 515 Registered: March 2005
|
Senior Member |
|
|
What exactly in the 10053 trace will tell me why it chooses one execution plan over another? The trace file is 72K, which I know is small for a trace file, but it's a fairly large file to read through while trying to guess which information is relevant to the question at hand.
|
|
|
Re: Why This Execution Plan [message #349369 is a reply to message #349316] |
Sat, 20 September 2008 14:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
scottwmackey
Messages: 515 Registered: March 2005
|
Senior Member |
|
|
Quote: | in list partitioning you do not need index on t1_type clumn.
CBO gathering histograms to partition, not to table.
so it is a lot faster to get all rows from 1 partition using full table scan (one partition full scan in this case), then use index to get all rows from one partition.
|
You completely missed my point. I know I don't need an index on t1_type. I don't have one. I know it is faster to do a full scan on a partition than accessing the partition through an index. My question was why is it accessing the partition at all. It doesn't need to. All of the information necessary to complete the query is in the indexes. So the comparison should be between and partition scan or an index scan.
See the example below for what I would think is completely analogous execution plan. If I remove the t1_type column altogether the new t1 table should, at least theoretically, be identical to a single partition in the old t1 table. In this case, it is doing exactly what I would expect it to do, a hash join on the two indexes and question, and NOT even touching the tables.
SQL> SELECT t1_id
2 FROM t1
3 WHERE t2_id IN (SELECT t2_id
4 FROM t2
5 WHERE col1 LIKE 'abc%');
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=128 Card=100527
Bytes=9650592)
1 0 HASH JOIN (Cost=128 Card=100527 Bytes=9650592)
2 1 INDEX (FULL SCAN) OF 'T1_T2_ID_IDX' (INDEX) (Cost=29 Card=102854 Bytes=1028540)
3 1 INDEX (RANGE SCAN) OF 'T2_COL1_IDX' (INDEX) (Cost=1 Card=100527 Bytes=4322661)
DROP TABLE t1;
DROP TABLE t2;
CREATE TABLE t1 AS
SELECT LEVEL t1_id
,'a bunch of gibberish to take up space' col1
,'a bunch of gibberish to take up space' col2
,'a bunch of gibberish to take up space' col3
,'a bunch of gibberish to take up space' col4
,'a bunch of gibberish to take up space' col5
,'a bunch of gibberish to take up space' col6
,'a bunch of gibberish to take up space' col7
,'a bunch of gibberish to take up space' col8
,'a bunch of gibberish to take up space' col9
,LEVEL t2_id
,systimestamp + INTERVAL '1' SECOND insert_ts
FROM dual
CONNECT BY LEVEL <= 100000;
ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (t1_id);
CREATE UNIQUE INDEX t1_t2_id_udx ON t1(t2_id);
CREATE INDEX t1_t2_id_idx ON t1(t2_id, t1_id);
CREATE TABLE t2 AS
SELECT LEVEL t2_id
,'a bunch of gibberish to take up space' col1
,'a bunch of gibberish to take up space' col2
,'a bunch of gibberish to take up space' col3
,'a bunch of gibberish to take up space' col4
,'a bunch of gibberish to take up space' col5
,'a bunch of gibberish to take up space' col6
,'a bunch of gibberish to take up space' col7
,'a bunch of gibberish to take up space' col8
,'a bunch of gibberish to take up space' col9
FROM dual
CONNECT BY LEVEL <= 100000;
ALTER TABLE t2 ADD CONSTRAINT t2_pk PRIMARY KEY (t2_id);
CREATE INDEX t2_col1_idx ON t2 (col1, t2_id);
BEGIN
dbms_stats.unlock_table_stats(ownname => USER
,tabname => 't1');
dbms_stats.unlock_table_stats(ownname => USER
,tabname => 't2');
dbms_stats.gather_table_stats(ownname => USER
,tabname => 't1'
,cascade => TRUE);
dbms_stats.gather_table_stats(ownname => USER
,tabname => 't2'
,cascade => TRUE);
dbms_stats.lock_table_stats(ownname => USER
,tabname => 't1');
dbms_stats.lock_table_stats(ownname => USER
,tabname => 't2');
END;
/
|
|
|
|
Re: Why This Execution Plan [message #349374 is a reply to message #349372] |
Sat, 20 September 2008 15:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
scottwmackey
Messages: 515 Registered: March 2005
|
Senior Member |
|
|
You're kidding, right? The entire setup script is in my initial post.Quote: | CREATE INDEX t1_t2_id_idx ON t1(t2_id, t1_id) LOCAL;
| The index can't be local in both cases. In the first case, t1 is partitioned and it is local. In the second case t1 is not partitioned, so it cannot be local.
|
|
|
Re: Why This Execution Plan [message #349376 is a reply to message #349304] |
Sat, 20 September 2008 16:18 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
I'm sorry, I do not look wery good, and havent notice that in second script table is not partitioned.
show me execution plan of select which i wrote, from partitioned table.
|
|
|
Re: Why This Execution Plan [message #349377 is a reply to message #349376] |
Sat, 20 September 2008 18:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
scottwmackey
Messages: 515 Registered: March 2005
|
Senior Member |
|
|
What could that possibly tell you? Let's say it does a full partition scan. What do we infer from that? Let's say it does a full index scan of the one partition. What do we infer from that?
I am extremely appreciative of all the folks who have helped me on several occasions on this site and would never disparage anybody's effort to help, especially when the one needing help is me. But you are not helping. I think you and Michel, unless he deigns to respond with further information, are actually hurting my chances of getting the help that I need with this question. Other users who might actually be able to help may see all the responses to my post on the main page and assume that you and Michel have answered my question and they will never bother to look at it. I followed all the rules. I posted the entire script recreate the issue. It takes all of 30 seconds to run. Any questions you may have can be easily answered by running it. If you don't want to run it, fine. You are under no obligation. But please stop cluttering my post with non-solutions and giving the illusion to others that my question has been addressed.
|
|
|
|
Re: Why This Execution Plan [message #349392 is a reply to message #349304] |
Sun, 21 September 2008 02:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
1) I started oracle on my PC
2) I run the script
3) resultas are the same lik yours
4) results MUST BE the same as you wrtite down
5) T1_ID on T1 table is UNICUE!!!
6) T2_ID on T1 table is UNICUE!!!
7) compostite index of 2 unicue columns IS HUGE!!!
8 ) to get to the leaf members of huge B-TREE index takes lots time
9)it is faster to join table and get all t1_id using full partition scan.
10) full index scan and full table scan uses diferent IO operations.
execution time with ful table scan was faster then index scan.
i made larger t1 table, with 100 columns
then full table scan was 3 times slower, but index scan was 3 times slower too.
create another index
CREATE INDEX t1_t2_id_idx ON t1(t2_id,t1_type ) LOCAL
and select not t1_id, but t1_type
and you see that optimiser uses full index scan.
and finaly YOU SO RUDE PERSON!!!
we try to help you, but you are some as%&*$le
USE TRACE and you see why full table scan is faster then index scan (is you know how to read trace files)
9)
6) reading UNICUE culm data from INDEX costs a lot, becaute index IS B-TREE!!!
[Updated on: Sun, 21 September 2008 02:38] Report message to a moderator
|
|
|
Re: Why This Execution Plan [message #349419 is a reply to message #349304] |
Sun, 21 September 2008 12:07 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
harrysmall3
Messages: 109 Registered: April 2008 Location: Massachusetts
|
Senior Member |
|
|
Quote: | execution time with ful table scan was faster then index scan.
|
Always the best way in my experience to understand 'Why' - when the performance is quicker!
@scottQuote: | it should be able to account for t1_type = 2.
| - if histograms were built.
But what truly would the alternative execution path entail (using the idex)?
I look at the cardinality for the range scan in the
EXISTS portion and see large rows processed.
I wouldn't want to do that in a nested loop.in one partition all Quote: | t1_type is the same (1 partition t1_type cardinality is 1)
|
I see what you intend in the desired plan, but for the number of
processed rows I dont see a full index scan (even an FFS) coming close to a table scan with
INDEX (FULL SCAN) OF 'T1_T2_ID_IDX' (INDEX) (Cost=29 Card=102854 Bytes=1028540)
And to re-state, all I provide is heresay without both methods
executed and true performance gaged. Only thing I can
trust!
Best regards
Harry
|
|
|
Re: Why This Execution Plan [message #349429 is a reply to message #349392] |
Sun, 21 September 2008 14:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
scottwmackey
Messages: 515 Registered: March 2005
|
Senior Member |
|
|
OK, so let's talk rude. This is the first line of your first response.Quote: | the answer is simpl.
| Simple? Let's examine that for a moment. I think the most appropriate definition of that word found in the Merriam-Webster dictionary would be "readily understood or performed". So what must one conclude of a person who doesn't understand a problem that is readily understood or performed? I think one must conclude that that person must be either inexperienced or simple. Given that I have been working with Oracle for work for over ten years, I am the technical lead for our DB team, and have helped out at least a couple of people on this forum, I think the former must be ruled out. That leaves only simple. I guess you might argue that I am reading far too much into a simple (irony intended) sentence, and if it were only that sentence, then perhaps I would agree. But the entire answer was based on the assumption that I and the question were simple. If you hadn't made that assumption, then you would have been a bit more careful to make sure you actually understood the problem before posting an answer.
So let's now examine the post for which I was called called "SO RUDE" and an "as%&*$le". I simply asked for you not respond if you didn't have a correct answer for me. I didn't use all caps or call you names. That might be considered rude. The fact that you are "trying" is, I would argue, irrelevant. I may not be correct in my belief that incorrect answers decrease my chances of getting a correct one, but if I am, then posting incorrect answers is the truly rude action. Even if I am incorrect, I think it is my right to request that my topic not be cluttered with unhelpful responses.
Beyond that, your latest post doesn't explain anything more than your previous posts.
|
|
|
Re: Why This Execution Plan [message #349430 is a reply to message #349385] |
Sun, 21 September 2008 15:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
scottwmackey
Messages: 515 Registered: March 2005
|
Senior Member |
|
|
Michel,
Find me one place in any of my posts in which I implied in any way, shape, or form that you owed me an answer. Quite the contrary, I have come to never expect an answer from you, well at least nothing beyond "Read the FAQ", "Format your post", or a link to a manual. I still have no clue why you are a moderator on this site given your obvious disdain for 95% of the posters here. So, I'll make a deal with you, just between you and me. Don't respond to any of my topics and I will refrain from responding from any of yours. I trust we will both happier.
|
|
|
|
Re: Why This Execution Plan [message #349432 is a reply to message #349419] |
Sun, 21 September 2008 15:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
scottwmackey
Messages: 515 Registered: March 2005
|
Senior Member |
|
|
Quote: | Always the best way in my experience to understand 'Why' - when the performance is quicker!
| I agree completely, but my question is, Why is it quicker? Note, however, that I am not convinced that the test Kriptas ran is testing what I am talking about. Kriptas didn't post the execution plan so my guess is that the query that was supposed to be testing the index range scan is still hitting the table, why else would the execution time increase when the table width increased.
That aside, I don't understand your comments. You say the CBO will only know about the partitions on t1_typeQuote: | if histograms were built.
| I thought histograms were only build on indexes. t1_type is not indexed, so I wouldn't think histograms would apply.
Quote: | But what truly would the alternative execution path entail (using the idex)?
| Well, I am expecting something like this with the scan of T1_T2_ID_IDX being limited to the partition in question.
SQL> SELECT t1_id
2 FROM t1
3 WHERE t2_id IN (SELECT t2_id
4 FROM t2
5 WHERE col1 LIKE 'abc%');
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=128 Card=100527
Bytes=9650592)
1 0 HASH JOIN (Cost=128 Card=100527 Bytes=9650592)
2 1 INDEX (FULL SCAN) OF 'T1_T2_ID_IDX' (INDEX) (Cost=29 Card=102854 Bytes=1028540)
3 1 INDEX (RANGE SCAN) OF 'T2_COL1_IDX' (INDEX) (Cost=1 Card=100527 Bytes=4322661)
I would agree that I wouldn't necessarily want it in a nested loop, but I wouldn't necessarily not want a nested loop. It all depends on how many rows are returned from the subquery. But I understand that the CBO cannot determine that before the query is actually run so it makes its best guess. But that is not my issue.
You say
Quote: | but for the number of
processed rows I dont see a full index scan (even an FFS) coming close to a table scan with
INDEX (FULL SCAN) OF 'T1_T2_ID_IDX' (INDEX) (Cost=29 Card=102854 Bytes=1028540)
| If that were true then why does the CBO generate the execution plan above. I didn't manually type this as a desired execution plan. That is real. The CBO decided that on a non-partitioned table, it is faster to do a full index scan than a full table scan. That's the CBO, not me making that claim. On a partitioned table, however, it thinks it is faster to do a full table scan limited to the partition than a full scan of the local index. My only question is why? What's the difference? Is there really a fundamental difference in the structures of the tables and indexes when they are partitioned which dramatically changes how fast they can be scanned, or is the CBO just not sophisticated enough to optimally deal with queries on partitioned tables with local indexes?
|
|
|
Re: Why This Execution Plan [message #349434 is a reply to message #349431] |
Sun, 21 September 2008 15:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
scottwmackey
Messages: 515 Registered: March 2005
|
Senior Member |
|
|
Oh, I think I understand now. Any question you can't answer is by definition a stupid question. Damn, I wish somebody would have informed me of these rules before I posted. Maybe you should get Michel to add them to the FAQ. I have an alternative to shutting down this thread. How about you just go away and we leave the topic open for anybody who might actually understand the question.
|
|
|
Re: Why This Execution Plan [message #349436 is a reply to message #349434] |
Sun, 21 September 2008 16:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
harrysmall3
Messages: 109 Registered: April 2008 Location: Massachusetts
|
Senior Member |
|
|
Hey Scott - just to clarify your question for my quote with
the
INDEX (FULL SCAN) OF 'T1_T2_ID_IDX' (INDEX) (Cost=29 Card=102854 Bytes=1028540)
I wasnt questioning the generation of this plan, just a NESTED LOOP approach that may be generated with utilization of the other index, such that this scan was executed for every record
of 'type='.
Believe, as you will tell from other posts of mine, I am a
hash_join fan and would like to see the execution of your desired plan. Can u force the plan with a hint and compare performance?
Quote: | do not ask stupid questions, adn you do not get stupid answers.
|
Why cant we all just keep this professional?
Regards
Harry
|
|
|
Re: Why This Execution Plan [message #349439 is a reply to message #349304] |
Sun, 21 September 2008 17:18 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
another thing I found.
If you run query :
SELECT t1_id
FROM t1 partition (p2)
WHERE t2_id IN (SELECT t2_id
FROM t2a
WHERE col1 LIKE 'abc%');
you will see the same execution plan as using not partitioned table.
if you add 't1_type=2' in where clause you will see the full table scan in execution plan.
so optimiser checks al data in partition 2 when in where clause is statement 't1_type=2'
using that 'PARTITION (partition_name)' directive queries in partitioned tables is running faster, some times 2-3 times faster comparing to other queries which have in where clause partition key filtering.
but in this case you need to know which partition9(s) to access.
|
|
|
Re: Why This Execution Plan [message #349440 is a reply to message #349304] |
Sun, 21 September 2008 19:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
harrysmall3
Messages: 109 Registered: April 2008 Location: Massachusetts
|
Senior Member |
|
|
Scott, sorry, i didnt address your other ?s on my response.
Quote: | That aside, I don't understand your comments. You say the CBO will only know about the partitions on t1_typeQuote:
if histograms were built.
I thought histograms were only build on indexes. t1_type is not indexed, so I wouldn't think histograms would apply.
|
Histograms can be built on any column. What I wanted to convey was that without histograms, the CBO would not know how many rows to expect back for a column in the where criteria.
You raise a good point in regards to partitions. This is something for me to look into - whether buckets are built within each partition as well.
But since t1_type IS the basis for the list partition, then
all rows would be expected. I'm getting thrown off a bit
with the plan in these regards:
Quote: | 2 1 PARTITION LIST (SINGLE) (Cost=656 Card=44435 Bytes=577655)
3 2 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=656 Card=444 35 Bytes=577655)
|
Oracle treats cardinality as selectivity * rows processed by
query. selectivity being the number of rows filtered / total rows of table.
Card = 444 listed un TABLE ACCESS full is my quandry.
Looks like I will set this scenario up tomorrow at work and
further my knowledge on access plans with partitions involved.
@Kriptas - do you have an explain-plan for your last test?
I know you specify plan is equivalent, but what about the
Partition List entry?
Thx
Good Evenings all
Harry
|
|
|
Re: Why This Execution Plan [message #349454 is a reply to message #349304] |
Sun, 21 September 2008 22:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
explain plan for last query
OPERATION Optimizer Cost Cardinality Bytes Partition Start Partition Stop Partition Id ACCESS PREDICATES FILTER PREDICATES
SELECT STATEMENT ALL_ROWS 205 45000 2385000
HASH JOIN 205 45000 2385000
PARTITION LIST(SINGLE) 31 45000 450000 2 2 2
INDEX(FAST FULL SCAN) KRIPTAS.T1_T2_ID_IDX ANALYZED 31 45000 450000 2 2 3
INDEX(FAST FULL SCAN) KRIPTAS.T2A_COL1_IDX ANALYZED 171 100848 4336464
[Updated on: Sun, 21 September 2008 22:44] Report message to a moderator
|
|
|
Re: Why This Execution Plan [message #349455 is a reply to message #349440] |
Sun, 21 September 2008 23:01 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Suspect this is a shortcoming in the optimiser.
It is technically possible to resolve the predicate WHERE t1_type = 2 using the local index t1_t2_id_idx simply because that index is local - even though it does not contain the t1_type column.
I seem to recall that the optimiser still requires all columns to be resolved to a table or index that contains them - even if it is technically redundant.
If you were to add t1_type as the first column of the locally partitioned index, you would get the result you're after.
Ross Leishman
|
|
|
Re: Why This Execution Plan [message #349479 is a reply to message #349455] |
Mon, 22 September 2008 00:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
scottwmackey
Messages: 515 Registered: March 2005
|
Senior Member |
|
|
Ross,
Once again, you are the man. That is the kind answer I was looking for. It's actually not what I wanted to hear, but you can't always get what you want. Thanks ever so much.
Scott
|
|
|
Goto Forum:
Current Time: Wed Sep 11 21:23:57 CDT 2024
|