Re: Optimizing SQL over linked DB

Shawn wrote:

> 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!

I'm with your management. I wouldn't let you on a production server with this either.

Get the rows with EXPort.

And then with the hours you have saved take a class on SQL tuning. ;-)

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)