Printable View
I found the scripts to remove the duplicates from a table in this website.
select (duplicate field names) from table_name
group by (list out fields)
having count(*) >1;
delete from table a where rowid > (select min(rowid) from table b where a.column1 = b.column1 and a.column2 = b.column2);
Could anybody kind enough to explain why the first one would find all the duplicats and the second one would elimilate the duplicates?
Thanks!
because one is a select and one is a delete?
I used the delete script to delete duplicated 80,000 rows from about 80m rows table. It's been running 15 hours. Is there anything wrong with it? Should I kill the job? Is there anyway to improve the performance?
It's urgent for me. If anybody can help me, I really appreciate it.
Thanks!
hope the columns are indexed ?
No, the column is not indexed. The table is a backup table I created for the testing. Does index help a lot? Thanks a lot for the help? Do I have to index two columns? Anything else I can do to improve the speed? How much faster it would be if I index the columns?
Thanks again!
undo is generated for a delete statement, if u dont want that to be generated u can use this stmt
alter table tablename nologging;
and then put the indexes (they definitely speed up) and then issue the update statement.
Srini
UNDO is always generated even if the table is in NOLOGGING mode for delete statement.
See below:
TamilPHP Code:09:33:45 SQL> create table t1 as select * from dba_objects ;
Table created.
09:33:58 SQL> select count(*) from t1 ;
COUNT(*)
----------
23701
09:34:07 SQL> insert into t1 select * from t1;
23701 rows created.
09:34:17 SQL> commit;
Commit complete.
09:34:22 SQL> alter table t1 nologging;
Table altered.
1 select end_time, begin_time , undoblks
2 from v$undostat
3 where end_time > to_date('02-10-2005 09:00:00','MM-DD-YYYY HH24:MI:SS')
4* order by 2
SQL> /
END_TIME BEGIN_TIME UNDOBLKS
-------------------- -------------------- ----------
10-FEB-2005 09:17:43 10-FEB-2005 03:27:43 0
10-FEB-2005 09:27:43 10-FEB-2005 09:17:43 563
10-FEB-2005 09:36:30 10-FEB-2005 09:27:43 2025 <-----
09:36:45 SQL> delete t1 where mod(object_id,2) = 0;
23688 rows deleted.
09:37:01 SQL> commit;
Commit complete.
SQL> L
1 select end_time, begin_time , undoblks
2 from v$undostat
3 where end_time > to_date('02-10-2005 09:00:00','MM-DD-YYYY HH24:MI:SS')
4* order by 2
SQL> /
END_TIME BEGIN_TIME UNDOBLKS
-------------------- -------------------- ----------
10-FEB-2005 09:17:43 10-FEB-2005 03:27:43 0
10-FEB-2005 09:27:43 10-FEB-2005 09:17:43 563
10-FEB-2005 09:37:07 10-FEB-2005 09:27:43 2704 <----
hi tamil
can u tell then whats the nologging option for then...
thanks for letting me know..
Srini
It's for heap-organized table bulk inserts and CTAS (Create Table As Select).
"Nologging" is still a misnomer -- "Minimal Logging" is more accurate".
thanks slimdave
so u mean minmal logging is always generated for a table which was created using normal create table stmt if we give the nologging option.
Srini
TamilPHP Code:10:23:13 SQL> get stat_info
1 select a.name, b.value from v$statname a, v$mystat b
2 where a.statistic# = b.statistic# and
3* lower(a.name) like '%redo size%'
10:23:28 SQL> /
NAME VALUE
------------------------------
redo size 5541584
1 row selected.
10:23:53 SQL> drop table t1 ;
Table dropped.
Now I create a table in LOGGING MODE.
10:23:59 SQL> create table t1 as select * from dba_objects ;
Table created.
10:24:04 SQL> get stat_info
1 select a.name, b.value from v$statname a, v$mystat b
2 where a.statistic# = b.statistic# and
3* lower(a.name) like '%redo size%'
10:24:08 SQL> /
NAME VALUE
------------------------------
redo size 8280104
1 row selected.
Oracle generated (8280104 - 5541584)= 2,738,520 bytes of redo.
10:24:09 SQL> drop table t1 ;
Table dropped.
10:24:15 SQL> get stat_info
1 select a.name, b.value from v$statname a, v$mystat b
2 where a.statistic# = b.statistic# and
3* lower(a.name) like '%redo size%'
10:24:23 SQL> /
NAME VALUE
------------------------------
redo size 8300836
1 row selected.
10:24:24 SQL> create table t1 nologging as select * from dba_objects ;
Table created.
10:24:43 SQL> get stat_info
1 select a.name, b.value from v$statname a, v$mystat b
2 where a.statistic# = b.statistic# and
3* lower(a.name) like '%redo size%'
10:24:47 SQL> /
NAME VALUE
------------------------------
redo size 8341560
1 row selected.
In NOLOGGING the redo size is reduced to (8341560 - 8300836 ) = 40,724 bytes.
As Slimdave said, even in NOLOGGING mode, oracle generates minimum redo.
cheers guys for the info.
Srini
This is v.timely - I've been hacking round the doc trying to bring all the implications of NOLOGGING together. Perhaps someone can help me with this:
NOLOGGING will only avoid writing log entries for "bulk" operations: Direct Load to Table (SQL or SQL*Loader), CTAS, Move Table, Create Index, Rebuild Index, (plus some similar operations for partitions).
So if an object has the NOLOGGING attribute, but only "normal" DML has executed against it (i.e. none of the above), all the entries exist in the log files to permit recovery.
Can such an object be restored & recovered?
Can Oracle know if there are changes for which the log entries are missing (none in this case)?
What is the status of the object if restored & recovered? (e.g. is an index USABLE?)
Is that due to logging on the Data Dictionary?Quote:
Originally posted by tamilselvan
In NOLOGGING the redo size is reduced to (8341560 - 8300836 ) = 40,724 bytes.
As Slimdave said, even in NOLOGGING mode, oracle generates minimum redo.
Yes, Dapi.
Do you live in US or Europe?
Tamil
Got it right!Quote:
Originally posted by tamilselvan
Yes, Dapi.
* warm glow *
That's Geneva, Switzerland (CH). I did once visit Geneva, Illinois (IL) so I though I ought to be specific! :)Quote:
Do you live in US or Europe?
. . . . ahhh ummmmmm is NOLOGGING different in Europe ? . ? . ? . . . help mummmmmyyyyyyyy ! ! ! ! !
Only in the environmental, forestry management sense.Quote:
... is NOLOGGING different in Europe ...
All index operations are logged, except for a create index in nologging mode (minimal logging there, blah blah).
For bulk insert operations I believe that the minimal logging is associated mostly with extent management in the data dictionary. So the fact that additional extents were created, or the HWM was moved, gets logged, but the new contents do not.
Thanks guys.
Any comment on my questions about restore & recovery?
PING - :o
yes, i don't think that there's a problem with that unless the object was actually created with the nologging option. Not sure what happens then ... I would imagine that the creation in nologging mode of a table would make it non-recoverable because even if no rows were initially inserted in it Oracle has no way of telling that.Quote:
Originally posted by DaPi
[B]So if an object has the NOLOGGING attribute, but only "normal" DML has executed against it (i.e. none of the above), all the entries exist in the log files to permit recovery.
Can such an object be restored & recovered?
http://download-west.oracle.com/docs...gy.htm#1004784
Except where the object is created as nologging, i believe it knows all. If a bulk insert occured in nologging mode then I believe that the redo log marks the affected blocks with a code to indicate that their contents are indeterminate.Quote:
Can Oracle know if there are changes for which the log entries are missing (none in this case)?
I think it's marked as corrupt in some way. The recommendation is to take an incremental or full backup after nologging operations. I have a feeling that RMAN is preferred for this because it can backup just the affected blocks -- I'm no RMAN expert though.Quote:
What is the status of the object if restored & recovered? (e.g. is an index USABLE?)
In fact this is the only reference to NOLOGGING I found in the 8i RMAN manual - which left me stuggling to see the big picture.Quote:
Originally posted by slimdave
The recommendation is to take an incremental or full backup after nologging operations. I have a feeling that RMAN is preferred for this because it can backup just the affected blocks . . .
Slim, Tamal, thanks v.much for your help. :)
Always glad to lend assistance to you Old Europeans!Quote:
Originally posted by DaPi
Slim, Tamal, thanks v.much for your help. :)
====
Always glad to lend assistance to you Old Europeans!
======
....provided the Euro is not raising very fast against Dollar
Tamil
swiss don't use the Euro, still safe