• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

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?
0
ajaybelde
Asked:
ajaybelde
  • 2
1 Solution
 
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
 
Devinder Singh VirdiCommented:
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now