Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Index ignored while using update statement

user6370697Nov 26 2012 — edited Nov 27 2012
Hello All,
I am having trouble figuring out why an update statment is ignoring the primary kiy index when performing an update through the application. The index IS used when the update is run from sql*plus or other sql tools.

The statement is very simple:
update ITEM_MASTER set COST = :1 where SMARTPART_NUM = :2;
ITEM_MASTER has unique, primary key index on SMARTPART_NUM

When I use OEM and other tools, I can see the index is not used in the exlain plan, and the query has a high CPU cost due to the full table scan.
The table is analyzed, lately using 100%. Table rowcount is 229768
SELECT column_name, num_distinct, num_buckets, histogram, trunc(last_analyzed) ANALYZED FROM USER_TAB_COL_STATISTICS
WHERE table_name = 'ITEM_MASTER' AND column_name = 'SMARTPART_NUM';

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM ANALYZED
------------------------------ ------------ ----------- --------------- ---------
SMARTPART_NUM 198417 254 HEIGHT BALANCED 25-NOV-12


The database is Oracle Database 10g Release 10.2.0.4.0 - 64bit Production (on Windows, standard edition)

Here are parameters related to optimizer:
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.4
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE

We've tried rebuilding the indexes then re-analyzing, dropping stats and locking at zero, using various sample sizes; none have any impact.

We wondered if the bind variables are causing this, though in testing outside the application, we tried pl/sql and sql batches to mimic the passing of values into the binds, and all still used the index.

Other than putting a hint into SQL in the application (we'd have to ask for a customization), how can I "fix" the database to use the index? In other words, how can I make the database see that the cost of a FTS is much higher than using the index, whenever it sees these updates (either from sql or the application)? This is the simplest example, but we have 4-5 SQL updates on this and other tables that are ignoring the indexes and using full table scans, so we'd like to fix it for all.

Any help would be appreciated!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 25 2012
Added on Nov 26 2012
10 comments
2,432 views