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

Giganews Newsgroups
Subject: Re: Can I use an index to get the last n records?
Posted by:  Jonathan Lewis (jonath…@jlcomp.demon.co.uk)
Date: Mon, 16 Jul 2007

"Timasmith" <timasmi…@hotmail.com> wrote in message
news:1184598034.676069.1020…@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

Replies

None

In response to

Can I use an index to get the last n records? posted by Timasmith on Mon, 16 Jul 2007