db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From banda...@apache.org
Subject svn commit: r369619 [2/4] - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/reference/ engine/org/apache/derby/iapi/sql/compile/ engine/org/apache/derby/iapi/sql/execute/ engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/impl...
Date Tue, 17 Jan 2006 01:11:11 GMT
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/optimizerOverrides.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/optimizerOverrides.out?rev=369619&r1=369618&r2=369619&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/optimizerOverrides.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/optimizerOverrides.out Mon Jan 16 17:10:52 2006
@@ -1,5 +1,9 @@
 ij> -- test the optimizer overrides
 autocommit off;
+ij> -- change display width in anticipation of runtimestatistics
+maximumdisplaywidth 5000;
+ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
+Statement executed.
 ij> -- create the tables
 create table t1 (c1 int, c2 int, c3 int, constraint cons1 primary key(c1, c2));
 0 rows inserted/updated/deleted
@@ -115,6 +119,59 @@
 2 |2 |2          
 3 |3 |3          
 4 |4 |4          
+ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    
+-----
+Statement Name: 
+	SQL_CURSH200C1
+Statement Text: 
+	select * from t1 --derby-properties index = t1_c1 
+for update
+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: 
+Index Row to Base Row ResultSet for T1:
+Number of opens = 1
+Rows seen = 4
+Columns accessed from heap = {0, 1, 2}
+	constructor time (milliseconds) = 0
+	open time (milliseconds) = 0
+	next time (milliseconds) = 0
+	close time (milliseconds) = 0
+	User supplied optimizer overrides on T1 are { index=T1_C1 }
+	Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using exclusive row locking chosen by the optimizer
+	Number of opens = 1
+	Rows seen = 4
+	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=All
+		Number of columns fetched=2
+		Number of deleted rows visited=0
+		Number of pages visited=1
+		Number of rows qualified=4
+		Number of rows visited=4
+		Scan type=btree
+		Tree height=1
+		start position: 
+	None
+		stop position: 
+	None
+		qualifiers:
+None
 ij> select * from t1 exposedname --derby-properties index = t1_c1 
 for update;
 C1 |C2 |C3         
@@ -123,6 +180,59 @@
 2 |2 |2          
 3 |3 |3          
 4 |4 |4          
+ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    
+-----
+Statement Name: 
+	SQL_CURSH200C1
+Statement Text: 
+	select * from t1 exposedname --derby-properties index = t1_c1 
+for update
+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: 
+Index Row to Base Row ResultSet for T1:
+Number of opens = 1
+Rows seen = 4
+Columns accessed from heap = {0, 1, 2}
+	constructor time (milliseconds) = 0
+	open time (milliseconds) = 0
+	next time (milliseconds) = 0
+	close time (milliseconds) = 0
+	User supplied optimizer overrides on T1 are { index=T1_C1 }
+	Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using exclusive row locking chosen by the optimizer
+	Number of opens = 1
+	Rows seen = 4
+	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=All
+		Number of columns fetched=2
+		Number of deleted rows visited=0
+		Number of pages visited=1
+		Number of rows qualified=4
+		Number of rows visited=4
+		Scan type=btree
+		Tree height=1
+		start position: 
+	None
+		stop position: 
+	None
+		qualifiers:
+None
 ij> select * from t1 --derby-properties index = t1_c1 
 for update of c2, c1;
 C1 |C2 |C3         
@@ -131,6 +241,59 @@
 2 |2 |2          
 3 |3 |3          
 4 |4 |4          
+ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    
+-----
+Statement Name: 
+	SQL_CURSH200C1
+Statement Text: 
+	select * from t1 --derby-properties index = t1_c1 
+for update of c2, 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: 
+Index Row to Base Row ResultSet for T1:
+Number of opens = 1
+Rows seen = 4
+Columns accessed from heap = {0, 1, 2}
+	constructor time (milliseconds) = 0
+	open time (milliseconds) = 0
+	next time (milliseconds) = 0
+	close time (milliseconds) = 0
+	User supplied optimizer overrides on T1 are { index=T1_C1 }
+	Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using exclusive row locking chosen by the optimizer
+	Number of opens = 1
+	Rows seen = 4
+	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=All
+		Number of columns fetched=2
+		Number of deleted rows visited=0
+		Number of pages visited=1
+		Number of rows qualified=4
+		Number of rows visited=4
+		Scan type=btree
+		Tree height=1
+		start position: 
+	None
+		stop position: 
+	None
+		qualifiers:
+None
 ij> select * from t1 exposedname --derby-properties index = t1_c1 
 for update of c2, c1;
 C1 |C2 |C3         
@@ -139,6 +302,59 @@
 2 |2 |2          
 3 |3 |3          
 4 |4 |4          
+ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    
+-----
+Statement Name: 
+	SQL_CURSH200C1
+Statement Text: 
+	select * from t1 exposedname --derby-properties index = t1_c1 
+for update of c2, 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: 
+Index Row to Base Row ResultSet for T1:
+Number of opens = 1
+Rows seen = 4
+Columns accessed from heap = {0, 1, 2}
+	constructor time (milliseconds) = 0
+	open time (milliseconds) = 0
+	next time (milliseconds) = 0
+	close time (milliseconds) = 0
+	User supplied optimizer overrides on T1 are { index=T1_C1 }
+	Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using exclusive row locking chosen by the optimizer
+	Number of opens = 1
+	Rows seen = 4
+	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=All
+		Number of columns fetched=2
+		Number of deleted rows visited=0
+		Number of pages visited=1
+		Number of rows qualified=4
+		Number of rows visited=4
+		Scan type=btree
+		Tree height=1
+		start position: 
+	None
+		stop position: 
+	None
+		qualifiers:
+None
 ij> -- constraint which includes columns in for update of list
 select * from t1 --derby-properties constraint = cons1 
 for update;
@@ -148,6 +364,59 @@
 2 |2 |2          
 3 |3 |3          
 4 |4 |4          
+ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    
+-----
+Statement Name: 
+	SQL_CURSH200C1
+Statement Text: 
+	select * from t1 --derby-properties constraint = cons1 
+for update
+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: 
+Index Row to Base Row ResultSet for T1:
+Number of opens = 1
+Rows seen = 4
+Columns accessed from heap = {0, 1, 2}
+	constructor time (milliseconds) = 0
+	open time (milliseconds) = 0
+	next time (milliseconds) = 0
+	close time (milliseconds) = 0
+	User supplied optimizer overrides on T1 are { index=xxxxGENERATED-IDxxxx }
+	Index Scan ResultSet for T1 using constraint CONS1 at read committed isolation level using exclusive row locking chosen by the optimizer
+	Number of opens = 1
+	Rows seen = 4
+	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=All
+		Number of columns fetched=3
+		Number of deleted rows visited=0
+		Number of pages visited=1
+		Number of rows qualified=4
+		Number of rows visited=4
+		Scan type=btree
+		Tree height=1
+		start position: 
+	None
+		stop position: 
+	None
+		qualifiers:
+None
 ij> select * from t1 exposedname --derby-properties constraint = cons1 
 for update;
 C1 |C2 |C3         
@@ -156,6 +425,59 @@
 2 |2 |2          
 3 |3 |3          
 4 |4 |4          
+ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    
+-----
+Statement Name: 
+	SQL_CURSH200C1
+Statement Text: 
+	select * from t1 exposedname --derby-properties constraint = cons1 
+for update
+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: 
+Index Row to Base Row ResultSet for T1:
+Number of opens = 1
+Rows seen = 4
+Columns accessed from heap = {0, 1, 2}
+	constructor time (milliseconds) = 0
+	open time (milliseconds) = 0
+	next time (milliseconds) = 0
+	close time (milliseconds) = 0
+	User supplied optimizer overrides on T1 are { index=xxxxGENERATED-IDxxxx }
+	Index Scan ResultSet for T1 using constraint CONS1 at read committed isolation level using exclusive row locking chosen by the optimizer
+	Number of opens = 1
+	Rows seen = 4
+	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=All
+		Number of columns fetched=3
+		Number of deleted rows visited=0
+		Number of pages visited=1
+		Number of rows qualified=4
+		Number of rows visited=4
+		Scan type=btree
+		Tree height=1
+		start position: 
+	None
+		stop position: 
+	None
+		qualifiers:
+None
 ij> select * from t1 --derby-properties constraint = cons1 
 for update of c2, c1;
 C1 |C2 |C3         
@@ -164,6 +486,59 @@
 2 |2 |2          
 3 |3 |3          
 4 |4 |4          
+ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    
+-----
+Statement Name: 
+	SQL_CURSH200C1
+Statement Text: 
+	select * from t1 --derby-properties constraint = cons1 
+for update of c2, 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: 
+Index Row to Base Row ResultSet for T1:
+Number of opens = 1
+Rows seen = 4
+Columns accessed from heap = {0, 1, 2}
+	constructor time (milliseconds) = 0
+	open time (milliseconds) = 0
+	next time (milliseconds) = 0
+	close time (milliseconds) = 0
+	User supplied optimizer overrides on T1 are { index=xxxxGENERATED-IDxxxx }
+	Index Scan ResultSet for T1 using constraint CONS1 at read committed isolation level using exclusive row locking chosen by the optimizer
+	Number of opens = 1
+	Rows seen = 4
+	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=All
+		Number of columns fetched=3
+		Number of deleted rows visited=0
+		Number of pages visited=1
+		Number of rows qualified=4
+		Number of rows visited=4
+		Scan type=btree
+		Tree height=1
+		start position: 
+	None
+		stop position: 
+	None
+		qualifiers:
+None
 ij> select * from t1 exposedname --derby-properties constraint = cons1 
 for update of c2, c1;
 C1 |C2 |C3         
@@ -172,9 +547,81 @@
 2 |2 |2          
 3 |3 |3          
 4 |4 |4          
+ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    
+-----
+Statement Name: 
+	SQL_CURSH200C1
+Statement Text: 
+	select * from t1 exposedname --derby-properties constraint = cons1 
+for update of c2, 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: 
+Index Row to Base Row ResultSet for T1:
+Number of opens = 1
+Rows seen = 4
+Columns accessed from heap = {0, 1, 2}
+	constructor time (milliseconds) = 0
+	open time (milliseconds) = 0
+	next time (milliseconds) = 0
+	close time (milliseconds) = 0
+	User supplied optimizer overrides on T1 are { index=xxxxGENERATED-IDxxxx }
+	Index Scan ResultSet for T1 using constraint CONS1 at read committed isolation level using exclusive row locking chosen by the optimizer
+	Number of opens = 1
+	Rows seen = 4
+	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=All
+		Number of columns fetched=3
+		Number of deleted rows visited=0
+		Number of pages visited=1
+		Number of rows qualified=4
+		Number of rows visited=4
+		Scan type=btree
+		Tree height=1
+		start position: 
+	None
+		stop position: 
+	None
+		qualifiers:
+None
 ij> -- select from view with bad derby-properties list
 select * from neg_v1;
 ERROR 42Y44: Invalid key 'asdf' specified in the Properties list of a FROM list. The case-sensitive keys that are currently supported are 'index, constraint, joinStrategy'. 
+ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    
+-----
+Statement Name: 
+	null
+Statement Text: 
+	call SYSIBM.SQLCAMESSAGE(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+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: 
+null                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
 ij> -- bad derby-properties tests on outer joins
 select * from t1 --derby-properties i = a 
 left outer join t2 on 1=1;
@@ -198,6 +645,25 @@
 ij> -- dependent on index
 prepare p1 as 'select * from t1 --derby-properties index = t1_c1
 ';
+ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    
+-----
+Statement Name: 
+	null
+Statement Text: 
+	call SYSIBM.SQLCAMESSAGE(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+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: 
+null                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
 ij> execute p1;
 C1 |C2 |C3         
 -----
@@ -205,15 +671,111 @@
 2 |2 |2          
 3 |3 |3          
 4 |4 |4          
+ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    
+-----
+Statement Name: 
+	SQL_CURSH200C1
+Statement Text: 
+	select * from t1 --derby-properties index = t1_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: 
+Index Row to Base Row ResultSet for T1:
+Number of opens = 1
+Rows seen = 4
+Columns accessed from heap = {0, 1, 2}
+	constructor time (milliseconds) = 0
+	open time (milliseconds) = 0
+	next time (milliseconds) = 0
+	close time (milliseconds) = 0
+	User supplied optimizer overrides on T1 are { index=T1_C1 }
+	Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
+	Number of opens = 1
+	Rows seen = 4
+	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={1}
+		Number of columns fetched=1
+		Number of deleted rows visited=0
+		Number of pages visited=1
+		Number of rows qualified=4
+		Number of rows visited=4
+		Scan type=btree
+		Tree height=1
+		start position: 
+	None
+		stop position: 
+	None
+		qualifiers:
+None
 ij> drop index t1_c1;
 0 rows inserted/updated/deleted
 ij> execute p1;
 ERROR 42Y46: Invalid Properties list in FROM list.  There is no index 'T1_C1' on table 'T1'.
+ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    
+-----
+Statement Name: 
+	null
+Statement Text: 
+	call SYSIBM.SQLCAMESSAGE(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+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: 
+null                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
 ij> remove p1;
 ij> rollback;
 ij> -- dependent on constraint
 prepare p2 as 'select * from t1 --derby-properties constraint = cons1
 ';
+ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    
+-----
+Statement Name: 
+	SQL_CURSH200C2
+Statement Text: 
+	values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
+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: 
+Row ResultSet:
+Number of opens = 1
+Rows returned = 1
+	constructor time (milliseconds) = 0
+	open time (milliseconds) = 0
+	next time (milliseconds) = 0
+	close time (milliseconds) = 0
 ij> execute p2;
 C1 |C2 |C3         
 -----
@@ -221,16 +783,83 @@
 2 |2 |2          
 3 |3 |3          
 4 |4 |4          
+ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    
+-----
+Statement Name: 
+	SQL_CURSH200C1
+Statement Text: 
+	select * from t1 --derby-properties constraint = cons1
+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: 
+Index Row to Base Row ResultSet for T1:
+Number of opens = 1
+Rows seen = 4
+Columns accessed from heap = {0, 1, 2}
+	constructor time (milliseconds) = 0
+	open time (milliseconds) = 0
+	next time (milliseconds) = 0
+	close time (milliseconds) = 0
+	User supplied optimizer overrides on T1 are { index=xxxxGENERATED-IDxxxx }
+	Index Scan ResultSet for T1 using constraint CONS1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
+	Number of opens = 1
+	Rows seen = 4
+	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}
+		Number of columns fetched=1
+		Number of deleted rows visited=0
+		Number of pages visited=1
+		Number of rows qualified=4
+		Number of rows visited=4
+		Scan type=btree
+		Tree height=1
+		start position: 
+	None
+		stop position: 
+	None
+		qualifiers:
+None
 ij> alter table t1 drop constraint cons1;
 0 rows inserted/updated/deleted
 ij> execute p2;
 ERROR 42Y48: Invalid Properties list in FROM list.  Either there is no named constraint 'CONS1' on table 'T1' or the constraint does not have a backing index.
+ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    
+-----
+Statement Name: 
+	null
+Statement Text: 
+	call SYSIBM.SQLCAMESSAGE(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+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: 
+null                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
 ij> remove p2;
 ij> rollback;
-ij> -- change display width in anticipation of runtimestatistics
-maximumdisplaywidth 5000;
-ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
-Statement executed.
 ij> -- the token derby-properties is case insensitive. Few tests for that
 select * from t1 --DeRbY-pRoPeRtIeS index = t1_c1 
 ;
@@ -266,6 +895,7 @@
 	open time (milliseconds) = 0
 	next time (milliseconds) = 0
 	close time (milliseconds) = 0
+	User supplied optimizer overrides on T1 are { index=T1_C1 }
 	Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
 	Number of opens = 1
 	Rows seen = 4
@@ -378,6 +1008,7 @@
 	open time (milliseconds) = 0
 	next time (milliseconds) = 0
 	close time (milliseconds) = 0
+	User supplied optimizer overrides on T1 are { index=t1_c2c1 }
 	Index Scan ResultSet for T1 using index t1_c2c1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
 	Number of opens = 1
 	Rows seen = 4
@@ -430,6 +1061,7 @@
 Begin Execution Timestamp : null
 End Execution Timestamp : null
 Statement Execution Plan Text: 
+User supplied optimizer overrides on T1 are { index=NULL }
 Table Scan ResultSet for T1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
 Number of opens = 1
 Rows seen = 4
@@ -485,6 +1117,7 @@
 	open time (milliseconds) = 0
 	next time (milliseconds) = 0
 	close time (milliseconds) = 0
+	User supplied optimizer overrides on T1 are { index=T1_C1 }
 	Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using share row locking chosen by the optimizer
 	Number of opens = 1
 	Rows seen = 4
@@ -546,6 +1179,7 @@
 	open time (milliseconds) = 0
 	next time (milliseconds) = 0
 	close time (milliseconds) = 0
+	User supplied optimizer overrides on T1 are { index=T1_C1 }
 	Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using exclusive row locking chosen by the optimizer
 	Number of opens = 1
 	Rows seen = 4
@@ -636,6 +1270,7 @@
 		next time (milliseconds) = 0
 		close time (milliseconds) = 0
 	Left result set:
+		User supplied optimizer overrides on T1 are { index=T1_C1 }
 		Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
 		Number of opens = 1
 		Rows seen = 4
@@ -662,6 +1297,7 @@
 			qualifiers:
 None
 	Right result set:
+		User supplied optimizer overrides on T2 are { index=T2_C2 }
 		Index Scan ResultSet for T2 using index T2_C2 at read committed isolation level using instantaneous share row locking chosen by the optimizer
 		Number of opens = 4
 		Rows seen = 16
@@ -687,6 +1323,116 @@
 	None
 			qualifiers:
 None
+ij> select 1 from --derby-PROPERTIES joinOrder=fixed
+t1, t2 where t1.c1 = t2.c1;
+1          
+-----
+1          
+1          
+1          
+1          
+ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    
+-----
+Statement Name: 
+	SQL_CURSH200C1
+Statement Text: 
+	select 1 from --derby-PROPERTIES joinOrder=fixed
+t1, t2 where t1.c1 = t2.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 (4):
+Number of opens = 1
+Rows seen = 4
+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:
+	User supplied optimizer overrides for join are { joinOrder=FIXED }
+	Hash Join ResultSet:
+	Number of opens = 1
+	Rows seen from the left = 4
+	Rows seen from the right = 4
+	Rows filtered = 0
+	Rows returned = 4
+		constructor time (milliseconds) = 0
+		open time (milliseconds) = 0
+		next time (milliseconds) = 0
+		close time (milliseconds) = 0
+	Left result set:
+		Index Scan ResultSet for T1 using constraint CONS1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
+		Number of opens = 1
+		Rows seen = 4
+		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 deleted rows visited=0
+			Number of pages visited=1
+			Number of rows qualified=4
+			Number of rows visited=4
+			Scan type=btree
+			Tree height=1
+			start position: 
+	None
+			stop position: 
+	None
+			qualifiers:
+None
+	Right result set:
+		Hash Scan ResultSet for T2 using constraint CONS2 at read committed isolation level using instantaneous share row locking: 
+		Number of opens = 4
+		Hash table size = 4
+		Hash key is column number 0
+		Rows seen = 4
+		Rows filtered = 0
+			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 deleted rows visited=0
+			Number of pages visited=1
+			Number of rows qualified=4
+			Number of rows visited=4
+			Scan type=btree
+			Tree height=1
+			start position: 
+	None
+			stop position: 
+	None
+			scan qualifiers:
+None
+			next qualifiers:
+Column[0][0] Id: 0
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
 ij> -- comparisons that can't get pushed down
 select * from t1 --derby-properties index = t1_c1 
 where c1 = c1;
@@ -762,6 +1508,7 @@
 		open time (milliseconds) = 0
 		next time (milliseconds) = 0
 		close time (milliseconds) = 0
+		User supplied optimizer overrides on T1 are { index=T1_C1 }
 		Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
 		Number of opens = 1
 		Rows seen = 4
@@ -809,6 +1556,7 @@
 			open time (milliseconds) = 0
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
+			User supplied optimizer overrides on T2 are { index=T2_C2 }
 			Index Scan ResultSet for T2 using index T2_C2 at read committed isolation level using instantaneous share row locking chosen by the optimizer
 			Number of opens = 4
 			Rows seen = 16
@@ -886,6 +1634,7 @@
 		next time (milliseconds) = 0
 		close time (milliseconds) = 0
 	Left result set:
+		User supplied optimizer overrides on T1 are { joinStrategy=NESTEDLOOP }
 		Index Scan ResultSet for T1 using index T1_C1C2C3 at read committed isolation level using instantaneous share row locking chosen by the optimizer
 		Number of opens = 1
 		Rows seen = 4



Mime
View raw message