/* 
	Name:			Date_distribution.sql
	Purpose:		Test accuracy of stats for different 'policies' for unknown dates
	Date:			23/09/2004 NL - Created	
*/
SPOOL C:\TEMP\DATE_DIST.LOG

alter session set nls_date_format = 'dd-mon-yyyy';  
SET LINES 120

drop table t1;
create table t1(
id				number,
high_date		date not null,
null_date		date,
char_date		varchar2(8) not null,
num_date		number(8) not null);

alter table t1 add constraint pk_t1 primary key(id);
create index idx_high on t1(high_date);
create index idx_null on t1(null_date);
create index idx_char on t1(char_date);
create index idx_num on t1(num_date);

declare
	l_rnd  number;
	l_date date;
begin
	dbms_random.seed(TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS'));
	for i in 1..20000 loop
		l_date := trunc(sysdate - i/10);
		insert into t1(
		id,
		high_date,
		null_date,
		char_date,
		num_date) 
		values (
		i,
		l_date,
		l_date,
		to_char(l_date,'YYYYMMDD'),
		to_number(to_char(l_date,'YYYYMMDD')));
	end loop;
	commit;
end;
/

/* 
set some recent records 'current'
*/

update t1
set high_date = to_date('31/12/9999','dd/mm/yyyy'),
null_date = null,
char_date = ' ',
num_date = 0
where high_date > sysdate - 30;

/*
leave some 'orphaned' records
*/
update t1
set high_date = to_date('31/12/9999','dd/mm/yyyy'),
null_date = null,
char_date = ' ',
num_date = 0
where high_date < sysdate - 30
and mod(id,20) = 0;


commit;

/*
show distribution
*/
prompt ' distribution of current records'
select trunc(high_date,'YYYY') high_year,trunc(null_date,'YYYY') null_year,SUBSTR(CHAR_DATE,1,4) char_year,SUBSTR(TO_CHAR(NUM_DATE),1,4) num_year,COUNT(*)
FROM T1
GROUP BY trunc(high_date,'YYYY'),trunc(null_date,'YYYY'),SUBSTR(CHAR_DATE,1,4),SUBSTR(TO_CHAR(NUM_DATE),1,4);

prompt ' my range scan christmas 2003 to liz's birthday'
select count(*)
from t1
where high_date between to_date('25/12/2003','dd/mm/yyyy') and to_date('04/01/2004','dd/mm/yyyy');


exec dbms_stats.gather_table_stats(user,'T1',CASCADE => TRUE);

/*
'current' plans
*/

EXPLAIN PLAN
FOR
SELECT ID FROM T1
WHERE HIGH_DATE = TO_DATE('31/12/9999','DD/MM/YYYY');

prompt 'Using a high date to find current records'
@show_plan_9i

EXPLAIN PLAN
FOR
SELECT ID FROM T1
WHERE null_DATE is null;

prompt 'Using a null date to find current records'
@show_plan_9i

EXPLAIN PLAN
FOR
SELECT ID FROM T1
WHERE char_DATE = ' ';

prompt 'using a character date to find current records'
@show_plan_9i

EXPLAIN PLAN
FOR
SELECT ID FROM T1
WHERE NUM_DATE = 0;

prompt 'Using a numeric date to find current records'
@show_plan_9i

EXPLAIN PLAN
FOR
SELECT ID FROM T1
WHERE HIGH_DATE BETWEEN TO_DATE('25/12/2003','DD/MM/YYYY') AND TO_DATE('05/01/2004','DD/MM/YYYY');

prompt 'Using a high date for a range scan'
@show_plan_9i

EXPLAIN PLAN
FOR
SELECT ID FROM T1
WHERE null_DATE BETWEEN TO_DATE('25/12/2003','DD/MM/YYYY') AND TO_DATE('05/01/2004','DD/MM/YYYY');

prompt 'Using a null date for a range scan'
@show_plan_9i

EXPLAIN PLAN
FOR
SELECT ID FROM T1
WHERE char_DATE BETWEEN '20031225' AND '20040105';

prompt 'using a character date for a range scan'
@show_plan_9i

EXPLAIN PLAN
FOR
SELECT ID FROM T1
WHERE NUM_DATE BETWEEN 20031225 AND 20040105;

prompt 'Using a numeric date for a range scan'
@show_plan_9i


prompt 'Now try histograms'
exec dbms_stats.gather_table_stats(user,'T1',METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 253');


EXPLAIN PLAN
FOR
SELECT ID FROM T1
WHERE HIGH_DATE = TO_DATE('31/12/9999','DD/MM/YYYY');

prompt 'Using a high date to find current records with histograms'
@show_plan_9i

EXPLAIN PLAN
FOR
SELECT ID FROM T1
WHERE null_DATE is null;

prompt 'Using a null date to find current records with histograms'
@show_plan_9i

EXPLAIN PLAN
FOR
SELECT ID FROM T1
WHERE char_DATE = ' ';

prompt 'using a character date to find current records with histograms'
@show_plan_9i

EXPLAIN PLAN
FOR
SELECT ID FROM T1
WHERE NUM_DATE = 0;

prompt 'Using a numeric date to find current records with histograms'
@show_plan_9i


EXPLAIN PLAN
FOR
SELECT ID FROM T1
WHERE HIGH_DATE BETWEEN TO_DATE('25/12/2003','DD/MM/YYYY') AND TO_DATE('05/01/2004','DD/MM/YYYY');

prompt 'Using a high date for a range scan with histograms'
@show_plan_9i

EXPLAIN PLAN
FOR
SELECT ID FROM T1
WHERE null_DATE BETWEEN TO_DATE('25/12/2003','DD/MM/YYYY') AND TO_DATE('05/01/2004','DD/MM/YYYY');

prompt 'Using a null date for a range scan with histograms'
@show_plan_9i

EXPLAIN PLAN
FOR
SELECT ID FROM T1
WHERE char_DATE BETWEEN '20031225' AND '20040105';

prompt 'using a character date for a range scan with histograms'
@show_plan_9i

EXPLAIN PLAN
FOR
SELECT ID FROM T1
WHERE NUM_DATE BETWEEN 20031225 AND 20040105;

prompt 'Using a numeric date for a range scan with histograms'
@show_plan_9i

SPOOL OFF