Re: query using index get no row?

On Apr 18, 12:35 pm, DA Morgan <damor..._at_psoug.org> wrote:
> peri..._at_net.hr wrote:
> > I had this two queries:
>
> > select * from table where id = number;
> > select  /*+ NO_INDEX (table PK_IDX)*/ * from table where id = number;
>
> > When query used index no row was returned. Index is on primary key
> > column and status in user_indexes was 'VALID'.
>
> > I rebuild index but problem is not fixed.
>
> > I fixed problem with:
> >  alter index PK_IDX unusable;
> > and then
> >  alter index PK_IDX rebuild;
>
> > What was problem?
>
> > Goran
>
> Please post full and complete version number, operating system and your
> real DML. Not the phony statement you posted in your query.
>
> SQL> CREATE TABLE TABLE (
>    2  testcol DATE);
> CREATE TABLE TABLE (
>               *
> ERROR at line 1:
> ORA-00903: invalid table name
>
> SQL>
>
> It should be further noted that ID is a reserved word and should never
> be used as a column name.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Goran, you can find a list of the Oracle reserved words in the view V $RESERVED_WORDS. You should avoid using any of the reserved words as object names or table or view column names.

Was the PK built using a descending index. There was a bug where descending indexes produced incorrect query results that affects 9.2.0.8 through 10.2.0.3 Bug#4916783.8 though I think you have to have an in list in the query.

There was also a bug 9.2 - 10gR1 where Function Based Indexes could result in wrong results but neither of these look promising off your post.

HTH -- Mark D Powell -- Received on Fri Apr 18 2008 - 15:02:22 CDT