performance issue

From: <neetiv_at_my-dejanews.com>
Date: Thu, 22 Oct 1998 14:11:01 GMT
Message-ID: <70nedl$sj7$1@nnrp1.dejanews.com>

Hi there,

I have few question with reference to performance tuning.It would be great if
you can enlighten me on these.

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...??

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Oct 22 1998 - 09:11:01 CDT