RE: SQL help

Wow, wow, wow.  I am old enough nowadays to have the belief I have seen it all 
(at least, in  theory) but this suggestion/answer by Mr. Lewis was truly 
eye-popping.  The developer I have been working with had the same reaction (but 
he's much younger so that's to be expected :) ).  Taking Jonathan's suggestion 
practically verbatim and making a few other minor tweaks like breaking one 
delete statement into 2 separate ones, the run time for this little program 
went from over 12 hours (and not even finishing) to about 2.5 minutes.   The 
buffer gets that were over a billion for this particular delete dropped to 2.8 
million.

SSS3@idm3> @AdditionalInterestMigration.pls

PL/SQL procedure successfully completed.

Elapsed: 00:02:27.58
SSS3@idm3>

It's been said many times over the years but I will say it again:  This list is 
quite possibly the single best place on the internet to receive timely, 
accurate, and valuable information about this little beast we call Oracle.

I have posted the explain plan for the revised statement below.  Note the 
overall cost dropped from 1096 to 13 (!) with Jonathan's insight.  I just may 
have to take my "Oracle Core - Essential Internals for DBAs and Developers" 
book next time JL visits Colorado in order to have it signed.  I thought my 
groupie days were over, but perhaps not.    :)

Thanks again for the advice.
-joe

SQL stmt
DELETE
FROM BLDGASSIGNEDADDINT
WHERE ID_ADDITIONALINTEREST in
   (
   SELECT pk
   FROM ADDINTEREST
   WHERE ID_INSURED = :1
   UNION ALL
   SELECT pk
   FROM ADDINTEREST
   WHERE lnnvl(ID_INSURED = :2)
  AND ID_POLICY in
      (
      SELECT q.pk
      FROM quote q
      WHERE q.COMMONINSURED_ID = :3
     AND q.ENDINFO_ID is null
      )
  AND id_policy != :4
   )

Explain plan
DELETE STATEMENT Optimizer=ALL_ROWS (Cost=13 Cardinality=6 Bytes=144)
DELETE OF BLDGASSIGNEDADDINT
  NESTED LOOPS (Cost=13 Cardinality=6 Bytes=144)
   VIEW OF VW_NSO_1 (VIEW) (Cost=10 Cardinality=3 Bytes=39)
    SORT (UNIQUE) (Cost=10 Cardinality=3 Bytes=62)
     UNION-ALL
      TABLE ACCESS (BY INDEX ROWID) OF ADDINTEREST (TABLE) (Cost=2 
Cardinality=1 Bytes=8)
       INDEX (RANGE SCAN) OF ADDINTERESTINSURED (INDEX) (Cost=1 Cardinality=1)
      NESTED LOOPS
       NESTED LOOPS (Cost=8 Cardinality=2 Bytes=54)
        TABLE ACCESS (BY INDEX ROWID) OF QUOTE (TABLE) (Cost=5 Cardinality=2 
Bytes=28)
         INDEX (RANGE SCAN) OF QUOTE_COMN_INSRD_ID_IDX (INDEX) (Cost=3 
Cardinality=2)
        INDEX (RANGE SCAN) OF ADDINTERESTPOLICY (INDEX) (Cost=1 Cardinality=2)
       TABLE ACCESS (BY INDEX ROWID) OF ADDINTEREST (TABLE) (Cost=2 
Cardinality=1 Bytes=13)
   INDEX (RANGE SCAN) OF BLDGASSIGNEDADDINT_BYADDINT (INDEX) (Cost=1 
Cardinality=2 Bytes=22)

From: Jonathan Lewis [mailto:jonathan@xxxxxxxxxxxxxxxxxx]
Sent: Monday, May 12, 2014 2:00 PM
To: Sweetser, Joe; Jeffrey Beckstrom
Cc: ORACLE-L
Subject: RE: SQL help



Looks like an example of "subquery with OR" that forces a filter operation and 
stops the optimiser from doing an unnest. See: 
http://jonathanlewis.wordpress.com/2007/02/26/subquery-with-or


I think you'll probably have to rewrite the query to look something like:

delete from  BLDGASSIGNEDADDINT where ID_ADDITIONALINTEREST in
(select pk from ADDINTEREST where ID_INSURED = :3
union all
select pk from ADDINTEREST where lnnvl(ID_INSURED = :3)
and ID_POLICY in
    (select q.pk from quote q where q.COMMONINSURED_ID = :1 and q.ENDINFO_ID is 
null)
and id_policy != :2
);


I think you'll also need an index on BLDGASSIGNEDADDINT(ID_ADDITIONALINTEREST) 
since that's how you're trying to identify the rows for deletion.



Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx> 
[oracle-l-bounce@xxxxxxxxxxxxx] on behalf of Sweetser, Joe [JSweetser@xxxxxxxx]
Sent: 12 May 2014 20:39
To: Jeffrey Beckstrom
Cc: ORACLE-L
Subject: RE: SQL help
Forgot the index definitions....
There are indexes on:
addinterest.pk, addinterest.id_insured, addinterest.id_policy
and
bldgassignedaddint.id_additionalinterest

-joe
From: Jeffrey Beckstrom [mailto:JBECKSTROM@xxxxxxxxx]
Sent: Monday, May 12, 2014 1:07 PM
To: Sweetser, Joe
Subject: Re: SQL help

Where's the index on id_policy for addinterest?

>>> "Sweetser, Joe" <JSweetser@xxxxxxxx<mailto:JSweetser@xxxxxxxx>> 5/12/14 
>>> 3:01 PM >>>
All,

Without going into too much detail as to why (at this point), I have the 
following statement:

delete from  BLDGASSIGNEDADDINT where ID_ADDITIONALINTEREST in
(select pk from ADDINTEREST where (ID_POLICY in
(select q.pk from quote q where q.COMMONINSURED_ID = :1 and q.ENDINFO_ID is 
null) and id_policy != :2)
or ID_INSURED = :3);

That yields an explain plan of this:

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 am trying to figure out a way to avoid the full table scans.  Below are the 
table creation statements for bldgassignaddint and addinterest tables minus 
extraneous columns

CREATE TABLE "BLDGASSIGNEDADDINT"
( "ID_BUILDING" NUMBER(10),
"ID_ADDITIONALINTEREST" NUMBER(10),
CONSTRAINT "FK17A3EAC98DDD8F2C" FOREIGN KEY ("ID_ADDITIONALINTEREST") 
REFERENCES "ADDINTEREST" ("PK") VALIDATE ,
CONSTRAINT "FK17A3EAC9C50C5C52" FOREIGN KEY ("ID_BUILDING") REFERENCES 
"BUILDING" ("PK") VALIDATE ,
PRIMARY KEY ("ID_BUILDING", "ID_ADDITIONALINTEREST") VALIDATE;

CREATE TABLE "ADDINTEREST"
( "PK" NUMBER(10),
"ID_INSURED" NUMBER(10),
"ID_POLICY" NUMBER(10),
CONSTRAINT "FK4B56FACB277D71C9" FOREIGN KEY ("ID_INSURED") REFERENCES "INSURED" 
("PK") VALIDATE ,
CONSTRAINT "FK4B56FACBE57638AB" FOREIGN KEY ("ID_POLICY") REFERENCES "QUOTE" 
("PK") VALIDATE ,
PRIMARY KEY ("PK") VALIDATE );

Any suggestions?  I am having no luck at all trying to incorporate EXISTS 
instead of IN.

Thanks,
-joe
Confidentiality Note: This message contains information that may be 
confidential and/or privileged. If you are not the intended recipient, you 
should not use, copy, disclose, distribute or take any action based on this 
message. If you have received this message in error, please advise the sender 
immediately by reply email and delete this message. Although ICAT Managers, 
LLC, Underwriters at Lloyd's, Syndicate 4242, scans e-mail and attachments for 
viruses, it does not guarantee that either are virus-free and accepts no 
liability for any damage sustained as a result of viruses. Thank you.
--
http://www.freelists.org/webpage/oracle-l





.

Other related posts: