Re: A couple of questions

Comments interleaved:
"MK" <MK_at_foo.com> wrote:

>"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote
>
>
>> It'd be interesting to see the table definition and the delete statement.
>
>CREATE TABLE MYTAB (
> MYTAB_SOURCE VARCHAR2(40) , -- PK1
> MYTAB_DATETIME DATE, -- PK1
> MYTAB_VALUE NUMBER
>)
>
>(MYTAB_SOURCE, MYTAB_DATETIME) is the primary key.
>
>
>DELETE statement:
>
> DELETE
> FROM MYTAB
> WHERE
> MYTAB_SOURCE NOT IN (
> SELECT MYTAB_SOURCE FROM ASMALLTABLE) AND
> MYTAB_DATETIME < SYSDATE - 90
>
>

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. NOT IN is very inefficient unless smalltable is small indeed 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?

<SNIP>

>
>

>> I'm also assuming stats are up to date and you are using
>> the CBO?
>
>
>Stats are unfortunately not up to date (I never did them),
>and I have no frikking idea about CBO (meaning I never
>investigated whether CBO is active or not for this
>particular query).
>

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. Received on Wed Sep 03 2003 - 15:27:08 CDT