query using index get no row?

[per...@net.hr]

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

[Mladen Gogala]

On Fri, 18 Apr 2008 01:24:09 -0700, perivoj 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'.

> Goran

This looks like a really nasty bug. Hints should have absolutely no
effect on the result set, only on the execution path. Can you tell us
what version is that and on what is the platform? My advice would be to
check the alert log for lovely strings like /^ORA-0600: .*/. Open a TAR
immediately and alert Oracle Support. This looks like a row not being in
the primary key index. Did you enforce the primary key with some strange
index?

--
Mladen Gogala
http://mgogala.freehostia.com

[DA Morgan]

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
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Mark D Powell]

On Apr 18, 12:35 pm, DA Morgan <damor...@psoug.org> wrote:

> damor...@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 --