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
scottwmackey
Messages: 505
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 test
CREATE 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 #349305 is a reply to message #349304] Fri, 19 September 2008 15:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'd say activate a 10053 trace and you will know why optimizer chooses this access path.

Regards
Michel
Re: Why This Execution Plan [message #349316 is a reply to message #349304] Sat, 20 September 2008 00:19 Go to previous messageGo to next message
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 messageGo to next message
scottwmackey
Messages: 505
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 messageGo to next message
scottwmackey
Messages: 505
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 #349372 is a reply to message #349304] Sat, 20 September 2008 14:57 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
there really
CREATE INDEX t1_t2_id_idx ON t1(t2_id, t1_id);
is LOCAL in both cases?

recreate that index as LOCAL, and show us execution plan for this select:
SELECT t1_id
FROM t1
WHERE t1_type = 2

[Updated on: Sat, 20 September 2008 14:57]

Report message to a moderator

Re: Why This Execution Plan [message #349374 is a reply to message #349372] Sat, 20 September 2008 15:58 Go to previous messageGo to next message
scottwmackey
Messages: 505
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 messageGo to next message
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 messageGo to next message
scottwmackey
Messages: 505
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 #349385 is a reply to message #349377] Sun, 21 September 2008 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Michel, unless he deigns to respond with further information...

1/ I don't owe you any answer (but actually the one I gave will definitively give you the answer to the question in the title)
2/ I have other things to do on this week-end like repairing my fence or spending time with my parents
3/ I wanted to do it but replaying your case (I'm not sure I'll get the same result), taking a trace, analyzing it to extract the important part will take a couple of hours which I currently have not, so you have to wait
4/ If you have J. Lewis book (Cost-Base Oracle fundamentals), chapter 14 "The 10053 trace file" explain how to read the trace.
5/ Curently Metalink is unreachable but did you search if there is a document on this trace (I know there is one on 10046)?
6/ I don't think our answers preclude others to read and answer this topic, it is just week-end and if you follow other forums and topics you will see there is few answers.

Regards
Michel
Re: Why This Execution Plan [message #349392 is a reply to message #349304] Sun, 21 September 2008 02:38 Go to previous messageGo to next message
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 messageGo to next message
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!

@scott
Quote:
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 messageGo to next message
scottwmackey
Messages: 505
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 messageGo to next message
scottwmackey
Messages: 505
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 #349431 is a reply to message #349304] Sun, 21 September 2008 15:33 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
I think we can close this topic.
and for scottwmackey: do not ask stupid questions, adn you do not get stupid answers.
gl
Re: Why This Execution Plan [message #349432 is a reply to message #349419] Sun, 21 September 2008 15:47 Go to previous messageGo to next message
scottwmackey
Messages: 505
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_type
Quote:
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 messageGo to next message
scottwmackey
Messages: 505
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 messageGo to next message
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 messageGo to next message
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 messageGo to next message
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 messageGo to next message
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 messageGo to next message
rleishman
Messages: 3724
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
scottwmackey
Messages: 505
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
Previous Topic: How to change value of CLUSTERING_FACTOR of index
Next Topic: Query taking long time
Goto Forum:
  


Current Time: Wed Dec 07 10:22:26 CST 2016

Total time taken to generate the page: 0.09273 seconds