I was wondering if you could clarify something.

When I change the optimizer mode from first_rows_10 to first_rows_100 the cost
of the plan goes up, even though the consistant_gets and physical reads goes
down.

I do see that the cardinallity gets higher but still not sure why the cost goes
up instead of down?

SYSTEM-ORCL->alter session set optimizer_mode = first_rows_10;

Session altered.

Elapsed: 00:00:00.00
SYSTEM-ORCL->select * from customers c, sales s
2 where c.cust_id = s.cust_id
3 and cust_last_name = 'Smith'
4 and s.time_id <= '31-DEC-98';

359 rows selected.

Elapsed: 00:06:01.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=22 Card=10 Bytes=2890)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS' (Cost=22 Card=1
Bytes=130)
2 1 NESTED LOOPS (Cost=22 Card=10 Bytes=2890)
3 2 PARTITION RANGE (ITERATOR)
4 3 TABLE ACCESS (FULL) OF 'SALES' (Cost=3 Card=201 Bytes=31959)
5 2 BITMAP CONVERSION (TO ROWIDS)
6 5 BITMAP AND
7 6 BITMAP CONVERSION (FROM ROWIDS)
8 7 INDEX (RANGE SCAN) OF 'CUSTOMERS_PK' (UNIQUE)
9 6 BITMAP CONVERSION (FROM ROWIDS)
10 9 INDEX (RANGE SCAN) OF 'CUST_CUST_LAST_NAME_IDX'
(NON-UNIQUE) (Cost=1 Card=641)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
877531 consistent gets
2731 physical reads
0 redo size
21137 bytes sent via SQL*Net to client
756 bytes received via SQL*Net from client
25 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
359 rows processed

SYSTEM-ORCL->rem Now with FIRST_ROWS_100
SYSTEM-ORCL->pause

SYSTEM-ORCL->alter session set optimizer_mode = first_rows_100;

Session altered.

Elapsed: 00:00:00.00
SYSTEM-ORCL->select * from customers c, sales s
2 where c.cust_id = s.cust_id
3 and cust_last_name = 'Smith'
4 and s.time_id <= '31-DEC-98';

359 rows selected.

Elapsed: 00:00:01.09

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=61 Card=100 Bytes=15900)
1 0 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'SALES' (Cost=61)
2 1 NESTED LOOPS (Cost=61 Card=100 Bytes=15900)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS' (Cost=5 Card=1
Bytes=130)
4 3 INDEX (RANGE SCAN) OF 'CUST_CUST_LAST_NAME_IDX' (NON-UNIQUE)
(Cost=3 Card=1)
5 2 PARTITION RANGE (ITERATOR)
6 5 BITMAP CONVERSION (TO ROWIDS)
7 6 BITMAP AND
8 7 BITMAP MERGE
9 8 BITMAP INDEX (RANGE SCAN) OF 'SALES_TIME_BIX'
10 7 BITMAP INDEX (SINGLE VALUE) OF 'SALES_CUST_BIX'




Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
3191 consistent gets
497 physical reads
0 redo size
20130 bytes sent via SQL*Net to client
756 bytes received via SQL*Net from client
25 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
359 rows processed 877531 consistent gets[/B][/B] [B]