Re: Optimizing SQL over linked DB

Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3F390A53.CE2DA6B_at_exxesolutions.com>...
> 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. ;-)

put all the stuff you need across the database link into an inline view. oracle will get it all in far less network roundtrips. that could be a bottleneck. I never tried it, but saw a post on it.

daniel is right. do an export of the tables, then import them. then build the table you need. that is best. if they wont let you do the export have someone they will let do the export. you shouldnt be doing development on a production system.

you have alot of stuff in that query that 'could' lead to a slowdown. I dont have enough details. that number of rows in each table, selectiveness of indexes, bytes of the table segments.

you might also want to just do

create table as select * from the table across the network. get it on your PC. then build the table you need off of it that way. I also dont see why need the outermost select. get rid of it. Received on Wed Aug 13 2003 - 11:10:41 CDT