RE: Manipulate DATE histograms

From: Milen Kulev <makulev_at_gmx.net>
Date: Thu, 14 Dec 2006 22:39:08 +0100
Message-ID: <004801c71fc8$49c18cd0$0a00a8c0@trivadis.com>

... And for avgclen and flags parameters of SET_COLUMN_STATS the documentation (http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_stats2.htm#1003948) says:

Avgclen -Average length for the column (in bytes). Flags -For internal Oracle use (should be left as NULL)

Quick & dirty test with exp/imp showed me something else:

SQL> insert into  dummy values (sysdate -100) ;
SQL>  insert into  dummy values (sysdate ) ;
SQL>  insert into  dummy values (sysdate + 100) ;
SQL> exec dbms_stats.gather_table_stats(ownname => 'TEST', tabname => 'DUMMY', method_opt => 'FOR all COLUMNS SIZE 1');

2) exp file=qt.dmp tables=DUMMY

3) strings qt.dmp > OUT && vi OUT
...
BEGIN DBMS_STATS.SET_TABLE_STATS(NULL,'"DUMMY"',NULL,NULL,NULL,3,1,8,6); END; DECLARE
SREC DBMS_STATS.STATREC;
BEGIN

SREC.MINVAL := '786A0904072924'; 
SREC.MAXVAL := '786B0317072934'; 
SREC.EAVS := 0; SREC.CHVALS := NULL; 
SREC.NOVALS := DBMS_STATS.NUMARRAY(2453983.27818287,2454183.27836806);  <---- DATE represented as array of numeric ?

SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1y);

SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"DUMMY"','"D1"', NULL ,NULL,NULL,3,.333333333333333,0,srec,8,6); <---- DATE is 8 bytes and flags is 6 and not NULL as documentation suggested ? END;
....

4) DATE is 7 bytes
SQL> select vsize(d1), D1 from dummy ;

VSIZE(D1) D1
---------- ---------

         7 04-SEP-06
         7 13-DEC-06
         7 23-MAR-07

Best Regards,
Milen

-----Original Message-----

From: Wolfgang Breitling [mailto:breitliw_at_centrexcc.com] Sent: Thursday, December 14, 2006 8:43 PM To: Milen Kulev
Cc: oracle-l_at_freelists.org
Subject: RE: Manipulate DATE histograms

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 - 15:39:08 CST