| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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 processed
Received on Thu Sep 25 2003 - 15:32:17 CDT
 |
 |