Re: PROBLEM: Cannot rebuild index!!

"BD" <bobby_dread_at_hotmail.com> wrote in message news:1135970508.391367.248550_at_g44g2000cwa.googlegroups.com...
> Hi all.
>
> I have a problem which I'm not sure how to deal with.
>
> Here's the scenario (8i on AIX):
>
> We have a system wherein some data warehouse tables are truncated and
> repopulated each night with fresh data.
>
> This morning, a unique index on one of these tables was in an unuseable
> state. I attempted to rebuild it, and got the error ORA-01452: cannot
> CREATE UNIQUE INDEX; duplicate keys found.
>
> I've looked on Metalink, and The only sense I can make of this is that
> when the new data was inserted, a duplicate row was created.
>
> I believe that this data insert was done via SQLLoader. How the inserts
> succeeded and got past the unique index, I'm not sure.
>
> There's a script on metalink to 'delete' duplicate rows (included
> below), but I would like to identify the duplicates before deleting
> them. I have changed the script from 'delete from' to 'select * from',
> so I can view the data. But I've so far waited 10 minutes for results,
> and for a 17000 column table that strikes me as not very promising.
>
> So, in short, my question is: How should I deal with suspected
> duplicate rows when my indexes are not working?
>
> Hope someone can help!
>
> BD
>
> ***here's the Metalink script***
>
> REM This is an example SQL*Plus Script to delete duplicate rows from
> REM a table.
> REM
> set echo off
> set verify off heading off
> undefine t
> undefine c
> prompt
> prompt
> prompt Enter name of table with duplicate rows
> prompt
> accept t prompt 'Table: '
> prompt
> select 'Table '||upper('&&t') from dual;
> describe &&t
> prompt
> prompt Enter name(s) of column(s) which should be unique. If more than
> prompt one column is specified , you MUST separate with commas.
> prompt
> accept c prompt 'Column(s): '
> prompt
> delete from &&t
> where rowid not in (select min(rowid) from &&t group by &&c)
> /
>

You should first check if the business logics allow to have duplicate rows. If they don't, there's something wrong with your ETL procedures and you should fix this. If duplicate rows are allowed, you shouldn't be having a UNIQUE index on those columns. Oh, I haven't seen a 17000 column table before, but i'm not that experienced with large data warehouses. Have you tried parallel DML in order to speed up the query ?

Matthias Received on Fri Dec 30 2005 - 13:46:17 CST