RE: Manipulate DATE histograms
- From: "Milen Kulev" <makulev@xxxxxxx>
- To: "'Wolfgang Breitling'" <breitliw@xxxxxxxxxxxxx>
- Date: Thu, 14 Dec 2006 22:39:08 +0100
... 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:
1)
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@xxxxxxxxxxxxx]
Sent: Thursday, December 14, 2006 8:43 PM
To: Milen Kulev
Cc: oracle-l@xxxxxxxxxxxxx
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
______________________________________________________________________
--
//www.freelists.org/webpage/oracle-l
Other related posts:
