Re: SQL help
From: Stéphane Faroult <sfaroult_at_roughsea.com>
Date: Mon, 12 May 2014 22:56:29 +0200
Message-ID: <5371357D.7010600_at_roughsea.com>
On 05/12/2014 09:01 PM, Sweetser, Joe wrote:
> All,
>
>
> DELETE STATEMENT Optimizer=ALL_ROWS (Cost=1096 Cardinality=2316 Bytes=150540)
> DELETE OF BLDGASSIGNEDADDINT
> FILTER
> HASH JOIN (Cost=1096 Cardinality=185659 Bytes=12067835)
> TABLE ACCESS (FULL) OF ADDINTEREST (TABLE) (Cost=435 Cardinality=88384 Bytes=3446976)
> TABLE ACCESS (FULL) OF BLDGASSIGNEDADDINT (TABLE) (Cost=110 Cardinality=185657 Bytes=4827082)
> TABLE ACCESS (BY INDEX ROWID) OF QUOTE (TABLE) (Cost=3 Cardinality=1 Bytes=39)
> INDEX (UNIQUE SCAN) OF SYS_C005688632 (INDEX (UNIQUE)) (Cost=2 Cardinality=1)
>
> That gets executed many times in a loop and is doing a boatload of logical IO's and no physical IO's. By boatload I mean over 100 million buffer gets each hour.
Date: Mon, 12 May 2014 22:56:29 +0200
Message-ID: <5371357D.7010600_at_roughsea.com>
On 05/12/2014 09:01 PM, Sweetser, Joe wrote:
> All,
>
>
> DELETE STATEMENT Optimizer=ALL_ROWS (Cost=1096 Cardinality=2316 Bytes=150540)
> DELETE OF BLDGASSIGNEDADDINT
> FILTER
> HASH JOIN (Cost=1096 Cardinality=185659 Bytes=12067835)
> TABLE ACCESS (FULL) OF ADDINTEREST (TABLE) (Cost=435 Cardinality=88384 Bytes=3446976)
> TABLE ACCESS (FULL) OF BLDGASSIGNEDADDINT (TABLE) (Cost=110 Cardinality=185657 Bytes=4827082)
> TABLE ACCESS (BY INDEX ROWID) OF QUOTE (TABLE) (Cost=3 Cardinality=1 Bytes=39)
> INDEX (UNIQUE SCAN) OF SYS_C005688632 (INDEX (UNIQUE)) (Cost=2 Cardinality=1)
>
> That gets executed many times in a loop and is doing a boatload of logical IO's and no physical IO's. By boatload I mean over 100 million buffer gets each hour.
I would start by getting rid of the loop. The full scan may be the better solution - if you run it once.
-- Stéphane Faroult RoughSea Ltd <http://www.roughsea.com> Konagora <http://www.konagora.com> RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd> Author, SQL Success <http://www.amazon.com/SQL-Success-Database-Programming-Proficiency/dp/1909765007/>, The Art of SQL <http://www.amazon.com/Art-SQL-Stephane-Faroult/dp/0596008945/>, Refactoring SQL Applications <http://www.amazon.com/Refactoring-SQL-Applications-Stephane-Faroult/dp/0596514972/> -- http://www.freelists.org/webpage/oracle-lReceived on Mon May 12 2014 - 22:56:29 CEST