How can i gather Histogram stats on a column?

I have a "select' statment with a column in where clause and that column has indexed.
But in explain plan it is not using that index may be because of uneven distribution of values.
So i want to update histogram stats on that column only.

How can i update Histogram stats on that column?
ajaybeldeAsked:
Who is Participating?
 
Devinder Singh VirdiConnect With a Mentor Lead Oracle DBA TeamCommented:
Just curious about:-
>> But in explain plan it is not using that index may be because of uneven distribution of values.

1. It is possible that runtime explain plan may or may not equal to show plan.
2. Execution plan of production may or may not equal to Development env.
3. If table is partitioned and somehow stats are not updated on partitions, then you can see this problem, where query working fine with certain values. (I had that problem earlier and need Oracle help to understand the scenario).
0
 
slightwv (䄆 Netminder) Commented:
I've not read of anyway to gather stats for a single column only.

I suggest regenerating stats on the specific index with dbms_stats.gather_index_stats

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_stats.htm#i1036276
0
 
ajaybeldeAuthor Commented:
BEGIN
  DBMS_STATS.GATHER_table_STATS (OWNNAME => 'OE', TABNAME => 'INVENTORIES',
  METHOD_OPT => 'FOR COLUMNS SIZE 20 warehouse_id');
END;
/

Can i Use this for histograms stats on warehouse_id column?

I found that in fallowing link
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm
0
 
slightwv (䄆 Netminder) Commented:
>>Can i Use this for histograms stats on warehouse_id column?

Based on how I read it, it looks like you can actually generate stats on a specific column after all?

But will those stats be applied over to the index in question?  You will likely need to regenerate stats on the index anyway.

I would look at the last_analyzed columns for the column and index to see how stale they are.

for the column: user_tab_columns
for the index: user_indexes
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.