PROBLEM: Cannot rebuild index!!
- From: "BD" <bobby_dread@xxxxxxxxxxx>
- Date: 30 Dec 2005 11:21:48 -0800
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)
/
.
- Follow-Ups:
- Re: PROBLEM: Cannot rebuild index!!
- From: Randy Harris
- Re: PROBLEM: Cannot rebuild index!!
- From: Matthias Hoys
- Re: PROBLEM: Cannot rebuild index!!
- Prev by Date: Re: Monitoring and tuning database performance
- Next by Date: Re: PROBLEM: Cannot rebuild index!!
- Previous by thread: Monitoring and tuning database performance
- Next by thread: Re: PROBLEM: Cannot rebuild index!!
- Index(es):
Relevant Pages
|
