why does a higher cost run faster?

From: Haroon A. Qureshi <haroon_at_qureshi.name>
Date: Thu, 15 Dec 2005 14:48:43 -0600
Message-Id: <200512152048.PRI45183@vmms9.netsolmail.com>

i have the following query in oracle apps:

  1 select inventory_item_id code, description meaning, description
  2 from mtl_system_items_b
  3 where organization_id = fnd_profile.value('ORG_ID')   4 and 63 = 63
  5* order by 2

no rows selected

Elapsed: 00:03:04.89

Execution Plan

---

          0
SELECT STATEMENT Optimizer=CHOOSE (Cost=7794 Card=118186 Bytes=3663766)

          1                  0

  SORT (ORDER BY) (Cost=7794 Card=118186 Bytes=3663766)

          2                  1

    TABLE ACCESS (FULL) OF 'MTL_SYSTEM_ITEMS_B' (Cost=7073 Card=118186 Bytes=366
3766)

the cost is 7073. when i force it use a concatenated index on organization_id and description, the cost jumps to 110381 but runs instantaneously.

SQL> start q1

no rows selected

Elapsed: 00:00:00.04

Execution Plan

---

          0
SELECT STATEMENT Optimizer=CHOOSE (Cost=110381 Card=118186 Bytes=3663766)

          1                  0

  SORT (ORDER BY) (Cost=110381 Card=118186 Bytes=3663766)

          2                  1

    TABLE ACCESS (BY INDEX ROWID) OF 'MTL_SYSTEM_ITEMS_B'
(Cost=109660 Card=1181

86 Bytes=3663766)

          3                  2
      INDEX (RANGE SCAN) OF 'MTL_SYSTEM_ITEMS_B_N2'

(NON-UNIQUE) (Cost=865 Card=

118186)

any ideas on why that is? am i not gathering my stats correctly?

thanks,
haroon

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Dec 15 2005 - 14:49:01 CST