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();
}

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 ( ) ; }
where there are multiple indices , and we have an order by , select the index that already has the required ordering .
