Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Why the index is not used?

Re: Why the index is not used?

From: Trifon Anguelov <trifona_at_pacbell.net>
Date: Fri, 12 Apr 2002 01:48:50 GMT
Message-ID: <62rt8.852$MY.562114711@newssvr21.news.prodigy.com>


Saikat,

The same problem and the solution were posted on the latest Oracle DBA forums at:
http://www.dbaclick.com/cgi-bin/ib3/ikonboard.cgi?s=3cb5d47f5421ffff;act=ST; f=19;t=118

Check the forums updated every day at:
http://www.dbaclick.com/cgi-bin/ib3/ikonboard.cgi

Hope that helps,

Trifon Anguelov
Senior Oracle DBA

http://www.dbaclick.com



"Saikat Chakraborty" <saikatchak_at_hotmail.com> wrote in message news:1739bae064ca0317eadece472c8d4814.16981_at_mygate.mailgate.org...
> 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 - 20:48:50 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US