From oracle-l-bounce@freelists.org Thu Sep 2 20:32:58 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i831Wvl17893 for ; Thu, 2 Sep 2004 20:32:57 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i831WvI17888 for ; Thu, 2 Sep 2004 20:32:57 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 485B172E6FC; Thu, 2 Sep 2004 20:11:55 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 17515-93; Thu, 2 Sep 2004 20:11:55 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9CA177309A3; Thu, 2 Sep 2004 19:54:02 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 02 Sep 2004 19:52:30 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8738A72C2B5 for ; Thu, 2 Sep 2004 19:43:51 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 08222-78 for ; Thu, 2 Sep 2004 19:43:51 -0500 (EST) Received: from www.hotsos.com (hotsos.com [209.120.206.15]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A2F0772CE58 for ; Thu, 2 Sep 2004 19:16:25 -0500 (EST) Received: from Dumbledore (c-67-171-174-60.client.comcast.net [67.171.174.60]) (authenticated (0 bits)) by www.hotsos.com (8.12.11/8.11.0) with ESMTP id i830Je4i016022 for ; Thu, 2 Sep 2004 19:19:40 -0500 From: "Karen Morton" To: Subject: Histogram ENDPOINT_ACTUAL_VALUE empty using DBMS_STATS Date: Thu, 2 Sep 2004 17:19:35 -0700 Organization: Hotsos Enterprises, Ltd. Message-ID: <000401c4914b$b4827600$6401a8c0@Dumbledore> MIME-Version: 1.0 Content-type: text/plain X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.3416 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 9013 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: karen.morton@hotsos.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org I was doing some histogram testing and came across something I couldn't explain nor could I find anything on Metalink about it and was hoping someone could assist. Here are the specifics: 9.2.0.4 on Windows XP table with 1 million rows county column (varchar2(30)) has 11 distinct values data values in the column are distributed as follows: COUNTY COUNT(*) -------------------- ---------- ANDERSON 50000 BROWN 25000 CANNON 25000 DAVIDSON 50000 FAYETTE 50000 GORDON 25000 HARRISON 25000 LANCOME 25000 MULTNOMAH 25000 SMITH 100000 WASHINGTON 600000 I collect a histogram on the column using dbms_stats as follows: exec dbms_stats.gather_table_stats (user,'my_table', method_opt => 'for columns county') The resulting information shown in dba_histograms is: Name Endpoint # Endpoint Value Endpoint Actual Value COUNTY 50000 339086732722891000000000000000000000 COUNTY 75000 344361036295129000000000000000000000 COUNTY 100000 349208450178451000000000000000000000 COUNTY 150000 354401379301588000000000000000000000 COUNTY 200000 364786209484552000000000000000000000 COUNTY 225000 370261905164842000000000000000000000 COUNTY 250000 375170252614487000000000000000000000 COUNTY 275000 395939118500926000000000000000000000 COUNTY 300000 401536910355255000000000000000000000 COUNTY 400000 432528194537519000000000000000000000 COUNTY 1000000 453054781625798000000000000000000000 Note that the ENDPOINT_ACTUAL_VALUE column is null. I then collect the histogram on the column using ANALYZE as follows: ANALYZE TABLE my_table COMPUTE STATISTICS FOR COLUMNS county ; The resulting information shown in dba_histograms is: Name Endpoint # Endpoint Value Endpoint Actual Value COUNTY 50000 339086732722891000000000000000000000 ANDERSON COUNTY 75000 344361036295129000000000000000000000 BROWN COUNTY 100000 349208450178451000000000000000000000 CANNON COUNTY 150000 354401379301588000000000000000000000 DAVIDSON COUNTY 200000 364786209484552000000000000000000000 FAYETTE COUNTY 225000 370261905164842000000000000000000000 GORDON COUNTY 250000 375170252614487000000000000000000000 HARRISON COUNTY 275000 395939118500926000000000000000000000 LANCOME COUNTY 300000 401536910355255000000000000000000000 MULTNOMAH COUNTY 400000 432528194537519000000000000000000000 SMITH COUNTY 1000000 453054781625798000000000000000000000 WASHINGTON Using ANALYZE the ENDPOINT_ACTUAL_VALUE column is now populated as I expected it to be. Not that this is a big deal, but it is annoying. So, my question is this: Why doesn't the ENDPOINT_ACTUAL_VALUE column get populated when using dbms_stats? Looks like a bug to me but I couldn't find anything to confirm. Karen Morton Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events at http://www.hotsos.com/education/schedule.html --- To unsubscribe - mailto:oracle-l-request@freelists.org&subject=unsubscribe To read recent messages - http://freelists.org/archives/oracle-l/09-2004