Re: Select Statement Gone South??

[Ferenc Mantfeld]

Title: 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