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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
