Re: Missing Index entries

"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 Received on Fri Jun 25 2004 - 17:14:09 CDT