Dear all,
Oracle version 8.1.7.0
OS Windows NT 4.0
I have a table of structure
CREATE TABLE AC_ROAMING_POSTED_0423 (
SERIALNO NUMBER (20),
RECTYPE VARCHAR2 (2) NOT NULL,
PLMNID VARCHAR2 (5) NOT NULL,
IMSI VARCHAR2 (15) NOT NULL,
RCI_ACCOUNTNO VARCHAR2 (10),
SVCNO VARCHAR2 (20),
CHRGDTTIME DATE NOT NULL,
- Nore fields
)
It have an index as
CREATE INDEX ACRP_IDX_0423 ON
AC_ROAMING_POSTED_0423(RCI_ACCOUNTNO, SVCNO)
I am fireng a query
select count(*)
from AC_ROAMING_POSTED_0423
where rci_accountno = 21970
No of records are around 500,000. Table analyzed
Explain plan shows an index range scan. But when I do an sqltrace
I find it is going for a full tabel scan.
Part of the trace file given below
PARSING IN CURSOR #1 len=73 dep=0 uid=19 oct=3 lid=19 tim=329358259
hv=539754431 ad='3ea6908'
select count(*)
from AC_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=329358259
BINDS #1:
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=329358259
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=675562835 p2=1 p3=0
WAIT #1: nam='db file scattered read' ela= 3 p1=7 p2=256075 p3=9
WAIT #1: nam='db file scattered read' ela= 24 p1=7 p2=346112 p3=128
-----Sniped
WAIT #1: nam='db file scattered read' ela= 23 p1=7 p2=295146 p3=128
WAIT #1: nam='db file scattered read' ela= 20 p1=7 p2=295274 p3=111
WAIT #1: nam='db file sequential read' ela= 0 p1=7 p2=295386 p3=1
WAIT #1: nam='db file scattered read' ela= 2 p1=7 p2=295388 p3=14
WAIT #1: nam='db file scattered read' ela= 24 p1=7 p2=295402 p3=128
-----More sniped
WAIT #1: nam='db file scattered read' ela= 3 p1=7 p2=409767 p3=20
FETCH
#1:c=288,e=5346,p=28051,cr=28065,cu=26,mis=0,r=1,dep=0,og=4,tim=329363605
WAIT #1: nam='SQL*Net message from client' ela= 1 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=329363606
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=4889 op='TABLE ACCESS FULL
AC_ROAMING_POSTED_0423 '
It is going for a full table scan. Even a hint does not work.
All blockes in the trace points to the tabel not the index.
What is happening?
Thanks and regards,
Saikat Chakraborty
--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
Received on Thu Apr 11 2002 - 09:29:49 CDT
