"MK" <MK_at_foo.com> wrote:
>"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.
>
No a full table scan does not always use 100% CPU usage. ( if it did, Oracle would barely qualify as multi-user) ---
Which process is using the CPU?
Received on Wed Sep 03 2003 - 16:06:57 CDT
