OraFAQ Forum: Performance Tuning » Why This Execution Plan

Home » RDBMS Server » Performance Tuning » Why This Execution Plan

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Why This Execution Plan [message #349304]

Fri, 19 September 2008 15:24

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 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;
/