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 -- //www.freelists.org/webpage/oracle-l
