Re: Mysterious Outer Join

Hi,

From TKPROF I found that these two functions called in the SQL are causing the bottleneck.

The functions look like:

begin
select count(*) into active_cost_p from bc_item_source_map ism, bc_items i, bc_quotes q, bc_quote_lines ql where i.item_id = v_item_id
and ql.quote_line_id = i.ACTIVE_PROTOTYPE_COST and q.quote_id = ql.quote_id
and q.ITEM_SOURCE_MAP_ID = v_item_source_map_id;

if active_cost_p > 0 then
return '1';
else
return '0';
end if;
end;

Can we rephrase this? As we only are selecting 'if there is any value or not'?

Thanks,

NM

"Phil Haigh" <phil.haigh_at_fwpharma.com> wrote in message news:<AQUO6.3880$8Z5.186087_at_monolith.news.easynet.net>...
> Nilendu Misra wrote in message
> <23af4225.0105221311.4af70f8d_at_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 :
> ...
>
> I'd love to help but I could do with a few CREATE TABLE scripts first so
> that I can actually get the QEP out...
Received on Thu May 24 2001 - 17:47:01 CDT