why does a higher cost run faster?
- From: "Haroon A. Qureshi" <haroon@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 15 Dec 2005 14:48:43 -0600
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
