Re: Fine tuning a view which would retrieve millions of records

ksathyavada_at_gmail.com (Kalyan) wrote in message news:<6186238e.0410060655.21598a97_at_posting.google.com>...
> Hello Sir/Madam,
>
> We have a peculiar requirement where we need to retrieve millions of
> records from almost 15 tables.
> There are 2 master tables which has many reference columns from almost
> 15 tables. I need to get the actual data from base tables(15 tables).
> This was taking time and so had to write functions to retrieve the
> data.
> But still the performance is not good. It was better than using left
> joins.
>
> The database is : Oracle 9i
> O/S : Windows NT
>
> Kindly help me with ways i can tune this query.
> The view script is as follows:::
>
>
> CREATE OR REPLACE VIEW DLC_MP_SUMMARY_VW AS
> SELECT /*+ INDEX( MAILPIECE DK_MAILPIECE_MAILRUNIDFK, MAILRUN
> PK_MAILRUN )*/
> MR.ROWID MRROWID,
> MR.ID ID,
> MR.MAILRUNID MAILRUNID,
> MR.CREATIONTIME CREATIONTIME,
> ROLL_UP_STATUS ,
> MR.COSTCENTER COSTCENTER ,
> MR.MAILDATE MAILDATE,
> MR.CLEAN_UP_STATUS CLEAN_UP_STATUS,
> DLC_GETSTATE_FNC(MR.STATEIDFK) STATEID,
> MR.SOURCEID SOURCEID,
> MR.JOURNALID JOURNALID,
> MP.ROWID MPROWID,
> MP.RECNUM MPRECNUM,
> DLC_GETSTATUS_FNC(MP.STATUSIDFK) STATUSID,
> MP.POSTAGE POSTAGE,
> MP.TIMESTAMP TIMESTAMP,
> DLC_GETACCOUNTID_FNC(MP.ACCOUNTIDFK) ACCOUNTID,
> DLC_GETSTEP_FNC(MP.STEPIDFK) STEP,
> MP.WEIGHTUNITS WEIGHT,
> DLC_GETRESULT_FNC(MP.RESULTIDFK) RESULTID,
> DLC_WEIGHTCAT_FNC(MP.WEIGHTCAT) WEIGHTCATEGORY,
> DLC_GETPERSONDET_FNC(MP.PERSONIDFK) PERSONID,
> DLC_GETPERSONFNAME_FNC(MP.PERSONIDFK) FIRSTNAME,
> DLC_GETPERSONLNAME_FNC(MP.PERSONIDFK) LASTNAME,
> DLC_GETDISPOSITION_FNC(MP.DISPOSITIONIDFK) DISPOSITIONID,
> DLC_GETSITE_FNC(MP.SITEIDFK) SITEID ,
> DLC_GETMACHINEID_FNC(MP.MACHINEIDFK) MACHINEID ,
> DLC_GETJOBTYPE_FNC(MR.JOBTYPEIDFK) JOBTYPEID ,
> DLC_CYCLENAME_FNC(MR.CYCLEIDFK) CYCLEID ,
> SYSDATE ROLLUP_DATE,
> SYSDATE DATETIME,
> DLC_GETCLIENTNAME_FNC(MR.JOBTYPEIDFK) NAME,
> DLC_GETCARIERNAME_FNC(MP.CLASSIDFK) CARRIER_NAME
> FROM
> MAILRUN MR, MAILPIECE MP
> WHERE
> AND MR.ID = MP.MAILRUNIDFK(+);
> /

Hi,

1. First, what do your DLC_ functions do? Could be a critical point if your functions contain business logic and execute in a millions-of-row retrieval...
2. For the execution plan testing in sqlplus

SET AUTOTRACE ON TRACEONLY /* gives you the execution plan without starting

                                           the data retrieval */

1. execute query and see the plan
2. gather ststistics (if not up-to-date)
3. execute query and see the plan
4. remove the hint
5. execute query and see the plan