Optimizer Question/Puzzle

From: Surjit Sharma <surjits_at_ozemail.com.au>
Date: Thu, 05 Apr 2001 06:18:16 -0700
Message-ID: <F001.002E25BD.20010405060031@fatcity.com>

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.

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). Received on Thu Apr 05 2001 - 08:18:16 CDT