Analyze this

Here’s a little script I wrote a few years ago to make a point about using the dbms_stats package. I’ve just re-run it on 10.2.0.3 to see if it still behaves the way it used to – and it does. If you want to be just a little bit baffled, set up a database with an 8KB blocks size, a tablespace that is locally managed, uniform extent size of 1MB, using freelist management, then run the script:

drop table t2;
drop table t1;

begin
	begin		execute immediate 'purge recyclebin';
	exception	when others then null;
	end;

	begin		execute immediate 'execute dbms_stats.delete_system_stats';
	exception	when others then null;
	end;

	begin		execute immediate 'alter session set "_optimizer_cost_model"=io';
	exception	when others then null;
	end;

end;
/

alter session set workarea_size_policy = manual;
alter session set hash_area_size = 1048576;

create table t1
as
with generator as (
	select	--+ materialize
		rownum 	id
	from	all_objects
	where	rownum <= 5000
)
select
	rownum			id,
	rownum			n1,
	lpad(rownum,10,'0')	v00,
	lpad(rownum,10,'0')	v01,
	lpad(rownum,10,'0')	v02,
	lpad(rownum,10,'0')	v03,
	lpad(rownum,10,'0')	v04,
	lpad(rownum,10,'0')	v05,
	lpad(rownum,10,'0')	v06,
	lpad(rownum,10,'0')	v07,
	lpad(rownum,10,'0')	v08,
	lpad(rownum,10,'0')	v09,
	lpad(rownum,10,'0')	v10,
	lpad(rownum,10,'0')	v11,
	lpad(rownum,10,'0')	v12,
	lpad(rownum,10,'0')	v13,
	lpad(rownum,10,'0')	v14,
	lpad(rownum,10,'0')	v15,
	lpad(rownum,10,'0')	v16,
	lpad(rownum,10,'0')	v17,
	lpad(rownum,10,'0')	v18,
	lpad(rownum,10,'0')	v19,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 10000
;

create table t2
as
with generator as (
	select	--+ materialize
		rownum 	id
	from	all_objects
	where	rownum <= 5000
)
select
	rownum			id,
	rownum			n1,
	lpad(rownum,200,'0')	v100,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 10000
;

analyze table t1 compute statistics;
analyze table t2 compute statistics;

set autotrace traceonly explain

prompt	=============
prompt	Using Analyze
prompt	=============

select
	t1.v00,
	t1.v01,
	t1.v02,
	t1.v03,
	t1.v04,
	t1.v05,
	t1.v06,
	t1.v07,
	t1.v08,
	t1.v09,
	t1.v10,
	t1.v11,
	t1.v12,
	t1.v13,
	t1.v14,
	t1.v15,
	t1.v16,
	t1.v17,
	t1.v18,
	t1.v19,
	t2.v100
from
	t1,
	t2
where
	t1.id = t2.id
;

set autotrace off

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'t1',
		estimate_percent => 100,
		method_opt 	 => 'for all columns size 1'
	);

	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'t2',
		estimate_percent => 100,
		method_opt 	 => 'for all columns size 1'
	);
end;
/

set autotrace traceonly explain

prompt	================
prompt	Using dbms_stats
prompt	================

select
	t1.v00,
	t1.v01,
	t1.v02,
	t1.v03,
	t1.v04,
	t1.v05,
	t1.v06,
	t1.v07,
	t1.v08,
	t1.v09,
	t1.v10,
	t1.v11,
	t1.v12,
	t1.v13,
	t1.v14,
	t1.v15,
	t1.v16,
	t1.v17,
	t1.v18,
	t1.v19,
	t2.v100
from
	t1,
	t2
where
	t1.id = t2.id
;

set autotrace off

Note that the script runs the same query twice – check the two execution plans carefully, they may not be the same.