Solved

How can i gather Histogram stats on a column?

Posted on 2011-09-07
4
334 Views
Last Modified: 2012-05-12
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
Comment
Question by:ajaybelde
  • 2
4 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
 

Author Comment

by:ajaybelde
Comment Utility
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
 
LVL 15

Accepted Solution

by:
Devinder Singh Virdi earned 500 total points
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now