RE: Manipulate DATE histograms

From: Milen Kulev <makulev_at_gmx.net>
Date: Thu, 14 Dec 2006 22:11:16 +0100
Message-ID: <004501c71fc4$64ff1c00$0a00a8c0@trivadis.com>

Hi Wolfgang,
Thank you very much for your reply/Christmas present ;) I have noticed that you are always using (..., 2,2) for the last two paramemers of DBMS_STATS.SET_COLUMN_STATS (avgclen and flags )

DBMS_STATS.SET_COLUMN_STATS (

   ownname       VARCHAR2, 
   tabname       VARCHAR2, 
   colname       VARCHAR2, 
   partname      VARCHAR2 DEFAULT NULL,
   stattab       VARCHAR2 DEFAULT NULL, 
   statid        VARCHAR2 DEFAULT NULL,
   distcnt       NUMBER DEFAULT NULL,
   density       NUMBER DEFAULT NULL,
   nullcnt       NUMBER DEFAULT NULL,
   srec          StatRec DEFAULT NULL,
   avgclen       NUMBER DEFAULT NULL, <- 2 
   flags         NUMBER DEFAULT NULL, <- 2 
   statown       VARCHAR2 DEFAULT NULL,

no_invalidate BOOLEAN DEFAULT FALSE); http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_stats2.htm#1003948

Shouldn't in this case avgclen to be set to 7 (for DATE type) ? avgclen should play a role somewhere in the CBO calculations, I think ...

And can "flags" get any other value other than 2 (asking just of curiosity) ?

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:11:16 CST