Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> star transformation not chosen because of bind variables
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);
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_id26 /
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 processedReceived on Thu Sep 25 2003 - 15:32:17 CDT