If you run the 'select' in isolation does it also take
a long time? This tells us whether its the insert or
the query that's hurting. An explain-plan would also
help the list.
hth
connor
- "Burton, Laura L." <BurtonL_at_prismplus.com> wrote:
> 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
>
>
Connor McDonald
web:
http://www.oracledba.co.uk
web:
http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
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 - 19:48:58 CST
