aggregateoptimization.out

derby database source code.good for you.

ij> -- test various aggregate optimizations
set isolation to rr;
0 rows inserted/updated/deleted
ij> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;
ij> -- create and populate tables
create table t1(c1 int, c2 char(200));
0 rows inserted/updated/deleted
ij> insert into t1 (c1) values 10, 9, 10, 9, 8, 7, 6, 1, 3;
9 rows inserted/updated/deleted
ij> update t1 set c2 = CHAR(c1);
9 rows inserted/updated/deleted
ij> -- distinct min -> min, distinct max -> max
call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
0 rows inserted/updated/deleted
ij> maximumdisplaywidth 7000;
ij> select min(distinct c1), max(distinct(c1)) from t1;
1          |2          
-----------------------
1          |10         
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	select min(distinct c1), max(distinct(c1)) from t1
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text: 
Project-Restrict ResultSet (4):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close time (milliseconds) = 0
	restriction time (milliseconds) = 0
	projection time (milliseconds) = 0
Source result set:
	Scalar Aggregate ResultSet:
	Number of opens = 1
	Rows input = 9
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
	Index Key Optimization = false
	Source result set:
		Project-Restrict ResultSet (3):
		Number of opens = 1
		Rows seen = 9
		Rows filtered = 0
		restriction = false
		projection = true
			constructor time (milliseconds) = 0
			open time (milliseconds) = 0
			next time (milliseconds) = 0
			close time (milliseconds) = 0
			restriction time (milliseconds) = 0
			projection time (milliseconds) = 0
		Source result set:
			Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer
			Number of opens = 1
			Rows seen = 9
			Rows filtered = 0
			Fetch Size = 16
				constructor time (milliseconds) = 0
				open time (milliseconds) = 0
				next time (milliseconds) = 0
				close time (milliseconds) = 0
				next time in milliseconds/row = 0
			scan information: 
				Bit set of columns fetched={0}
				Number of columns fetched=1
				Number of pages visited=1
				Number of rows qualified=9
				Number of rows visited=9
				Scan type=heap
				start position: 
null				stop position: 
null				qualifiers:
None
ij> select min(distinct c1), max(distinct(c1)) from t1 group by c1;
1          |2          
-----------------------
1          |1          
3          |3          
6          |6          
7          |7          
8          |8          
9          |9          
10         |10         
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	select min(distinct c1), max(distinct(c1)) from t1 group by c1
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text: 
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 7
Rows filtered = 0
restriction = false
projection = true
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close time (milliseconds) = 0
	restriction time (milliseconds) = 0
	projection time (milliseconds) = 0
Source result set:
	Project-Restrict ResultSet (4):
	Number of opens = 1
	Rows seen = 7
	Rows filtered = 0
	restriction = false
	projection = true
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
		restriction time (milliseconds) = 0
		projection time (milliseconds) = 0
	Source result set:
		Grouped Aggregate ResultSet:
		Number of opens = 1
		Rows input = 9
		Has distinct aggregate = false
		In sorted order = false
		Sort information: 
			Number of rows input=9
			Number of rows output=7
			constructor time (milliseconds) = 0
			open time (milliseconds) = 0
			next time (milliseconds) = 0
			close time (milliseconds) = 0
		Source result set:
			Project-Restrict ResultSet (3):
			Number of opens = 1
			Rows seen = 9
			Rows filtered = 0
			restriction = false
			projection = true
				constructor time (milliseconds) = 0
				open time (milliseconds) = 0
				next time (milliseconds) = 0
				close time (milliseconds) = 0
				restriction time (milliseconds) = 0
				projection time (milliseconds) = 0
			Source result set:
				Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer
				Number of opens = 1
				Rows seen = 9
				Rows filtered = 0
				Fetch Size = 1
					constructor time (milliseconds) = 0
					open time (milliseconds) = 0
					next time (milliseconds) = 0
					close time (milliseconds) = 0
					next time in milliseconds/row = 0
				scan information: 
					Bit set of columns fetched={0}
					Number of columns fetched=1
					Number of pages visited=1
					Number of rows qualified=9
					Number of rows visited=9
					Scan type=heap
					start position: 
null					stop position: 
null					qualifiers:
None
ij> -- min optimization
create index i1 on t1(c1);
0 rows inserted/updated/deleted
ij> -- min column is 1st column in index
select min(c1) from t1;
1          
-----------
1          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- min column is 1st column in index
select min(c1) from t1
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text: 
Project-Restrict ResultSet (4):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close time (milliseconds) = 0
	restriction time (milliseconds) = 0
	projection time (milliseconds) = 0
Source result set:
	Scalar Aggregate ResultSet:
	Number of opens = 1
	Rows input = 1
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
	Index Key Optimization = true
	Source result set:
		Project-Restrict ResultSet (3):
		Number of opens = 1
		Rows seen = 1
		Rows filtered = 0
		restriction = false