900 distinct values, yet density yields 0.5??

From: <hansdegit_at_hotmail.com>
Date: 3 Mar 2006 02:23:21 -0800
Message-ID: <1141381401.641952.33470@z34g2000cwc.googlegroups.com>

 Hi,

Ora 9.2.06 AIX

In a Peoplesoft environment a ran a dbms_stats.gather_table_stats on the PS_JRNL_LN table. In that table, there is a column called process_instance, which holds some kind of batch number.

Now, when I gather stats, Oracle sees 900 distinct values for that column. But, and now comes the part I don't understand, when I look in DBA_TAB_COLUMNS the column density does not equal 1/900, but .5. So according to Oracle, a full table scan would be a viable plan to perform an update using only process_instance in the predicate.

I did a 10053 trace and found that Oracle uses the density, rather than NDV... <snip>
SINGLE TABLE ACCESS PATH

Column: PROCESS_IN  Col#: 42     Table: PS_JRNL_LN   Alias: PS_JRNL_LN
    NDV: 900       NULLS: 0         DENS: 5.0000e-01 LO:  0  HI:

9000012588

    NO HISTOGRAM: #BKT: 1 #VAL: 2
  TABLE: PS_JRNL_LN ORIG CDN: 4065419 ROUNDED CDN: 2032710 CMPTD CDN: 2032710
  Access path: tsc Resc: 17020 Resp: 16972   Skip scan: ss-sel 0 andv 1539
    ss cost 9234
    index io scan cost 0
  Access path: index (scan)

      Index: PSDJRNL_LN
  TABLE: PS_JRNL_LN
      RSC_CPU: 4644964186 RSC_IO: 322723   IX_SEL: 5.0000e-01 TB_SEL: 5.0000e-01   BEST_CST: 17021.00 PATH: 2 Degree: 1

</snip>

I ran this command to gather stats:
begin
  dbms_stats.gather_table_stats(

      ownname =>  'SYSADM'
           , tabname => 'PS_JRNL_LN'


      , estimate_percent => dbms_stats.auto_sample_size

      , block_sample => null

      , method_opt => 'for all columns size 1'

      , degree => dbms_stats.default_degree

      , granularity => 'default'

      , cascade  => true

);
end;

Am I hitting a very serious bug, or is this 'expected behaviour'?

Please help.

TIA,
Hans Received on Fri Mar 03 2006 - 04:23:21 CST