Oracle-L: Re: Select Statement Gone South??

From: Ferenc Mantfeld <mantfield_at_connexus.net.au>
Date: Tue, 25 Feb 2003 19:28:58 -0800
Message-ID: <F001.005597E6.20030225192858@fatcity.com>

Select Statement Gone South??Laura

It would seem you have a Cartesian product, since you are not joining in the pph alias (part_price_histories ) into the main query, but you have included as a standalone correlated subquery, which then references two of the outer query's tables.

Join the pph directly to the other three tables and then put and AND clause to include the correlated subquery, that ought to do it.

HTH. Regards :

Ferenc Mantfeld
Dreaming costs you nothing. Not dreaming costs you everything.

Original Message ----- From: Burton, Laura L. To: Multiple recipients of list ORACLE-L Sent: Wednesday, February 26, 2003 12:23 PM Subject: Select Statement Gone South??

I am inserting records into a table based on a select statement and it is taking way too much time. I have created indexes of the foreign keys and tried to rearrange the where clause to omit records earlier, but to no avail. The statement looks like this:

Insert into table test

select part_num,

nomenclature,

to_char(requisition_date,'yddd')||lpad(preq.document_serial,4,0),

2, /* '53-Purchase' */

PSA.TRANS_date,

requisition_qty,

unit_price,

4 /* 'ALMD Disapproval' */

from part_master_catalogs pmc,

part_requisitions preq,

part_price_histories pph,

part_status_assocs psa

where preq.pmc_id_fk = pmc.pmc_id

and preq.preq_id = psa.preq_id_fk

and psa.req_status_cd_fk = 'D'

AND PSA.TRANS_DATE <= TO_DATE('&&NEWMEDATE','MM-DD-YYYY')

AND PSA.TRANS_DATE > TO_DATE('&&LASTMEDATE','MM-DD-YYYY') and pmc.pre_approved_purch_flag = 'N'

and pmc.company_reimburse_flag = 'Y'

and pph.pph_id = (select max(pph_id) from part_price_histories

                              where preq.pmc_id_fk = pmc_id_fk

                                and requisition_date >= effective_date)

Any ideas?? Any insight would be appreciated.

Thanks,

Laura

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Ferenc Mantfeld
INET: mantfield_at_connexus.net.au

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Feb 25 2003 - 21:28:58 CST