Re: Index clustering factor

I think you are overly focused on clustering factor. Assuming makes trouble. Let's start with what we do know, and not guess about what we do not.

If explain plan does *not* match execution plan (red flag) - bind peeking may be the factor. Let's try a simple test.

SQL> alter session set "_optim_peek_user_binds" = false;
SQL> <set your binds and execute the statement>
SQL> select * from table(dbms_xplan.display_cursor);

Does this yield a plan with index access or FTS? If it uses the index, then there may be a histogram on one or both columns so lets find which ones:
SQL> select column_name,histogram from user_tab_col_statistics where table_name ='<table>'

Another good test is to substitute the literal values in place of the binds and see if that yields the desired plan.

When you post your reply with the information from above, we'll proceed with more of what we know.

On 1/9/08, Orysia Husak <Orysia.Husak_at_apollogrp.edu> wrote:
> When I generate an explain plan manually, the appropriate index is used.
> When I view the sql running through OEM, I see the execution plan is a
> full tablescan. Since the clustering factor for this index is very high,
> my assumption is that the index isn't being selected due to the high
> clustering factor.

-- 
Regards,

Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l