Re: Can I use an index to get the last n records?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 16 Jul 2007 17:02:02 +0100
Message-ID: <BvqdnWtxm4TqCwbbnZ2dnUVZ8t2snZ2d@bt.com>

"Timasmith" <timasmith_at_hotmail.com> wrote in message news:1184598034.676069.102000_at_q75g2000hsh.googlegroups.com...
> If I have a table with 100,000,000 rows
>
> sales(sales_id, sales_person_id, sales_date_time, ...)
>
> create index sales_date_index on sales (sales_person_id,
> sales_date_time);
>
> I want to execute something like this (which doesnt work at all)
>
> select * from sales
> where sales_person_id = :sales_person_param
> and rownum < 10
> order by sales_date_time desc
>
> I want it to use my sales_date_index to read just 10 indexed records -
> NOT read all records for the sales_person_id, sort in memory and then
> return me the result set.
>
> Anyway in general I can force traversal of the records using an index?
>
> thanks
>

The syntax ought to be a little different. In theory your query may "accidentally" get the right answer, and do it very efficiently. In practice you need to ask for:

    all the rows sorted in the right order,     but stop after 10.

The standard method is to use an inline view. Here's some sample data showing the required execution plan.

drop table t1;

create table t1 as
select
 mod(rownum, 400) sales_person_id,
 trunc(sysdate,'YYYY') + dbms_random.value(1,365) sales_date_time,  rownum id,
 rpad('x',20) small_bit,
 rpad('x',200) big_bit
from
 all_objects
where
 rownum < 10000
;

create index t1_i1 on t1(sales_person_id, sales_date_time);

begin
 dbms_stats.gather_table_stats(
  user,
  'T1',
  cascade => true,
  method_opt => 'for all columns size 1',   estimate_percent => null
 );
end;
/

set autotrace traceonly

select *
from (
 select
  id, small_bit, to_char(sales_date_time,'dd-mon hh24:mi:ss')  from
  t1
 where
  sales_person_id = 99
 order by
  sales_date_time desc
 )
where
 rownum <= 20
;

set autotrace off

10 rows selected.

Execution Plan

---

Plan hash value: 2056339116

---

| Id  | Operation                      | Name  | Rows  | Bytes | Cost 
(%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |       |    10 |   340 |    12 
(0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                 |       |       |       | 
|          |
|   2 |   VIEW                         |       |    10 |   340 |    12 

(0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | T1 | 25 | 925 | 12 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN DESCENDING| T1_I1 | 10 | | 2 (0)| 00:00:01 |

---

Predicate Information (identified by operation id):

---

   1 - filter(ROWNUM<=10)
   4 - access("SALES_PERSON_ID"=99)
       filter("SALES_PERSON_ID"=99)

Statistics

---

          1  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
        851  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

Despite appearances to the contrary, the 'rownum <= 10' reported as a filter predicate in line 1 does gets applied inside the view. If you check the "consistent gets" you can see that the work done is consistent with using the index to fetch 10 rows and stopping - rather than getting the full 25 that exist.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Received on Mon Jul 16 2007 - 11:02:02 CDT