Re: tunning....

You do not say what version you are using, but if it is 8.x, why not use a partitioned table (partition by month). You can have global (full table) indexes and partition indexes that should both simplify your admin and speed you up.

In article <70neik$sra$1_at_nnrp1.dejanews.com>,   vimal.upreti_at_targetbase.com wrote:
> Hi there,
>
> I have few question with reference to performance tuning.It would be great
> if
> you can enlighten me on these.
>
> 1. We have tables for each month and we are creating a view for the full
> year by
> using UNION ALL.While selecting the distinct values from the views its
> taking
> too much time.Its doing a range scan.is there any way that we can tune the
> performance while running the queries against views.
>
> SELECT DISTINCT SUM_MONTH
> FROM AS_ALL_MBR_STN_PAIR_MONTH
> WHERE MBR_NBR = :b1
>
> 2. Is there any way to handle an outer join between two large tables with
> quick
> access.
> 3. Does constants in outer joins make any sense.
>
> SELECT A.MBR_NBR, B.ISS_DATE, A.CR_ID, B.CR_SRC_CD, B.PARTNR_CD,
> B.PARTNR_ACT_ID, A.SYS_UPD_TS, B.CR_TYPE_CD, B.FLT_CR_CRIT_ID,
> B.ADJMNT_TYPE_CD, B.ENRL_TYPE_CD
> FROM CREDIT_UNUSED B,
> CREDIT_UNUSED_AUDIT A
> WHERE A.MBR_NBR = B.MBR_NBR (+) AND
> A.CR_ID = B.CR_ID (+) AND
> A.IUD_FLAG = 'I' AND
> A.SYS_UPD_TS >= :b1 AND
> A.SYS_UPD_TS < :b2 AND
> B.ISS_DATE (+) > '1-JAN-95' ---- CONSTANT in outer join.
> ORDER BY A.SYS_UPD_TS, A.MBR_NBR
>
> 4. Our queries are showing high I/O on system queries. Is it
> OK to
> run analyze on SYS if NO then why...??
>
> Waiting for your reply,An early responce will be appreciated.
>
> Thanks,
> Vimal
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Oct 23 1998 - 14:42:21 CDT