When you've got plenty of rows on you're driving
table, then using an EXISTS clause will typically mean
an index lookup for everyone of those rows, where as
the sort-merge is overcoming that by "brute force" so
to speak...
Its also important to realise that the 'cost' figure
cannot be used ACROSS different queries. Its used to
rank different access paths within an optimisation of
a single query.
hth
connor
- Surjit Sharma <surjits_at_ozemail.com.au> wrote: > Hi
>
> I have running the following two queries. If you
> look at the first query the cost is 37181 and takes
> about 16 minutes to run on (3CPUs Aplha Tru 64
> Unix Oracle 8.0.5) whereas the second query (cost
> 561) which I thought would have run lot faster does
> not come back as quick (in fact I killed the query
> in the end).
>
> Both the tables are analyzed and have statistics. I
> am really puzzled about the cost opimtimizer here.
>
> Looking for some insight from all the gurus out
> there.
>
> 1) Takes 16 minutes ..
> update add_comp_2000 a
> set
>
household_kinship_key=decode(residents_category_key,1,1,2,4)
> where residents_category_key in (1,2)
> and address_link in (select address_link
> from my_temp20 b
> )
>
>
> UPDATE STATEMENT Optimizer=CHOOSE (Cost=37181
> Card=3395612 Bytes=118846420)
> UPDATE OF ADD_COMP_2000
> MERGE JOIN (Cost=37181 Card=3395612
> Bytes=118846420)
> SORT (JOIN) (Cost=11915 Card=3395612
> Bytes=74703464)
> TABLE ACCESS (FULL) OF ADD_COMP_2000
> (Cost=561 Card=3395612
> Bytes=74703464)
> SORT (JOIN) (Cost=25266 Card=5164102
> Bytes=67133326)
> VIEW (Cost=12934 Card=5164102
> Bytes=67133326)
> SORT (UNIQUE) (Cost=12934 Card=5164102
> Bytes=67133326)
> TABLE ACCESS (FULL) OF MY_TEMP20
> (Cost=602 Card=5164102
> Bytes=67133326)
>
> 2) Update add_comp_2000 a
> set
>
household_kinship_key=decode(residents_category_key,1,1,2,4)
> where residents_category_key in (1,2)
> and exists (select address_link from
> my_temp20 b where
> a.address_link = b.address_link
> )
>
> UPDATE STATEMENT Optimizer=CHOOSE (Cost=561
> Card=169781 Bytes=3735182)
> UPDATE OF ADD_COMP_2000
> FILTER
> TABLE ACCESS (FULL) OF ADD_COMP_2000 (Cost=561
> Card=169781
> Bytes=3735182)
> INDEX (RANGE SCAN) OF TEMP20_ADD_LINK
> (NON-UNIQUE) (Cost=1 Card=1
> Bytes=13)
>
> Takes forever ...
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Surjit Sharma
> INET: surjits_at_ozemail.com.au
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)
"Some days you're the pigeon, some days you're the statue"
Do You Yahoo!?
Get your free @yahoo.co.uk address at
http://mail.yahoo.co.uk
or your free @yahoo.ie address at
http://mail.yahoo.ie
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Thu Apr 05 2001 - 10:49:59 CDT
