Re: Why is the non-unique index not used here?

Apart from the advice about statistics:

Check the column type of ROW_ID; is it numeric, as implied by the predicate, or some other type.

Use explain plan, followed by a call to dbms_xplan to check the predicate that was actually used at run-time.

In passing, now that you have a non-unique index, the best way to find the duplicates is probably to

    alter table {table_name}
    add constraint {constraint_name} primary key({pk_columns})     exceptions into {exceptions_table}

You have to precreate the exceptions table using

    $ORACLE_HOME/rdbms/admin/utlexcpt.sql

The table will identify all duplicated keys- and you will have to decide which rows to keep and which to delete.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"osuri" <Pmandayam_at_gmail.com> wrote in message 
news:1171161846.081085.69010_at_v33g2000cwv.googlegroups.com...


>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

>