On Feb 10, 9:44 pm, "osuri" <Pmanda..._at_gmail.com> wrote:
> 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 non-
> unique index that I created. Can anyone please explain why that index
> is ignored?
>
> Regards,
>
> Osuri
When did you collect stats on the new index?
Received on Sun Feb 11 2007 - 06:36:32 CST
