Nethence Newdoc Olddoc Lab Your IP BBDock  


Warning: those guides are mostly obsolete, please have a look at the new documentation.

UnixWindowsOracleObsoleteHardwareDIYMechanicsScriptsConfigs

Cleaning up and making an Oracle table index
 
 
Introduction
We've got a dummy table w/o any sequences, triggers, constraints nor indexes like for example table 'check0' here,
sqlplus USERNAME/PASSWORD@dbname
create table check0 (
col1 varchar2(200),
col2 varchar2(200),
col3 varchar2(200)
);
--select table_name from user_tables;
insert into check0 values ('lala', 'lili', 'lolo');
insert into check0 values ('lala', 'lili', 'lolo');
insert into check0 values ('lala', 'lili', 'lolo');
insert into check0 values ('lala', 'lili', 'lolo1');
insert into check0 values ('lala2', 'lili', 'lolo2');
Note. there's 1 integral triplon (line 1,2,3)
Note. there's 1 quadruplon if you want a index on col1 and col2
Note. if you proceed on an existing table with concurrent data in it, backup it first,
--create table check0backup as select * from check0;
 
Check there's no already existing index as user,
select index_name from user_indexes;
 
We need to add a index based on the 'col1' and 'col2' fields (unique col1 and col2 associations). But at first we're going to make sure there's no integral duplicates (all columns).
 
Note. if you need to restart this tutorial from scratch,
--select table_name from user_tables;
--select object_name from user_procedures;
drop table check0;
drop procedure wrk_delduplicates;
 
 
Delete integral duplicates
Look for integral duplicates (all columns),
set heading off
set feedback on
select col1, col2, col3
from check0
group by col1, col2, col3
having count(*) > 1;
Note. here, duplicates, triplon and more are only selected once
Note. you could also check by comparing the output of,
--select * from check0;
--select distinct * from check0;
 
We could use the "distinct" feature to simply erase the integral duplicates,
--create table check0lala as select distinct * from check0;
but this would imply to delete the check0 table and rename check0lala to check0, eventually causing an incident in production environments (service interruption + loosing concurrent sessions).
 
Delete first occurence of duplicates,
create or replace procedure wrk_delduplicates is
cursor primo is
select col1, col2, col3
from check0
group by col1, col2, col3
having count(*) > 1;
begin
for curs1 in primo loop
delete from check0
where col1=curs1.col1
and col2=curs1.col2
and col3=curs1.col3
and rownum=1;
end loop;
commit;
end;
/
exec wrk_delduplicates
note. make sure there's a ';' at the end of the 'delete' statement
note. rownum=1 makes sure only the first occurence of the duplicates are deleted
note. if you get compilation errors,
--show errors
 
Check again there's no duplicates,
select col1, col2, col3
from check0
group by col1, col2, col3
having count(*) > 1;
note. since we have a triplon, you need to execute the procedure once more !
exec wrk_delduplicates
then check again.
 
Clean up your work,
drop procedure wrk_delduplicates;
--select object_name from user_procedures;
 
 
Delete and keep/modify specific duplicates
Look for duplicates on the 'col1' and 'col2' columns (columns we need to build a index on),
set heading off
set feedback on
select col1, col2
from check0
group by col1, col2
having count(*) > 1;
Note. we can also check while comparing the output of those two queries,
--select col1, col2 from check0;
--select distinct col1, col2 from check0;
 
We want to be able to choose which entries to keep, since the col3 colmun differs in the duplicates. Let's show the specific duplicates to choose which one to modify and to keep,
select col1,col2,col3
from check0
where col1 || col2 in
  (select col1 || col2
  from check0
  group by col1, col2
  having count(*) > 1);
but it's easyer to visualize those with Toad. Add rowid to the query (needed to edit records),
select rowid, col1,col2,col3
from check0
where col1 || col2 in
  (select col1 || col2
  from check0
  group by col1, col2
  having count(*) > 1);
and remove the record with Toad's data grid,
select the record and click on the (-) sign to delete it
you can also delete the record with an SQL statement if you like.
 
Back to the SQL prompt, check again there's no specific duplicates,
select col1, col2
  from check0
  group by col1, col2
having count(*) > 1;
 
 
Make the index
Create the index on the cleaned up check0 table,
create unique index check0_index1 on check0 (col1, col2);
check,
select index_name from user_indexes;
 
 
Troubleshooting
If you get this one,
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
make sure there's no duplicates (read the first two chapters of this guide).
 
 
References
(FR) http://www.commentcamarche.net/forum/affich-79827-architecture-base-de-donnees
 

(obsolete, see the new doc)