SQL optimization

From: zeb <thierry.constant1.nospam_at_free.fr>
Date: Sat, 4 Oct 2003 10:18:35 +0200
Message-ID: <3f7e822e$0$13302$626a54ce@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 Sat Oct 04 2003 - 03:18:35 CDT