Re: Index clustering factor
- From: "Greg Rahn" <greg@xxxxxxxxxxxxxxxxxx>
- To: Orysia.Husak@xxxxxxxxxxxxx
- Date: Wed, 9 Jan 2008 16:08:31 -0800
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@xxxxxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
