Re: Why the index is not used?

in the where condition you have:

where rci_accountno = 21970

you are comparing rci_accountno which is defined as varchar2 with a number.

Oracle implicitly converts into:

where to_number(rci_accountno) = 21970

and hence it is not using the index.

to correct:

 where rci_accountno = to_char(21970 )

                             #but will not match with values like '0021970'

Or

define rci_accountno as a number type

Or

Use function based indexes (>=8i versions)

-MS

"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
Received on Thu Apr 11 2002 - 12:20:03 CDT