Re: Optimizing SQL over linked DB

I wouldn't let you at the production database either. Companies like to protect their production data from anyone and everyone. Only those that truly need access to the data should be allowed to have it. And moving the data off the production server so that you can process it is not always a bad idea.

In your query the mti.message_hist_date_at_rgwp table is the only remote table. Why not just pull the contents of this table to your other database and then process it there?

Although, as Daniel said, it would probably be quicker to exp/imp the data.

HTH,
Brian

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!

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"