Re: PROBLEM: Cannot rebuild index!!


"BD" <bobby_dread@xxxxxxxxxxx> wrote in message
news:1135970508.391367.248550@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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


.

Relevant Pages

  • Re: PROBLEM: Cannot rebuild index!!
    ... > CREATE UNIQUE INDEX; duplicate keys found. ... > There's a script on metalink to 'delete' duplicate rows (included ... > REM a table. ...
    (comp.databases.oracle.server)
  • PROBLEM: Cannot rebuild index!!
    ... CREATE UNIQUE INDEX; duplicate keys found. ... There's a script on metalink to 'delete' duplicate rows (included ... REM a table. ...
    (comp.databases.oracle.server)
  • Re: FTP Transfer
    ... REM Change Directory where the files to be transferred are. ... echo ftpusername> FtpScr.txt ... echo prompt>> FtpScr.txt ... REM lcd to change to the directory where the ...
    (microsoft.public.windows.server.scripting)
  • CMD.exe batch for net use
    ... Apologies if I should be using msdos batch group. ... XP and had a simple batch that had 3 variables at the prompt to create ... rem %1 is IP address of target ... echo %User% ...
    (microsoft.public.windowsxp.general)