# Optimizer overrides

RUNTIMESTATISTICS provides information about user-specified optimizer hints that were specified by using a -- DERBY-PROPERTIES 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

Parent topic: Analyzing the information
Related concepts
Statistics timing
Statement execution plan
Optimizer estimates
Understanding XPLAIN style database tables