RE: Interview question
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <rjamya@xxxxxxxxx>, "'raju raju'" <rajugaru.vij@xxxxxxxxx>
- Date: Fri, 26 Apr 2013 14:47:35 -0400
" If you want to remove, then you have to delete"
is an unfortunately common misconception. Now in the context you wrote it,
which was the difference between flagging it and actually removing it, I
understand you are highlighting that difference.
However, please do keep in mind the entire school of thought about
copy-keep, swap, and eventually drop, probably best described as one key
concept of Tim Gorman's "Scaling to Infinity" series.
When you walk through the differential costs of delete versus copy-keep and
swap, most people are surprised where the actual breakeven points are even
with old-fashioned table shuffles and synonym repointing. With time based
partitioning it gets truly surprising.
Now, your mileage WILL vary depending on the actuals, but if I asked an
interviewee about deleting and someone responded about the need to consider
whether actual deletion or a copy keep operation would be better, possibly
with a soft delete flag until enough had accumulated to be a win on
copy-keep, I'd be pretty impressed. At least until I found out whether they
knew how to do it, not just the words to describe it.
In the case of the 1 million row versus 400 million row tables, it depends a
lot on whether those tables are partitioned in a useful way that the deletes
are clustered with respect to the total amount of data you would have to
copy in order to leave the unwanted rows behind. Understanding the long term
rate of change is essential to knowing whether or not you are dealing with a
potential death spiral.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of rjamya
Sent: Friday, April 26, 2013 1:58 PM
To: raju raju
Cc: ORACLE-L
Subject: Re: Interview question
If you want to remove, then you have to delete, I wonder how removing rows
will be achieved by flagging them unless you want soft delete (which is not
delete)? I'd ask the interviewer " what do you want to do? delete them or
keep them in the table and soft delete them?" The best performance answer
depends on what you need.
I want to compare table A and Table B and find all the rows available in b
> which are already available in A. I want to remove all those which are
> available in B from A.
> What would be best option? Deleting them or adding a column and
> flagging them? What's the better option performance wise?
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts: