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 Corporationwww.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
