RE: SQL help
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "JSweetser@xxxxxxxx" <JSweetser@xxxxxxxx>, Jeffrey Beckstrom <JBECKSTROM@xxxxxxxxx>
- Date: Mon, 12 May 2014 19:59:53 +0000
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 [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.
--
//www.freelists.org/webpage/oracle-l
.
Other related posts:
