Cost is higher but query is much faster

From: BG <crockydile_at_hotmail.com>
Date: 26 Mar 2002 14:17:18 -0800
Message-ID: <c1e19cc6.0203261417.ec8d5de@posting.google.com>

Hello.

I am under the impression that a lower cost is better than a higher cost.

I have never tuned before but had to do to a query taking several minutes to complete (about 150,000 rows). When I did not give a hint the query was doing a full table scan and its cost was 2892. After I gave it a hint its cost went up to 44141 but it is now lightning fast.

I notice that with the hint that disk IO is significantly less and this probably accounts for the speed, but why did the cost go up so much??

Thanks
Brian

SELECT
--SELECT /*+ INDEX(messages dt_recvd, MESSAGES_IND4) */   msgseqnum,
  msgtype,
  cust_id,
  msgid,
  LOWER (sender),
  LOWER (receiver),
  NVL (SUBSTR (subject, 1, 25), '<>'),
  NVL (status, '<>'),
  TO_CHAR (dt_recvd, 'Mon DD, YYYY HH24:MI A.M.') FROM messages

   WHERE (dt_recvd between TO_DATE ('Mar 26, 2000', 'Mon dd, yyyy')

         AND TO_DATE ('Mar 26, 2002', 'Mon dd, yyyy') + 1)
         AND msgtype = UPPER ('new')
         AND cust_id <> '_none_'

*(Results without giving an index hint)* Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2892 Card=33934 Byte
          s=4818628)

   1    0   TABLE ACCESS (FULL) OF 'MESSAGES' (Cost=2892 Card=33934 By
          tes=4818628)

Statistics

---

          0  recursive calls
          4  db block gets
     186388  consistent gets
     183632  physical reads
          0  redo size
       2077  bytes sent via SQL*Net to client
        317  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

(Results with an index hint)
Execution Plan

---

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=44141 Card=33934 Byt
          es=4818628)

1    0   TABLE ACCESS (BY INDEX ROWID) OF 'MESSAGES' (Cost=44141 Ca
          rd=33934 Bytes=4818628)

2    1     INDEX (RANGE SCAN) OF 'MESSAGES_IND4' (NON-UNIQUE) (Cost
          =361 Card=33934)

Statistics

---

          0  recursive calls
          0  db block gets
      12049  consistent gets
      11710  physical reads
          0  redo size
       2100  bytes sent via SQL*Net to client
        317  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

Received on Tue Mar 26 2002 - 16:17:18 CST