Re: Why the index is not used?

Hi

Try setting the following two init parameters. 1. OPTIMIZER_INDEX_CACHING
This initialization parameter represents a percentage value, ranging between the values of 0 and 99. The default value of 0 indicates to the CBO that 0% of database blocks accessed using indexed access can be expected to be found in the Buffer Cache of the Oracle SGA. This implies that all index accesses will require a physical read from the I/O subsystem for every logical read from the Buffer Cache, also known as a 0% hit ratio on the Buffer Cache. This parameter applies only to the CBO's calculations of accesses for blocks in an index, not for the blocks in the table related to the index. 2. OPTIMIZER_INDEX_COST_ADJ
This initialization parameter is also a percentage value, ranging between 1 and 10000, representing a comparison between the relative cost of physical I/O requests for indexed access and full table-scans. The default value of 100 indicates to the cost-based optimizer that indexed access is 100% as costly (i.e., equally costly) as FULL table scan access.

Hope this helps.

Rajesh

"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 - 12:01:32 CDT