Mysterious Outer Join

From: Nilendu Misra <nilendu_misra_at_yahoo.com>
Date: 22 May 2001 14:11:31 -0700
Message-ID: <23af4225.0105221311.4af70f8d@posting.google.com>

Hi,

It has happened before the same query behaved differently on DEV and PROD boxes. But not with such less magnitude of difference even while it has same execution plan on both. Using 8.1.7.1 on Sun boxes.

The query :

---

select e.entity_name as seller_name,

ism.item_sell as seller_item, 
ism.seller_part_number as seller_part_number, 
ism.seller_part_name as seller_part_name, 

e.entity_id as seller_id,
decode(e.entity_id, app.bc_unspecified_supplier, 1, 0) as unspecified_supplier_p,
e2.entity_name as maker_name,

ism.item_make as maker_item, 
ism.maker_part_number as maker_part_number, 
ism.maker_part_name as maker_part_name, 

e2.entity_id as maker_id,
decode(e2.entity_id, app.bc_unspecified_supplier, 1, 0) as unspecified_other_supplier_p,
ism.item_source_map_id as item_source_map_id, ism.estimate_p as estimate_p,
ql.quote_line_id as quote_line_id,
app.IS_ACTIVE_PRODUCTION_COST_P(ism.item_buy, ism.item_source_map_id) as active_production_cost_p,
app.IS_ACTIVE_PROTOTYPE_COST_P(ism.item_buy, ism.item_source_map_id) as active_prototype_cost_p,

ql.MINIMUM_QUANTITY as min_qty, 
ql.MAXIMUM_QUANTITY as max_qty, 
ql.quote_line_price_amount as price_amount, 
ql.QUOTE_LINE_PRICE_CURRENCY as price_currency, 

ism.buy_sell_unit_conversion as unit_conversion, DECODE(ibuy.owner_part_number, NULL, '-- unassigned --', DECODE(ibuy.revision, NULL, ibuy.owner_part_number,

ibuy.owner_part_number || '-' || ibuy.revision)) as item_number,
ibuy.item_name as item_name, 
ibuy.item_id as item_id, 
ibuy.cost_method_cc as cost_calc_method, 
ubuy.unit_name as buy_unit_name, 

usell.unit_name as sell_unit_name,
decode(ql.LEAD_TIME_SPAN || ql.LEAD_TIME_UNIT,'','', ql.LEAD_TIME_SPAN || ' ' || ql.LEAD_TIME_UNIT) as lead_time, ql.notes as notes
from

app.bc_item_source_map ism, 
app.bc_entities e, 
app.bc_entities e2, 
app.bc_units_of_measure ubuy, 
app.bc_units_of_measure usell, 
app.bc_quotes q, 
app.bc_items ibuy, 
app.bc_items isell, 
app.bc_quote_lines ql 

where
ism.seller = e.entity_id (+)
and ism.maker = e2.entity_id (+)
and q.item_source_map_id(+) = ism.item_source_map_id and ql.quote_id(+) = q.quote_id
and q.owner(+) = ism.owner
and ql.owner(+) = q.owner
and ibuy.item_id = ism.item_buy
and ubuy.unit_id(+) = ibuy.unit_id
and isell.item_id(+) = ism.item_sell
and usell.unit_id(+) = isell.unit_id

and ism.estimate_p =0 
and ism.seller = '12232' 
and ism.buyer = '12323' 
and ism.owner = '12323' 

order by item_source_map_id asc, min_qty asc NULLS FIRST;

Execution Time: >20 minutes and still going. On development taking 0.07 second.

Same explain plan path (Index Range Scan of the tables followed by Outer Nested Loop Join and finally Sort)in production. The data is exactly *double* in production however. Any lead? At least any advice on what hint I can use or how could I rewrite this?

Thanks in appreciation,

Nilendu Received on Tue May 22 2001 - 16:11:31 CDT