Home » RDBMS Server » Performance Tuning » Help - Histogram
Help - Histogram [message #257947] Thu, 09 August 2007 12:04 Go to next message
orasaket
Messages: 70
Registered: November 2006
Member
Hi,

i am using Otacle 9i R2 on Linux
I have a table, tab1(c1 number,c2 number,c3 number,c4 number)

tab1 has 1000000 rows

c1 has primary index on it which is used for queries 'select xx from tab1 where c1=..'

Now, i have a query on c2 column which has skewed data, as following

select count(1),C2 from tab1 group by C2;

COUNT(1)	   C2
11	   	   -23
6	   	   -1
4613	  	   2
3		   3
21993451	   4
45		   5
10		   13
17		   99
403295	           null



If i create index on C2 and compute statistics on it with 'size 254', i am getting good execution plan.

However, i am computing stats and histogram as
exec dbms_stats.gather_table_stats('HSASYS','TAB1',METHOD_OPT=>'FOR COLUMNS C2 SIZE=254',CASCADE=>TRUE);

My Question is that, Will this command, compute histogram for column C1 as well?
If answer is 'yes', will be problematic since C1 has unique values in it?
In that case is there any option with which i can compute histograms only for selected Index

Please suggest.

Thanks and Regards,
OraSaket
Re: Help - Histogram [message #257959 is a reply to message #257947] Thu, 09 August 2007 12:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you name the column, there is no reason Oracle calculates histogram statistics for other column.

Regards
Michel
Re: Help - Histogram [message #257979 is a reply to message #257947] Thu, 09 August 2007 13:17 Go to previous messageGo to next message
orasaket
Messages: 70
Registered: November 2006
Member
Thanks Michel !!

However,
suppose if i haven't name the column in gather_table_stats and i want to compute stats on table with histogram for both columns i will not be able to mention SIZE parameter.

Since Unique key column needs SIZE 1
and skewed will need 254 something.

Thanks and Regards,
OraSaket
Re: Help - Histogram [message #257984 is a reply to message #257979] Thu, 09 August 2007 13:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t 
  2  as 
  3  select rownum c1,
  4         case when rownum < 2 then 1
  5              when rownum < 10 then 2
  6              when rownum < 50 then 3
  7              when rownum < 1000 then 10
  8              end c2
  9  from all_objects
 10  where rownum < 3000
 11  /

Table created.

SQL> select count(*),C2 from t group by C2 order by c2;
  COUNT(*)         C2
---------- ----------
         1          1
         8          2
        40          3
       950         10
      2000

5 rows selected.

SQL> exec dbms_stats.gather_table_stats(user,'T',METHOD_OPT=>'FOR COLUMNS C1 SIZE 1 COLUMNS C2 SIZE 254');

PL/SQL procedure successfully completed.

SQL> select column_name, num_buckets from user_tab_col_statistics where table_name='T';
COLUMN_NAME                    NUM_BUCKETS
------------------------------ -----------
C1                                       1
C2                                       4

2 rows selected.

Regards
Michel
Re: Help - Histogram [message #257994 is a reply to message #257947] Thu, 09 August 2007 14:36 Go to previous message
orasaket
Messages: 70
Registered: November 2006
Member
Simply Best and very useful !!

Many thanks Michel.

I wasn't knowing this method

Regards,
OraSaket
Previous Topic: dead ORADISM process
Next Topic: cached objects in buffer
Goto Forum:
  


Current Time: Fri Dec 09 00:03:59 CST 2016

Total time taken to generate the page: 0.08879 seconds