Re: why does a higher cost run faster?

How many rows does the query actually return? The optimizer, based on the statistics available to it (and its built-in assumptions) estimates that 118186 rows will be returned and for that it figures a full table scan to be more efficient. You can see that in the costs it assigned to the full table access vs. the index access. If the index access is so much faster that means that one of the following is true:
a) the available statistics do not accurately reflect the reality of the data distribution (maybe organization_id is skewed, then possibly a histogram could help)
b) your are using Oracle 9i or later and have not collected system statistics. This could be viewed as a subclass of point a. c) the built-in assumptions are violated - e.g. predicate independence (this particular assumption wouldn't apply in your case, but there are others).
d) you hit an optimizer bug ( rather unlikely for your simply query, except possibly the use of the pl/sql function in the predicate - is the plan the same if you replace the function call with the value returned?).

Haroon A. Qureshi wrote:
> 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?
>

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l