Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> star transformation not chosen because of bind variables

star transformation not chosen because of bind variables

From: Jim Reesman <jimreesman_at_yahoo.com>
Date: 25 Sep 2003 13:32:17 -0700
Message-ID: <ef9d9e.0309251232.7d247cad@posting.google.com>

In the following listing, the first query has 2 literal values in the where clause. It results in a star transformation being chosen and the plan and response time are good. The second query has simply had the 2 literals replaced with bind variables. Despite including the STAR_TRANSFORMATION hint, the CBO chooses the full scan and index range scans (it does so seemingly without regard to _any_ hints - eg. FACT () ). How can I get the second query to use the first execution plan? I'm using 9.2.

TIA, Jim

SQL> set echo on
SQL> @lotxybincounts-lits.sql
SQL> /* lotxybincounts */
SQL> /* bin counts for each x,y in the lot */
SQL> SELECT
  2            die_x,
  3            die_y,
  4            bin_id,
  5            count(bin_id) freq
  6          FROM
  7          (
  8                  SELECT
  9                      /* die.x_coordinate */ die_x
 10                    , /* die.y_coordinate */ die_y
 11                    , sof.bin_id
 12                    , pass_count pass
 13                  FROM operation op
 14                    , lot l
 15                    , die_bin_result dbr
 16  --                , die die
 17                    , software_bin sof
 18                  WHERE (l.lot_lnkid = dbr.lot_lnkid)
 19                         AND (op.operation_lnkid =
dbr.operation_lnkid)
 20  --                     AND (die.die_lnkid = dbr.die_lnkid)
 21                         AND (sof.software_bin_lnkid =
dbr.software_bin_lnkid)
 22                             AND (l.lot_id = '6BCZ05775.1')
 23                             AND (op.operation_name = 'CP1')
 24          )
 25          group by die_x, die_y, bin_id;

3221 rows selected.

Execution Plan


   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3151 Card=92 Bytes=3
          588)

   1    0   SORT (GROUP BY) (Cost=3151 Card=92 Bytes=3588)
   2    1     HASH JOIN (Cost=3092 Card=15603 Bytes=608517)
   3    2       TABLE ACCESS (FULL) OF 'SOFTWARE_BIN' (Cost=2 Card=308
           Bytes=1540)

   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'DIE_BIN_RESULT' (Cos
          t=3087 Card=16077 Bytes=546603)

   5    4         BITMAP CONVERSION (TO ROWIDS)
   6    5           BITMAP AND
   7    6             BITMAP MERGE
   8    7               BITMAP KEY ITERATION
   9    8                 TABLE ACCESS (BY INDEX ROWID) OF 'LOT' (Cost
          =1 Card=1 Bytes=16)

  10    9                   BITMAP CONVERSION (TO ROWIDS)
  11   10                     BITMAP INDEX (SINGLE VALUE) OF 'LOT_IDX5
          '

  12    8                 BITMAP INDEX (RANGE SCAN) OF 'DIE_BIN_RESULT
          _I_LOT_LNKID'

  13    6             BITMAP MERGE
  14   13               BITMAP KEY ITERATION
  15   14                 TABLE ACCESS (BY INDEX ROWID) OF 'OPERATION'
           (Cost=2 Card=1 Bytes=7)

  16   15                   INDEX (RANGE SCAN) OF 'OPERATION_AK' (UNIQ
          UE) (Cost=1 Card=1)

  17   14                 BITMAP INDEX (RANGE SCAN) OF 'DIE_BIN_RESULT
          _I_OPERATION_LNK'





Statistics


          0  recursive calls
          0  db block gets
        203  consistent gets
          0  physical reads
          0  redo size
      45208  bytes sent via SQL*Net to client
       1842  bytes received via SQL*Net from client
        216  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       3221  rows processed

SQL>
SQL> @lotxybincounts-vars.sql
SQL> /* lotxybincounts */
SQL> /* bin counts for each x,y in the lot */
SQL>
SQL> variable c_lot_id varchar2(32);

SQL> variable c_operation_name varchar2(32); SQL> exec :c_lot_id := '6BCZ05775.1';

PL/SQL procedure successfully completed.

SQL> exec :c_operation_name := 'CP1';

PL/SQL procedure successfully completed.

SQL> SELECT

  2            die_x,
  3            die_y,
  4            bin_id,
  5            count(bin_id) freq
  6          FROM /*+ STAR_TRANSFORMATION */
  7          (
  8                  SELECT
  9                      /* die.x_coordinate */ die_x
 10                    , /* die.y_coordinate */ die_y
 11                    , sof.bin_id
 12                    , pass_count pass
 13                  FROM operation op
 14                    , lot l
 15                    , die_bin_result dbr
 16  --                , die die
 17                    , software_bin sof
 18                  WHERE (l.lot_lnkid = dbr.lot_lnkid)
 19                         AND (op.operation_lnkid =
dbr.operation_lnkid)
 20  --                     AND (die.die_lnkid = dbr.die_lnkid)
 21                         AND (sof.software_bin_lnkid =
dbr.software_bin_lnkid)
 22                             AND (l.lot_id = :c_lot_id)
 23                             AND (op.operation_name =
:c_operation_name)
 24          )
 25          group by die_x, die_y, bin_id
 26 /

3221 rows selected.

Execution Plan


   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2299 Card=92 Bytes=5
          704)

   1    0   SORT (GROUP BY) (Cost=2299 Card=92 Bytes=5704)
   2    1     HASH JOIN (Cost=2212 Card=15603 Bytes=967386)
   3    2       TABLE ACCESS (FULL) OF 'SOFTWARE_BIN' (Cost=2 Card=308
           Bytes=1540)

   4    2       HASH JOIN (Cost=2209 Card=16077 Bytes=916389)
   5    4         MERGE JOIN (CARTESIAN) (Cost=4 Card=1 Bytes=23)
   6    5           TABLE ACCESS (BY INDEX ROWID) OF 'OPERATION' (Cost
          =2 Card=1 Bytes=7)

   7    6             INDEX (RANGE SCAN) OF 'OPERATION_AK' (UNIQUE) (C
          ost=1 Card=1)

   8    5           BUFFER (SORT) (Cost=2 Card=1 Bytes=16)
   9    8             TABLE ACCESS (BY INDEX ROWID) OF 'LOT' (Cost=2 C
          ard=1 Bytes=16)

  10    9               INDEX (RANGE SCAN) OF 'LOT_AK' (UNIQUE) (Cost=
          1 Card=1)

  11    4         TABLE ACCESS (FULL) OF 'DIE_BIN_RESULT' (Cost=2185 C
          ard=3472535 Bytes=118066190)





Statistics


          0  recursive calls
          0  db block gets
      35860  consistent gets
      35704  physical reads
          0  redo size
      45425  bytes sent via SQL*Net to client
       1842  bytes received via SQL*Net from client
        216  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       3221  rows processed
Received on Thu Sep 25 2003 - 15:32:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US