Optimizing SQL over linked DB

From: Shawn <sghanna_at_hotmail.com>
Date: 12 Aug 2003 08:31:38 -0700
Message-ID: <443af30b.0308120731.cbb87b7@posting.google.com>

I work for a huge company that thinks I'm too reckless to touch the production database. That being said, I am allowed to link to the production database and bring down the data that I need.

I need to somehow optimize this download. Any suggestions.

Version: 8.1.7.4
Tool: Rapid SQL or TOAD

EXPLAIN PLAN:
CREATE TABLE STATEMENT Optimizer=CHOOSE (Cost=1666367 Card=2263071 Bytes=205939461)
  LOAD AS SELECT
    NESTED LOOPS (Cost=1666367 Card=2263071 Bytes=205939461)

      VIEW (Cost=4 Card=1 Bytes=18)
        SORT (AGGREGATE)
          INDEX (FAST FULL SCAN) OF INDX_MTI_DX (UNIQUE) (Cost=4
Card=2299701 Bytes=20697309)
      REMOTE*

SQL:
CREATE TABLE mti_sigma_hist
AS SELECT

trn_date,
trn_number,
que_line_id,

details,
hist_date_time
FROM
   (SELECT /*+ INDEX */

   trn_date,
   trn_number,
   que_line_id,

   details,
   hist_date_time
   FROM

       mti.message_hist_date_at_rgwp,
       (SELECT lo, hi
       FROM 
           (SELECT MIN(trn_date) LO, MAX(trn_date) HI
           FROM mti_sigma_dx)
        ) DX

   WHERE trn_date >= LO
   AND trn_date <= HI
   )

WHERE
   (details LIKE '%NOT A CORE PAYMENT%'

       OR que_line_id IN
('REPAIR_LOG','FTRVFY_LOG','EXCEPTN_LOG','FTRENT_LOG',
'CALLBACK_LOG','CALLBACKQ','MNV_LOG','RISK_LOG','SWFOPRLOG','WIRERPR_LOG'
,

'WIREVFY_LOG','PHNADV_LOG','STOP_PAY_LOG','ATT_DLV_L','CORMATCH_LOG',
'ADMENT_LOG','ADMEXC_LOG','ADMVFY_LOG','STOP_ADM_LOG','REF_INDEX','PAYADV_LOG',
'FED_RTN_EXCQ','GMD_RTN_EXCQ','GMDLO_EXCQ','GMDHI_EXCQ')
   )

Thanks! Received on Tue Aug 12 2003 - 10:31:38 CDT