Solved

How can i gather Histogram stats on a column?

Posted on 2011-09-07
4
337 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)
ID: 36495819
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
ID: 36495860
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)
ID: 36495896
>>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
ID: 36503212
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
levels for reporting 5 76
Clone Oracle 12c Database 5 52
Require data to appear on a single line 2 44
Deleting Rows from an Oracle Database - Performance 19 47
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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 shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

777 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