Re: Why the index is not used?

From: Saikat Chakraborty <saikatchak_at_hotmail.com>
Date: Thu, 11 Apr 2002 14:34:45 +0000 (UTC)
Message-ID: <c221648fb5450d950a33c7ef3f22c46a.16981@mygate.mailgate.org>

In as an workaround, I created a view as

create or replace force view VAC_ROAMING_POSTED_0423 as select /*+ index(a,ACRP_IDX_0423) */ * from AC_ROAMING_POSTED_0423 where SVCNO > ' '

Now it is faster but it is going for an index full scan although the plan still showing range scan. Even RBO does not work. The trace is

---

PARSING IN CURSOR #1 len=74 dep=0 uid=19 oct=3 lid=19 tim=329363606 hv=2603522563 ad='34f20cc'
select count(*)
from VAC_ROAMING_POSTED_0423
where rci_accountno = 21970
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=329363606 BINDS #1:
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=329363606 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=675562835 p2=1 p3=0 FETCH #1:c=83,e=83,p=0,cr=2279,cu=0,mis=0,r=1,dep=0,og=4,tim=329363689 WAIT #1: nam='SQL*Net message from client' ela= 0 p1=675562835 p2=1 p3=0 FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=329363689

WAIT #1: nam='SQL*Net message to client' ela= 0 p1=675562835 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=675562835 p2=1 p3=0
STAT #1 id=1 cnt=1 pid=0 pos=0 obj=0 op='SORT AGGREGATE '
STAT #1 id=2 cnt=141 pid=1 pos=1 obj=4899 op='INDEX FULL SCAN '
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=675562835 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=675562835 p2=1 p3=0


=====================

There are other similar tables but they behave _normally_, i.e. index range
scans are used.
Thanks,
Saikat Chakraborty

-- 
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

Received on Thu Apr 11 2002 - 09:34:45 CDT