RE: Manipulate DATE histograms

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 14 Dec 2006 12:43:29 -0700
Message-Id: <20061214194256.76DFF56C3AF@turing.freelists.org>

Why do you think I pointed you to dbms_stats.prepare_column_values. That's what it is there for.

create table dummy (d1 date);
DECLARE
SREC DBMS_STATS.STATREC;
NOVALS DBMS_STATS.DATEARRAY;
BEGIN

  SREC.EAVS := 0;
  SREC.CHVALS := NULL;
  SREC.EPC := 3;

NOVALS :=
DBMS_STATS.DATEARRAY(to_date('2004-01-01','yyyy-mm-dd'),to_date('2005-01-01','yyyy-mm-dd'),to_date('2006-01-01','yyyy-mm-dd')); SREC.BKVALS := DBMS_STATS.NUMARRAY(100,200,1000); DBMS_STATS.PREPARE_COLUMN_VALUES (SREC,NOVALS); DBMS_STATS.SET_COLUMN_STATS(NULL, 'DUMMY', 'D1', NULL, NULL, NULL, 3, .00005, 0, SREC, 2, 2); END;
/

9.2.0.7> DECLARE
   2 SREC DBMS_STATS.STATREC;
   3 NOVALS DBMS_STATS.DATEARRAY;
   4 BEGIN
   5

   6   SREC.EAVS := 0;
   7   SREC.CHVALS := NULL;
   8   SREC.EPC := 3;

   9 NOVALS :=
DBMS_STATS.DATEARRAY(to_date('2004-01-01','yyyy-mm-dd'),to_date('2005-01-01','yyyy-mm-dd'),to_date('2006-01-01','yyyy-mm-dd'));   10 SREC.BKVALS := DBMS_STATS.NUMARRAY(100,200,1000);   11 DBMS_STATS.PREPARE_COLUMN_VALUES (SREC,NOVALS);   12 DBMS_STATS.SET_COLUMN_STATS(NULL, 'DUMMY', 'D1', NULL, NULL, NULL, 3, .00005, 0, SREC, 2, 2);
  13
  14 END;
  15 /

PL/SQL procedure successfully completed.

table                    column                NDV            density 
nulls     lo               hi               av lg  bkts G U last

analyzed sample

------------------------ --------------------- --------- ------------ 
--------- ---------------- ---------------- ----- ----- - - 
---------------- ---------
DUMMY                    D1                           3    5.0000E-05 
        0  AD 2004-01-01 00 AD 2006-01-01 00     2     2 Y N 2006-12-14 12:42


table                    column                       EP 
                    value actual value
------------------------ --------------------- --------- 
------------------------------------- ------------------------------
DUMMY                    D1                          100 
                  2453006
DUMMY                    D1                          300 
                  2453372
DUMMY                    D1                         1300 
                  2453737

3 rows selected.

I took it straight from the example in my paper you quoted; just changed the arraytype to datearray.
Consider it a Christmas present:

At 12:06 PM 12/14/2006, Milen Kulev wrote:
>Hi Wolfgang ,
>I have looked at DBMS_STATS.* functions and procedures.
>I have read your paper
>http://www.centrexcc.com/Using%20DBMS_STATS%20in%20Access%20Path%20Optimization.ppt.pdf
>(Page
>30)too. What I need is an example how to format the dates to RAW
>format on order to initialize SREC.BKVALS array.
>
>Best Regards. Milen

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Dec 14 2006 - 13:43:29 CST