Why is the non-unique index not used here?

I created a large Oracle 10g data warehouse table with 37M + rows. I then tried to create its primary key index and several non-unique indexes on the table. Because I had inadvertently created some test rows in an earlier test run and forgotten to truncate the table before populating it with the 37M+ rows, the primary key index creation failed. It was on a column called ROW_ID.

Just to make sure that the large table performed well for some of my queries (before redoing the entire process again), I simply created a non-unique index on ROW_ID. My thoughts were that I could also use this index to quickly weed out the few duplicate records from the test run so I could create the PK again.

Here is where I got stumped, when I ran the following simple query: SELECT * FROM LARGE_TABLE WHERE ROW_ID = 1; Yes, it returned 2 rows like I expected but it took more than an hour to return this since it did a full table scan instead of using the nonunique  index that I created. Can anyone please explain why that index is ignored?

Regards,

Osuri Received on Sat Feb 10 2007 - 20:44:06 CST