Re: A couple of questions

From: MK <MK_at_foo.com>
Date: Wed, 3 Sep 2003 22:11:29 +0200
Message-ID: <bj5htn$fk3jp$1@ID-174077.news.uni-berlin.de>

"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

> I'm assuming that on average you are deleting 1/90th of the table
> ( i.e its a 36 million row table)

True. Every day, I'm culling the oldest data.

> and that the column that drives the delete is
> indexed?

True. But there are two columns driving the DELETE.

> 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). Received on Wed Sep 03 2003 - 15:11:29 CDT