question: private void testOrderedIndexes ( ) throws SQLException { deleteDb ( "optimizations" ) ; Connection conn = getConnection ( "optimizations" ) ; Statement stat = conn . createStatement ( ) ; stat . execute ( "CREATE TABLE my_table(K1 INT, K2 INT, " + "VAL VARCHAR, PRIMARY KEY(K1, K2))" ) ; stat . execute ( "CREATE INDEX my_index ON my_table(K1, VAL)" ) ; ResultSet rs = stat . executeQuery ( "EXPLAIN PLAN FOR SELECT * FROM my_table WHERE K1=7 " + "ORDER BY K1, VAL" ) ; rs . next ( ) ; assertContains ( rs . getString ( 1 ) , "/* PUBLIC.MY_INDEX: K1 = 7 */" ) ; stat . execute ( "DROP TABLE my_table" ) ; stat . execute ( "CREATE TABLE my_table(K1 INT, K2 INT, VAL VARCHAR)" ) ; stat . execute ( "CREATE INDEX my_index1 ON my_table(K1, K2)" ) ; stat . execute ( "CREATE INDEX my_index2 ON my_table(K1, K2, VAL)" ) ; rs = stat . executeQuery ( "EXPLAIN PLAN FOR SELECT * FROM my_table WHERE K1=7 " + "ORDER BY K1, K2, VAL" ) ; rs . next ( ) ; assertContains ( rs . getString ( 1 ) , "/* PUBLIC.MY_INDEX2: K1 = 7 */" ) ; conn . close ( ) ; }
target:  Where there are multiple indices, and we have an ORDER BY, select the index that already has the required ordering.