Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!z34g2000cwc.googlegroups.com!not-for-mail
From: "hansdegit@hotmail.com" <hansdegit@hotmail.com>
Newsgroups: comp.databases.oracle.server
Subject: 900 distinct values, yet density yields 0.5??
Date: 3 Mar 2006 02:23:21 -0800
Organization: http://groups.google.com
Lines: 59
Message-ID: <1141381401.641952.33470@z34g2000cwc.googlegroups.com>
NNTP-Posting-Host: 145.221.24.5
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1141381407 15460 127.0.0.1 (3 Mar 2006 10:23:27 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 3 Mar 2006 10:23:27 +0000 (UTC)
User-Agent: G2/0.2
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.1) Gecko/20060111 Firefox/1.5.0.1,gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.1 S1PS,1.1 S1PS
Complaints-To: groups-abuse@google.com
Injection-Info: z34g2000cwc.googlegroups.com; posting-host=145.221.24.5;
   posting-account=AKZQtQwAAADs3l-AXAvJSL3yq9dyJlmN
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:262677

 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