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

Here are some thoughts:
- Post the complete statement. The fact that you have "about 15 tables" and only 1 join definition is troubling. - Give an overview of the volume of data involved for the main tables. - Give some generic overview of your database setup (OPTIMIZER_MODE, ...)
- Get and post the execution plan (do a search here if you don't know how).

HTH Daniel

> 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(+);
> /
Received on Wed Oct 06 2004 - 14:35:07 CDT