Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: A couple of questions
"Turkbear" <john.greco_at_dot.state.mn.us> wrote
> If I understand correctly, you are using a table ( smalltable)
> to indicate those records that should be retained in addition
> to those that are less than 90 days old.
True. I have a small subset of MYTAB_SOURCEs
(defined in ASMALLTABLE) that I'm
keeping for longer than 90 days in MYTAB.
> NOT IN is very inefficient unless smalltable is small indeed
ASMALLTABLE is very very very small indeed - 800 records.
> and smalltable.mytab_source is also indexed, since it has to
> read all the records in smalltable for EACH record in MYTAB..
>
> Is there another way to specify the records you wish to delete
> instead of identifying those to keep?
I don't think so. I have two classes of data in MYTAB only. Date with horizon = 90 days, and data with horizons defined in ASMALLTABLE.
> Depending on your Oracle version, if you analyze your
> table the optimizer will select the best access path, but
> the NOT IN will likely still result in a full table scan on
> swmalltable.
Full table scan is not what is worrying me at the moment.
(Time is not a critical resource; CPU is.) AFAIC, I don't
care if this DELETE takes 10 hours, instead of 15 minutes
(present case). What is worrying
me, is that DELETE takes 100% of CPU.
Or, are you saying that full table scan always takes 100% of CPU?! If yes, I'd recommend Oracle to put "delay(1ms)" here or there in all of their code! But I don't think that's the case, in fact now I remember that most full table scans don't take 100% of CPU. Received on Wed Sep 03 2003 - 15:43:42 CDT