SQL Select taking forever

Hello,
could anyone help me with the following problem, I'm joining on two tables, each about 800,000 rows and inserting the resul into a table. All three tables reside in oracle - though the third is under a different schema. The tables are set up with plenty of tablespace, about 200M each. The primary keys are indexed (which is what I'm joining on). Anyway, the query looks something like

Any help, could you e-mail rather than (or as well as) post to tslttery_at_maths.tcd.ie

Thanks.

INSERT INTO

        cl_Work_Order
                (clWorkOrderId,
                 clServiceNumFlyLeads,
                 clCompletionNote,
                 sms_WorkOrderId,
                 clAdditionalCrewId,
                 clNumberOfInstallments,
                 clWorkOrderTypeId,
                 sms_db_source,
                 clServiceNumOutlets,
                 clClearedBy,
                 clClearedDate,
                 clCompletionDate,
                 clCrewId,
                 clCSRnote,
                 clResult,
                 swCreatedBy,
                 swDateCreated)
        SELECT 
                /*+ USE_NL(d) */
                cl_Work_Order_seq.NEXTVAL,
                t.cust_number,
                t.w_o_work_code,
                t.work_order#,
                t.crew,
                t.instalment_flag,
                t.w_o_type,
                t.sms_db_source,
                d.inventory_number,
                d.empl_number,
                TO_DATE(TO_CHAR(d.clearing_date),'YYYYMMDD'),
                TO_DATE(TO_CHAR(d.date_comp_w_o), 'YYYYMMDD'),
                d.crew,
                d.note,
                d.w_o_status,
                NVL(d.salesman#,0),
                TO_DATE(TO_CHAR(d.date_issued_w_o), 'YYYYMMDD')
        FROM
                devdata.w_o_transaction t,
                devdata.w_o_detail d
        WHERE
                t.sms_db_source (+) = d.sms_db_source
        AND     
                t.work_order# (+) = d.work_order#
/              

--
Tom Slattery.
-------------------------------------------------------------------------------- e-mail: tslttery_at_tcd.ie | Tom.Slattery_at_scrg.cs.tcd.ie

   Web: http://alf2.tcd.ie/~tslttery/index.html
--------------------------------------------------------------------------------Disclaimer : My college is not responsible for comments that I make.
Received on Mon Jun 08 1998 - 11:13:48 CDT