Oracle 11g and Index Access

In a forum I frequent a question was raised regarding an index not being used. The poster is using Oracle 11.1.0.7, which limits the index access paths Oracle can use; a concatenated index is created on the table (which has columns x,y,z,t,q and w, for lack of better names) on columns x, y, and z. Let’s look at what 11.2.0.3 will do with such a situation then explain what may be going on with 11.1.0.7 and, as a result, why the index isn’t being used.

The original problem states that the table is new, meaning it has been created and has no data in it (possibly a daily occurrence) and at least one index is created on this table during the create table process. Of course having no data in the table means that even if you do generate statistics they will be essentially useless. Let’s set this up with 11.2.0.3, using a single-column index (since at the time this example was created the existence of the concatenated index wasn’t known) and see what happens:


SQL>
SQL> --
SQL> -- Create an empty table with no indexes
SQL> --
SQL> create table emp2 as select * from emp where 0=1;

Table created.

SQL>
SQL> --
SQL> -- Create an index on the empty table
SQL> --
SQL> create index empno_idx on emp2(empno);

Index created.

SQL>
SQL> --
SQL> -- Populate the table (will insert 1,800,000 rows)
SQL> --
SQL>
SQL> begin
  2  	     for i in 1..100000 loop
  3  		     insert into emp2
  4  		     select * from emp;
  5
  6  		     update emp2
  7  		     set empno = empno+i, mgr=mgr+i, updated='YES'
  8  		     where updated is null;
  9
 10  		     commit;
 11  	     end loop;
 12
 13  	     commit;
 14  end;
 15  /

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Select from emp2 and see if any indexes are used
SQL> --
SQL> set autotrace on
SQL>
SQL> select *
  2  from emp2
  3  where empno = (select min(empno)+1 from emp);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO UPD
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---
      7370 SMITH      CLERK           7903 17-DEC-80        800                    20 YES


Execution Plan
----------------------------------------------------------
Plan hash value: 1300931333

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    90 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP2      |     1 |    90 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMPNO_IDX | 82203 |       |     1   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE           |           |     1 |    13 |            |          |
|   4 |     TABLE ACCESS FULL       | EMP       |    18 |   234 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"= (SELECT MIN("EMPNO")+1 FROM "EMP" "EMP"))

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          7  recursive calls
          1  db block gets
        859  consistent gets
          0  physical reads
          0  redo size
       1094  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Generate statistics
SQL> --
SQL> exec dbms_stats.gather_table_stats('GRIBNAUT', 'EMP2', estimate_percent=>100, cascade=>true)

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Try the query again
SQL> --
SQL> set autotrace on
SQL>
SQL> select *
  2  from emp2
  3  where empno = (select min(empno)+1 from emp);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO UPD
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---
      7370 SMITH      CLERK           7903 17-DEC-80        800                    20 YES


Execution Plan
----------------------------------------------------------
Plan hash value: 1300931333

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |    18 |   738 |    22   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP2      |    18 |   738 |    19   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMPNO_IDX |    18 |       |     3   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE           |           |     1 |    13 |            |          |
|   4 |     TABLE ACCESS FULL       | EMP       |    18 |   234 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"= (SELECT MIN("EMPNO")+1 FROM "EMP" "EMP"))

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
       1094  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> set autotrace off
SQL>

Even with no ‘formal’ statistics Oracle 11.2.0.3 can use the CBO to generate access paths to use the index because of dynamic sampling. Notice that when statistics are gathered the cost estimates change to reflect those more accurate statistics. Knowing that a concatenated index was created we drop the original single-column index and replace it with a concatenated index where the column of interest is not the leading column:


SQL> --
SQL> -- Drop the empno index and replace it
SQL> --
SQL> drop index empno_idx;

Index dropped.

SQL> create index ename_empno_idx on emp2(ename, empno);

Index created.

SQL>
SQL> --
SQL> -- Try the query again
SQL> --
SQL> set autotrace on
SQL>
SQL> select *
  2  from emp2
  3  where empno = (select min(empno)+1 from emp);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO UPD
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---
      7370 SMITH      CLERK           7903 17-DEC-80        800                    20 YES


Execution Plan
----------------------------------------------------------
Plan hash value: 2131058526

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |    18 |   738 |    26   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP2            |    18 |   738 |    23   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | ENAME_EMPNO_IDX |    18 |       |    20   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE           |                 |     1 |    13 |            |          |
|   4 |     TABLE ACCESS FULL       | EMP             |    18 |   234 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"= (SELECT MIN("EMPNO")+1 FROM "EMP" "EMP"))
       filter("EMPNO"= (SELECT MIN("EMPNO")+1 FROM "EMP" "EMP"))

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         52  consistent gets
         32  physical reads
          0  redo size
       1094  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> set autotrace off
SQL>

Yes, we should have truncated the table, created the new index and re-loaded the data but the results, in 11.2.0.3, would be the same. Notice that this release of Oracle provides an INDEX SKIP SCAN access path; 11.1.0.7 doesn’t provide that index access pathway. As such it’s very likely that the column in the WHERE clause is referencing the second or third column in the index key and if that is the case then Oracle 11.1.0.7 will shift to a full table scan. To circumvent that it will be necessary to create another index, a concatenated index where the leading column is the column of interest as indicated by the WHERE clause. If we recreate the index yet again, swapping the columns so that the leading column is the column referenced in the WHERE clause both 11.1.0.7 and 11.2.0.3 will show the same plan:


SQL> -- Drop the empno index and replace it
SQL> --
SQL> drop index ename_empno_idx;

Index dropped.

SQL> create index empno_ename_idx on emp2(empno, ename);

Index created.

SQL>SQL> --
SQL> -- Try the queries again
SQL> --
SQL> set autotrace on
SQL>
SQL> select *
  2  from emp2
  3  where empno = (select min(empno)+1 from emp);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO UPD
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---
      7370 SMITH      CLERK           7903 17-DEC-80        800                    20 YES


Execution Plan
----------------------------------------------------------
Plan hash value: 3006901234

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |    18 |   738 |    24   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP2            |    18 |   738 |    21   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMPNO_ENAME_IDX |    18 |       |     3   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE           |                 |     1 |    13 |            |          |
|   4 |     TABLE ACCESS FULL       | EMP             |    18 |   234 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"= (SELECT MIN("EMPNO")+1 FROM "EMP" "EMP"))

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         20  consistent gets
          2  physical reads
          0  redo size
       1094  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

So, we see that computing statistics on the index in question doesn’t help matters in 11.1.0.7 since, apparently, the problem query is looking for a table column that is not the leading column of that index. And we also see that adding an index, where the leading column is the column of interest, causes that index to be used. It’s good to remember that how the optimizer behaves is version-dependent.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Latest Articles