Re: CBC latch contention on index root block
From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Sun, 20 Jan 2013 23:16:46 +0400
Message-ID: <CAOVevU5ZNYUHdPPcPPtPAM_+5EmxGF6Mq+SDicaWMySeDNO01A_at_mail.gmail.com>
So, i think, if you change last predicate( or third part of union as Jonathan Lewis suggested) to:
Date: Sun, 20 Jan 2013 23:16:46 +0400
Message-ID: <CAOVevU5ZNYUHdPPcPPtPAM_+5EmxGF6Mq+SDicaWMySeDNO01A_at_mail.gmail.com>
So, i think, if you change last predicate( or third part of union as Jonathan Lewis suggested) to:
TAMRABS_PGM.CUST_NUM=(
SELECT GLB_NUM FROM TAMRCUST_GLB_MAP WHERE TAMRCUST_GLB_MAP.TBL_NM='TAMRABS_PGM' AND TAMRCUST_GLB_MAP.CUST_NUM= 112805 AND TAMRCUST_GLB_MAP.ATRB_TYP_CD = TAMRABS_PGM.ABS_PGM_ID)
you can reduce number of accessing to index of "TAMRCUST_GLB_MAP" because of scalar subquery caching.
On Sun, Jan 20, 2013 at 5:53 PM, oracledba <oracledba71_at_gmail.com> wrote:
> it returns 175 rows.The actual sql has bind variables.I just replaces them
> with the values for better understanding.
>
>
> On Sun, Jan 20, 2013 at 3:27 AM, Sayan Malakshinov <xt.and.r_at_gmail.com>
> wrote:
>>
>> Sorry, previous query was wrong, could you show result of:
>>
>> select count(distinct ABS_PGM_ID)
>> from TAMRABS_PGM
>> where CUST_NUM not in (-1,112805)
>
>
-- Best regards, Sayan Malakshinov Senior performance tuning engineer PSBank Tel: +7 903 207-1576 -- http://www.freelists.org/webpage/oracle-lReceived on Sun Jan 20 2013 - 20:16:46 CET