3

I have been working on obtaining a paginated result from a table in my database (11g). While I have a query that does work (ie, the result is correct) it does not perform as well as I'd like to and I am trying to improve its efficiency (est. 60 calls per second on that query only).

So first of all, I read What is an Effective Way to Count the Number of Pages in Oracle?, and the article pointed too, but unfortunately it does not discuss the execution plan of the queries presented at all (I'll get to see them come Monday).

Here is the query I had come up with (the table is partitioned on part_code which is a date range):

select <all-columns> from (
    select rownum as rnum, <all-columns> from (
        select /*+index (my_table index)*/ <all-columns> from my_table
        where part_code in (?, ?, ?)
          and date between ? and ?
          and type in (?, ?, ?)
          and func_col1 = ?
          /*potentially: and func_col2 = ? and func_col3 = ? ... */
        order by date, type, id
     )
) where rnum between M and N; /* N-M ~= 30 */

Note: most of the queries will be carried out with a single func_xxx filter, and I think M will be small... but have no guarantee and in theory M can be up to 9999.

Note: ~72 partitions in total, but only ~39 active at most, ~300,000 different values of func_col1 (but with some having ~50,000 rows and some having 1 row), ~10 values of type, id is unique (generated through a sequence).

It does work, but there is a nasty surprise in the execution plan: the 2nd query (with rownum as rnum) is completely executed, fetching up to ~50,000 rows from the DB, before the pagination kicks in. Fetching ~50,000 rows from the DB to return some ~30 of them seems particularly inefficient.

In the execution-plan this shows up as:

View
\_ Filter (rnum)
   \_ View <= here comes the trouble
      \_ Sort
         \_ ...

I can create an index over the table if necessary, and I can transform the existing partitioned index (part_code, func_col1, date, type, id). The index has exactly the order required by my order by clause; but it does not seem to be sufficient (and removing the order by clause does not get me any closer anyway).

Is there a way to prevent the "materialization" of the view and have Oracle build it on the fly as the outer query needs more data (ie, moving to lazy evaluation of the inner queries) ?

4
  • " The index has exactly the order required by my order by clause" - in fact it doesn't. You'd need part_code and func_coll in the order by for that. Why do you have the hint in there - is the index not taken otherwise? What sort of stats do you have on the relevant objects?
    – Mat
    Mar 9, 2013 at 13:33
  • @Mat: corporate policy, stats collection is off and all queries must be hinted appropriately. I can add part_code (redundant with date) and func_col1 (once again not functionally relevant because of the =) in the order by clause if it helps. Mar 9, 2013 at 13:45
  • @Mat: Don't I wish :( Unfortunately there is a reason for it: because we run systems that should not go down (and have penalties to pay for when they do), we settle for consistent and predictable over fastest available. I think stats collection are run manually from time to time (esp. on new tables after the volume stabilizes), but we are specifically asked not to rely on it. Mar 9, 2013 at 14:32
  • 1
    Here's a good read if you don't have it already New optimizer features in 11g (PDF from Oracle.com). Especially the plan management stuff. (You might find dynamic sampling scary in your situation - I guess you've already turned that off :-) )
    – Mat
    Mar 9, 2013 at 14:58

1 Answer 1

5

I believe you should be aiming for a plan that avoid any actual sort operation, and "stops short" as soon as possible.

To avoid the sort (and "materializing" the inner view), your sort order must match exactly the index columns, or your where clauses must be strict equals only on all the leading columns. Otherwise there will be a need to sort subsets, and that will require evaluating the whole inner view.

Here's an example with a hypothetical foo table:

create table foo (a number, b number, c varchar(100));
-- fill with dummy data
exec dbms_stats.gather_table_stats(ownname => user, tabname => 'FOO');
create index foo_ix on foo(a, b, c);

Closest simple equivalent to your select:

select * from (
  select rownum r, i.* from (
    select a, b, c
    from foo
    where a in (3,4) and b = 3
    order by c
  )  i
) where r between 5 and 10;

The explain plan isn't good:

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |   163 | 14833 |     5  (20)| 00:00:01 |
|*  1 |  VIEW                 |        |   163 | 14833 |     5  (20)| 00:00:01 |
|   2 |   COUNT               |        |       |       |            |          |
|   3 |    VIEW               |        |   163 | 12714 |     5  (20)| 00:00:01 |
|   4 |     SORT ORDER BY     |        |   163 |  9291 |     5  (20)| 00:00:01 |
|   5 |      INLIST ITERATOR  |        |       |       |            |          |
|*  6 |       INDEX RANGE SCAN| FOO_IX |   163 |  9291 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - filter("R"<=10 AND "R">=5)
   6 - access(("A"=3 OR "A"=4) AND "B"=3)

The count is too late, and isn't actually (I think) "stopping things short" in this case.

Add the index columns to the order (might not meet your requirements, sorry):

select * from (
  select rownum r, i.* from (
    select a, b, c
    from foo
    where a in (3,4) and b = 3
    order by a, b, c
  )  i
) where r between 5 and 10;
-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |   163 | 14833 |     4   (0)| 00:00:01 |
|*  1 |  VIEW                |        |   163 | 14833 |     4   (0)| 00:00:01 |
|   2 |   COUNT              |        |       |       |            |          |
|   3 |    VIEW              |        |   163 | 12714 |     4   (0)| 00:00:01 |
|   4 |     INLIST ITERATOR  |        |       |       |            |          |
|*  5 |      INDEX RANGE SCAN| FOO_IX |   163 |  9291 |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter("R"<=10 AND "R">=5)
   5 - access(("A"=3 OR "A"=4) AND "B"=3)

At least we got rid of the sort. Now let's try and "stop short":

select * from (
  select rownum r, i.* from (
    select a, b, c
    from foo
    where a in (3,4) and b = 3
    order by a, b, c
  )  i where rownum < 10
) where r > 5;
-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |     9 |   819 |     4   (0)| 00:00:01 |
|*  1 |  VIEW                |        |     9 |   819 |     4   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY      |        |       |       |            |          |
|   3 |    VIEW              |        |     9 |   702 |     4   (0)| 00:00:01 |
|   4 |     INLIST ITERATOR  |        |       |       |            |          |
|*  5 |      INDEX RANGE SCAN| FOO_IX |     9 |   513 |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter("R">5)
   2 - filter(ROWNUM<10)
   5 - access(("A"=3 OR "A"=4) AND "B"=3)

This might be enough for your query: notice the count stopkey (rownum < magic, doesn't kick in with between in what I've seen) and the "rows" column - the inner scan doesn't have to bother about fetching more rows after it's found N.

With the above, you'll still be reading N rows from the table.

You could limit that if all the search criteria are indexed: do the above filtering but retrieving only the ROWID from each match rather than all the columns, and then access the table by ROWID.

select * from foo where rowid in (
 select rid from (
    select rownum r, i.* from (
      select rowid rid
      from foo
      where a in (3,4) and b = 3
      order by a, b, c
    )  i where rownum < 10
  ) where r > 5
);
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    78 |     6  (17)| 00:00:01 |
|   1 |  NESTED LOOPS               |          |     1 |    78 |     6  (17)| 00:00:01 |
|   2 |   VIEW                      | VW_NSO_1 |     9 |   108 |     4   (0)| 00:00:01 |
|   3 |    HASH UNIQUE              |          |     1 |   225 |            |          |
|*  4 |     VIEW                    |          |     9 |   225 |     4   (0)| 00:00:01 |
|*  5 |      COUNT STOPKEY          |          |       |       |            |          |
|   6 |       VIEW                  |          |     9 |   108 |     4   (0)| 00:00:01 |
|   7 |        INLIST ITERATOR      |          |       |       |            |          |
|*  8 |         INDEX RANGE SCAN    | FOO_IX   |     9 |   594 |     4   (0)| 00:00:01 |
|   9 |   TABLE ACCESS BY USER ROWID| FOO      |     1 |    66 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - filter("R">5)
   5 - filter(ROWNUM<10)
   8 - access(("A"=3 OR "A"=4) AND "B"=3)

This does not work if any of the search fields are not in the index. And make sure you trace this with real data & usual search criteria to see if it materially makes a difference - it might actually be worse especially for low values of M (which is probably the case you want to be the fastest) or high values of N-M.

6
  • Interesting. I can definitely add to the order by clause, as I said functionally it's redundant but I had not realized that Oracle could not understand it on its own. If I manage to get the STOPKEY it's also definitely worth it. However, with M = 4000 and N = 4030, it seems to still require reading a lot of rows (potentially all in-index though), right ? Mar 9, 2013 at 15:12
  • You have to, at a minimum, get the first N rows from the index, there's no avoiding that - you can't "jump into" the index at "position X". I'm wondering if you could do a rowid trick to only read N-M rows from the actual table though... I'll give it a try. Oracle can't understand something that "functionally redundant" if you don't tell it. The order of the rows with and without the leading keys in the index is not the same if you have an in on one of the filters.
    – Mat
    Mar 9, 2013 at 15:17
  • Hum... thinking about it the last version might not return rows in the correct order within a page. Might need to extract r somehow and do an order by at the outer level.
    – Mat
    Mar 9, 2013 at 16:50
  • Thanks for the latest proposition. I do plan on tracing all, we've got staging databases refreshed weekly with the state of their production database counterpart specifically so that we can test performance. Regarding the N-M difference, it is in the worst case capped at 250 for now; and we do plan on keeping much lower (~30) in most cases. Mar 9, 2013 at 16:57
  • I managed to test your proposal, and indeed aligning the order by straigt on the index gets rid of the SORT step. Unfortunately, there is still a VIEW step right before the COUNT (STOPKEY) step... and therefore it's really costly. No cookie :x Mar 11, 2013 at 8:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.