Re: star transformation not chosen because of bind variables

Jim Reesman wrote:

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

I can't wait to see the answer because from my experience hints are often ignored.
And yet others have repeated posted that they are not. This looks like a good test case.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)