Optimizer overrides

RUNTIMESTATISTICS provides information about user-specified optimizer hints that were specified by using a --DERBY-PROPERTIES clause.

See Overriding the default optimizer behavior for details about how to use this clause.

The following example shows a SELECT statement in which the optimizer was forced to use a particular index:

SELECT * FROM t1 -- DERBY-PROPERTIES index = t1_c1 
FOR UPDATE OF c2, c1

RUNTIMESTATISTICS returns the following information about this statement:

Statement Name: 
		 null
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
Related concepts
Statistics timing
Statement execution plan
Optimizer estimates
Understanding XPLAIN style database tables