RE: Manipulate DATE histograms
- From: "Milen Kulev" <makulev@xxxxxxx>
- To: "'Wolfgang Breitling'" <breitliw@xxxxxxxxxxxxx>
- Date: Thu, 14 Dec 2006 22:11:16 +0100
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@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:
