Re: Missing Index entries

Dave wrote:
>
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
> news:cbi7mp$r9t$1_at_sparta.btinternet.com...
> >
> > There is an option to crosscheck a table
> > with its indexes. From memory I think it
> > is:
> >
> > analyze table T validate structure cascade;
> >
> > As a quick and dirty for this row, you can
> > try the following to see if the rowid is
> > used in the index at all:
> >
> > select /*+ index_ffs(cdr_detail your_index_name) */ cdr_id
> > from cdr_detail
> > where ROWID = 'AAAkobAHLAAAXvsAAA'
> >
> > This will do an index fast full scan on the index
> > instead of the default 'table access by user rowid'
> > that you're doing at present.
> >
> >
> > --
> > Regards
> >
> > Jonathan Lewis
> >
> > http://www.jlcomp.demon.co.uk
> >
> > http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> > The Co-operative Oracle Users' FAQ
> >
> > http://www.jlcomp.demon.co.uk/seminar.html
> > Optimising Oracle Seminar - schedule updated May 1st
> >
> >
> > "Dave" <david.sharples3_at_ntlXworld.com> wrote in message
> > news:Ya0Dc.43$Pp.9_at_newsfe5-win...
> > > Any seen this before or have any ideas?
> > >
> > > SQL> select cdr_id from cdr_detail where account_id = '351000912380865'
> > and
> > > cost=0.12 and chargeable_units = 29;
> > >
> > > CDR_ID
> > > ----------
> > > 83649770
> > >
> > > SQL> select cdr_id from cdr_detail where cdr_id = 83649770;
> > >
> > > no rows selected
> > >
> > > SQL> select rowid from cdr_detail where account_id = '351000912380865'
> and
> > > cost=0.12 and chargeable_units = 29;
> > >
> > > ROWID
> > > ----------
> > > AAAkobAHLAAAXvsAAA
> > >
> > > SQL> select cdr_id from cdr_detail where ROWID = 'AAAkobAHLAAAXvsAAA'
> > >
> > > CDR_ID
> > > ----------
> > > 83649770
> > >
> > > So the primary key is not getting updated as well as the table in some
> > > cases. (cdr_id part of the primary key)
> > >
> > > If I put a full hint into the select statement I get the row back.
> > >
> > > I dont think the table or index is corrupt as I can still select from
> them
> > > both.
> > >
> > > Any ideas?
> > >
> > >
> >
> >
>
> Thanks very much, Oracle support told me to use the analyze table ....
> without telling me what it does (so was unsure of what it actually did)
>
> The table this over 100 Million rows and 11 indexes so I presume it will go
> and crosscheck all the indexes, do you know if there is way to just have it
> do the primary key as the other indexes seem to be ok?
>
> Also out of curiousity do you know why it can get into that state?
>
> Will try that other statement on Monday when I get back in the office
>
> Thanks
>
> Dave

Every once in a while Oracle gets a bug with indexes. When index descending scans came out in 7.3 it was possible to get incorrect data from the index (although the index itself was apparently ok). Skip forward to 8.1.6 and online index building could leave the index out of sync with the table...so these things do happen from time to time.

hth
connor

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------