orderbyelimination.out

derby database source code.good for you.

ij> -- test elimination of sort for order by
set isolation to rr;
0 rows inserted/updated/deleted
ij> -- test combining of sorts for distinct and order by
-- create some tables
create table t1(c1 int, c2 int, c3 int, c4 int);
0 rows inserted/updated/deleted
ij> insert into t1 values (1, 2, 3, 4);
1 row inserted/updated/deleted
ij> insert into t1 values (2, 3, 4, 5);
1 row inserted/updated/deleted
ij> insert into t1 values (-1, -2, -3, -4);
1 row inserted/updated/deleted
ij> insert into t1 values (-2, -3, -4, -5);
1 row inserted/updated/deleted
ij> insert into t1 values (1, 2, 4, 3);
1 row inserted/updated/deleted
ij> insert into t1 values (1, 3, 2, 4);
1 row inserted/updated/deleted
ij> insert into t1 values (1, 3, 4, 2);
1 row inserted/updated/deleted
ij> insert into t1 values (1, 4, 2, 3);
1 row inserted/updated/deleted
ij> insert into t1 values (1, 4, 3, 2);
1 row inserted/updated/deleted
ij> insert into t1 values (2, 1, 4, 3);
1 row inserted/updated/deleted
ij> maximumdisplaywidth 7000;
ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
0 rows inserted/updated/deleted
ij> -- no index on t1
-- full match
select distinct c1, c2, c3, c4 from t1 order by 1, 2, 3, 4;
C1         |C2         |C3         |C4         
-----------------------------------------------
-2         |-3         |-4         |-5         
-1         |-2         |-3         |-4         
1          |2          |3          |4          
1          |2          |4          |3          
1          |3          |2          |4          
1          |3          |4          |2          
1          |4          |2          |3          
1          |4          |3          |2          
2          |1          |4          |3          
2          |3          |4          |5          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- no index on t1
-- full match
select distinct c1, c2, c3, c4 from t1 order by 1, 2, 3, 4
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: 
Sort ResultSet:
Number of opens = 1
Rows input = 10
Rows returned = 10
Eliminate duplicates = true
In sorted order = false
Sort information: 
	Number of rows input=10
	Number of rows output=10
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close 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 = 10
	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=All
		Number of columns fetched=4
		Number of pages visited=1
		Number of rows qualified=10
		Number of rows visited=10
		Scan type=heap
		start position: 
null		stop position: 
null		qualifiers:
None
ij> select distinct c1, c2, c3, c4 from t1 order by c1, c2, c3, c4;
C1         |C2         |C3         |C4         
-----------------------------------------------
-2         |-3         |-4         |-5         
-1         |-2         |-3         |-4         
1          |2          |3          |4          
1          |2          |4          |3          
1          |3          |2          |4          
1          |3          |4          |2          
1          |4          |2          |3          
1          |4          |3          |2          
2          |1          |4          |3          
2          |3          |4          |5          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	select distinct c1, c2, c3, c4 from t1 order by c1, c2, c3, c4
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: 
Sort ResultSet:
Number of opens = 1
Rows input = 10
Rows returned = 10
Eliminate duplicates = true
In sorted order = false
Sort information: 
	Number of rows input=10
	Number of rows output=10
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close 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 = 10
	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=All
		Number of columns fetched=4
		Number of pages visited=1
		Number of rows qualified=10
		Number of rows visited=10
		Scan type=heap
		start position: 
null		stop position: 
null		qualifiers:
None
ij> -- in order prefix
select distinct c3, c4 from t1 order by 1, 2;
C3         |C4         
-----------------------
-4         |-5         
-3         |-4         
2          |3          
2          |4          
3          |2          
3          |4          
4          |2          
4          |3          
4          |5          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- in order prefix
select distinct c3, c4 from t1 order by 1, 2
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: 
Sort ResultSet:
Number of opens = 1
Rows input = 10
Rows returned = 9
Eliminate duplicates = true
In sorted order = false
Sort information: 
	Number of rows input=10
	Number of rows output=9
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close 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 = 10
	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={2, 3}
		Number of columns fetched=2
		Number of pages visited=1
		Number of rows qualified=10
		Number of rows visited=10
		Scan type=heap
		start position: 
null		stop position: 
null		qualifiers:
None
ij> select distinct c3, c4 from t1 order by c3, c4;
C3         |C4         
-----------------------
-4         |-5         
-3         |-4         
2          |3          
2          |4          
3          |2          
3          |4          
4          |2          
4          |3          
4          |5          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	select distinct c3, c4 from t1 order by c3, c4
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: 
Sort ResultSet:
Number of opens = 1