Hello,
On DSS, large table 840 000 000 records.
I know that there is 2 distinct value for the field BUSINESS_UNIT reparted on a 93% / 7% basis.
Histograms have been calculated with
execute dbms_stats.gather_table_stats(
ownname => user,
tabname => 'T1',
granularity=>'GLOBAL',
estimate_percent => 0.05,
block_sample => TRUE,
method_opt => 'FOR COLUMNS BUSINESS_UNIT SIZE 2',
degree => 6);
select TABLE_NAME,COLUMN_NAME,DENSITY
from USER_TAB_COL_STATISTICS
where table_name='T1'
and column_name='BUSINESS_UNIT';
TA COLUMN_NAME DENSITY
-- ------------------------------ ------------------------------
T1 BUSINESS_UNIT .000000000616598840794179
>>>>>>>>>>>>> density * num_rows
SQL> select 6.1660E-10 * 842276500 from dual;
6.1660E-10*842276500
-------------------------
.5193476899
Metalink :
The density is expressed as a decimal number between 0 and 1.
Values close to 1 indicate that this column is unselective
Values close to 0 indicate that this column is highly selective
The more selective a column, the less rows are likely to be returned by a query referencing this column in its predicate list.
I don't get it ?!?
The field seems to be very selective according to this statistic, but in fact, it is not. Giving a certain value, user get 97% of all records !
SQL> select *
from user_tab_histograms
where table_name='T1'
and column_name='BUSINESS_UNIT';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU
------------------------------ --------------- --------------- -------------- --------------------
T1 BUSINESS_UNIT 7269 3.6963E+35
TMEURST BUSINESS_UNIT 8109 3.8002E+35
Any insight, guess, idea welcomed.
Cheers
Nicolas
All different, all Unix
