Monthly Archives: October 2010

I have been seeing a lot on stopkey optimization lately. First, at the Michigan OakTable Symposium, I attended Randolf Geist’s presentation on FIRST_ROWS_N. It was a very good presentation with great examples that got me wanting to test some other cases to see the results. Second, Jonathan Lewis posted about a stopkey limitation with updates.

I finally got some time to look at a couple more cases to further test the behavior. I will post the results over the next few posts.

First, I wanted to see if changing the update to be an update based on a select statement. My tests are using the same setup as Jonathan did in his post.

explain plan for
update
(
  select  
    small_vc  
  from  
    t1  
  where  
    id > 100  
)
set small_vc = upper(small_vc)
where rownum <= 200;  

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |      |   200 |  3000 |    48   (0)| 00:00:01 |
|   1 |  UPDATE             | T1   |       |       |            |          |
|*  2 |   COUNT STOPKEY     |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| T1   |  9901 |   145K|    48   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<=200)
   3 - filter("ID">100)

Next, I wanted to see if I could get around the update limitation by trying to get the stopkey optimization to work in a subquery returning which rowids I want to update:

explain plan for
update t1 set  
    small_vc = upper(small_vc)  
where  
    rowid in (
      select rowid
      from t1
      where  
        id > 100  
        and rownum <= 200
    );

select * from table(dbms_xplan.display);

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |          |     1 |    35 |     9  (12)| 00:00:01 |
|   1 |  UPDATE                      | T1       |       |       |            |          |
|   2 |   NESTED LOOPS               |          |     1 |    35 |     9  (12)| 00:00:01 |
|   3 |    VIEW                      | VW_NSO_1 |   200 |  2400 |     7   (0)| 00:00:01 |
|   4 |     SORT UNIQUE              |          |     1 |  3200 |            |          |
|*  5 |      COUNT STOPKEY           |          |       |       |            |          |
|*  6 |       INDEX FAST FULL SCAN   | T1_I1    |  9901 |   154K|     7   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY USER ROWID| T1       |     1 |    23 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter(ROWNUM<=200)
   6 - filter("ID">100)

As you can see, these tests fall under the same limitations of the other updates. The second example did trade the full table scan for an index fast full scan, so it could be a small win, but it still wasn't the index range scan I was hoping for.

In the next post I will do some testing with paging examples (i.e. get rows 101-200, 201-300, etc.)