Validating null value in oracle sql startup speed dating sydney

20-Sep-2017 08:49

validating null value in oracle sql-7

tempat dating di shah alam

Hi Tom: I have several tables (about 1 million rows) that I want to delete some of the records (20,000 to 50,000 rows) for sure. December 05, 2002 - am UTC ARG -- why do people always say "there is no requirement for redo/rollback -- the data is not needed".I want a quick DELETE and there is no requirement to generate rollback data because the data are definitely not needed. I mean I could use regular delete statement but not sure if I could even TURN OFF the redo entries generated (before image) ? What about the data you are NOT deleting, is that data needed???? If it is needed -- then you do in fact need ROLLBACK and REDO.So there is an image of the rows in rollback which are currently not present in the table. Now all the rollback blocks are written to the redo log files too.So you have the data blocks with the table (without the deleted rows, of course) and the rollback blocks with the old image both producing redo, which accounts for additional archive logs.Above, i describe the table i want to clean from dba_segments SEGMENT_NAME EXTENTS BYTES ---------------------------------------- ---------- ---------- CTDNOV 26 2276614144 from dba_extents SEGMENT_NAME EXTENT_ID Kbytes ---------------------------------------- ---------- ---------- CTDNOV 0 520 1 520 2 800 3 1200 4 1800 5 2680 6 4000 7 6000 8 9000 9 13480 10 20200 11 30280 12 45440 13 68136 14 102240 15 153312 16 229968 17 20480 18 30720 19 46080 20 69120 21 103680 22 155520 23 233280 24 349920 25 524880 I'm interesting about your opinion, what do you think is the best way to clean(delete millons of rows) a big table? #1 is my preferred way unless you want to get more radical (see below).The problem with #3 is you are forgetting about indexes, you can do a direct path INSERT /* APPEND */ to move the data, but the indexes would still be maintained and they generate gobs of redo.I cant comment on the ratio of deleted records to ratio of redo produced, though.

case3: partition the data so that you can do a DROP partition instead of DELETE. in general, if you delete a million records, you are going to put them back sometime won't you.

This process took more than 4 hours to finish and generated a huge amounts of archives.

2nd Approach (i didn't test it, i think this is slower than the 1st approach.

redo/Rollback isn't just to restore the data you deleted in this case, it is there to put the database table BACK the way it was so it is not some scrambled bunch of bits and bytes in the middle of a change that is totally unusable after a crash. COM delete from t where owner in ( 'SYSTEM', 'WKSYS' ); 42560 rows deleted.

That table is a data structure -- if you crash or fail in the middle of modifying it -- it is left in an unusable, dangerous state. Elapsed: .49 The run time is almost tripled for the delete (still, pretty darn fast -- and this is on my desktop PC, a p4 with 512meg and a single ide disk -- perhaps this is as they say "much ado about nothing").

given 1, 2, and 3 to choose from -- I would use #1.