Re: star transformation not chosen because of bind variables

One of those things...
There are restrictions on star transformation, one of them is that you can not have bind variables in your query. Using bind variables in a data warehouse environment may not be a good idea in any case.

• Jusung Yang

jimreesman_at_yahoo.com (Jim Reesman) wrote in message news:<ef9d9e.0309251232.7d247cad_at_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 Fri Sep 26 2003 - 15:38:25 CDT