Re: query using index get no row?
Date: Fri, 18 Apr 2008 13:02:22 -0700 (PDT)
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
> 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 126.96.36.199 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