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
