Re: SQL optimization

zeb

Looking at the execution plan you were getting, it's clear to see that this cannot be anything like the RBO version. Bitmap indexes won't be used by the RBO. The RBO will be unlikely to attempt to do what the CBO is doing with bitmap index merging - the CBO is having to convert the primary key index on the sales table to a bitmap first. The RBO will probably done a full table scan of the starting table - the sales_order_fact table due to the RBO working right to left thru the from clause if there are no indexes from which to "start" the query from.

Looking at your where clause and the existing plan there does not seem to be a selective indexed access to any of the tables (or there would be probably be indexes appearing for the e_mrp_result or sched_lines_fact tables other than the primary key for the sched_lines_fact table used in the fast full scan.

Things I'd try (in order and testing between each) in preference to hinting RULE (given it's obsolescense in 10g) would be

1. Simply reverse the order of the from clause tables and add the ORDERED hint.
2. If that doesn't help determine which end of the chain of e_mrp_result<-->sched_lines_fact<-->sales_orders_fact has the most selective where clause and appropriate indexes and look to put that table first, the sched_lines_fact second and the other table last.
3. Hint that the query should use a fast full scan of the sales_order_fact table INDEX_FFS(sales_order_fact SALES_ORDERS_FACT_PK). This should then skip doing the bitmap index conversion/merge stuff. I'm guessing that the e_load_mode ='B' is not very selective.
4. On a more radical note - if the sched_lines_fact table is just a join table and rows will always be present for given combos you could drop this table from the select altogether and use the erp_mrp_result.j_2cepos column as a replacement for sched_lines_fact.schedule_line_id in the select and in the group by clause. Join e_mrp_result to sales_orders_fact direct. This could save some IO if the result sets are identical.

Hope this helps

Andy

"zeb" <thierry.constant1.nospam_at_free.fr> wrote in message news:3f7e822e$0$13302$626a54ce_at_news.free.fr...
>
> Hi,
>
> The request is long with CBO and very quick with RBO ( the stats are
> uptodate )
> Do you know why I have a full table scan whith CBO ?
>
> Thanks in advance
> Oracle 8.1.7.4
>
> 1 SELECT sales_orders_fact.sales_order_number sales_order_number,
> 2 sales_orders_fact.sales_line_item_id sales_line_item_id,
> 3 sched_lines_fact.schedule_line_id schedule_line_id,
> 4 MAX (e_mrp_result.j_2cstat) e_mrpstatus,
> 5 SUM (e_mrp_result.menge) e_mrpalloc
> 6 FROM e_mrp_result, sched_lines_fact, sales_orders_fact
> 7 WHERE sales_orders_fact.sales_order_number =
> 8
> sched_lines_fact.sales_order_num
> er
> 9 AND sales_orders_fact.sales_line_item_id =
> 10
> sched_lines_fact.sales_line_item
> id
> 11 AND e_mrp_result.aufnr = sched_lines_fact.sales_order_number
> 12 AND e_mrp_result.hppos = sched_lines_fact.sales_line_item_id
> 13 AND e_mrp_result.j_2cepos = sched_lines_fact.schedule_line_id
> 14 AND e_mrp_result.werks = sales_orders_fact.plant_id
> 15 AND e_mrp_result.bdart = 'KD'
> 16 AND e_mrp_result.j_2cbskz = 'C'
> 17 AND sales_orders_fact.e_load_mode = 'B'
> 18 GROUP BY sales_orders_fact.sales_order_number,
> 19 sales_orders_fact.sales_line_item_id,
> 20* sched_lines_fact.schedule_line_id
>
>
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=710 Card=1 Bytes=78)
> 1 0 SORT (GROUP BY) (Cost=710 Card=1 Bytes=78)
> 2 1 HASH JOIN (Cost=708 Card=1 Bytes=78)
> 3 2 NESTED LOOPS (Cost=623 Card=1985 Bytes=83370)
> 4 3 INDEX (FAST FULL SCAN) OF 'SCHED_LINES_FACT_PK' (UNI
> QUE) (Cost=4 Card=2512564 Bytes=50251280)
>
> 5 3 TABLE ACCESS (BY INDEX ROWID) OF 'SALES_ORDERS_FACT'
> (Cost=623 Card=1282061 Bytes=28205342)
>
> 6 5 BITMAP CONVERSION (TO ROWIDS)
> 7 6 BITMAP AND
> 8 7 BITMAP CONVERSION (FROM ROWIDS)
> 9 8 INDEX (RANGE SCAN) OF 'SALES_ORDERS_FACT_PK'
> (UNIQUE) (Cost=10)
>
> 10 7 BITMAP INDEX (SINGLE VALUE) OF 'SALES_ORDERS_F
> ACT_IDX03'
>
> 11 2 TABLE ACCESS (FULL) OF 'E_MRP_RESULT' (Cost=53 Card=23
> 50 Bytes=84600)
>
>
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=HINT: RULE
> 1 0 SORT (GROUP BY)
> 2 1 NESTED LOOPS
> 3 2 NESTED LOOPS
> 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'E_MRP_RESULT'
> 5 4 INDEX (RANGE SCAN) OF 'E_MRP_RESULT_IDX01' (NON-UN
> IQUE)
>
> 6 3 INDEX (RANGE SCAN) OF 'SCHED_LINES_FACT_PK' (UNIQUE)
> 7 2 TABLE ACCESS (BY INDEX ROWID) OF 'SALES_ORDERS_FACT'
> 8 7 INDEX (UNIQUE SCAN) OF 'SALES_ORDERS_FACT_PK' (UNIQU
> E)
>
>
>
>
>
Received on Tue Oct 07 2003 - 05:28:17 CDT