How can i gather Histogram stats on a column?

ajaybelde
ajaybelde used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

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

Author

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
Most Valuable Expert 2012
Distinguished Expert 2018

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
Lead Oracle DBA Team
Commented:
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).

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial