Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: A couple of questions

Re: A couple of questions

From: MK <MK_at_foo.com>
Date: Wed, 3 Sep 2003 22:43:42 +0200
Message-ID: <bj5jq2$f5rqj$1@ID-174077.news.uni-berlin.de>


"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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US