Skip to Main Content
  • Questions

  • Use of analyic function to speed up query.

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Peter.

Asked: August 12, 2004 - 11:56 am UTC

Last updated: August 13, 2004 - 3:36 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hello Tom
I have a small question. I am trying to make query statement return the last know insert, faster than I currently do, using the analytic function

I have a table:
here is an example

create table t ( a int, b date, c number );

Values are inserted from a program every day.

insert into t values ( 1, to_date( '01-jan-2004'), 1 );
insert into t values ( 2, to_date( '02-jan-2004'), 2 );
insert into t values ( 1, to_date( '03-jan-2004'), 3 );
insert into t values ( 2, to_date( '04-jan-2004'), 5 );
insert into t values ( 1, to_date( '05-jan-2004'), 4 );
insert into t values ( 2, to_date( '06-jan-2004'), 3 );
insert into t values ( 1, to_date( '07-jan-2004'), 2 );
insert into t values ( 2, to_date( '08-jan-2004'), 6 );
insert into t values ( 1, to_date( '09-jan-2004'), 7 );
insert into t values ( 2, to_date( '10-jan-2004'), 9 );

to get the last entry inserted I would normaly do
select * from
(select a,b,c from t order by b desc)
where rownum <2

As the table grows the query takes longer and longer.

My question is whether I can make the query faster by using analytic functions? My current query works but does not seem very efficient.

I have tried LAST_VALUE as follows but I always get more than what I expect.

select a,b,last_value(c)
over (partition by a order by b)
from t
where a=2


What would be the equivalent query using analytic functions?
Thanks for any advice.


and Tom said...

that query should perform as fast for a 1 row table as it does for a 1,000,000,000,000,000,000 row table.


first_rows hint might be called for and an index on B would be the thing that makes this just as fast for 1 row as for <as many as you can find> rows.

B should be NOT NULL, B should be indexed, then this will be super fast:


ops$tkyte@ORA9IR2> create table t ( a int, b date NOT NULL, c number );

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t_idx on t(b);

Index created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t select rownum, sysdate+rownum, user_id from all_users;

46 rows created.

ops$tkyte@ORA9IR2> analyze table t compute statistics for table;

Table analyzed.


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace on
ops$tkyte@ORA9IR2> select * from
2 ( select /*+ FIRST_ROWS */ a, b, c from t order by b desc )
3 where rownum < 2;

A B C
---------- --------- ----------
46 27-SEP-04 195


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=5 Card=1 Bytes=35)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=5 Card=46 Bytes=1610)
3 2 SORT (ORDER BY STOPKEY) (Cost=5 Card=46 Bytes=782)
4 3 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=46 Bytes=782)




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

ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> truncate table t;

Table truncated.

ops$tkyte@ORA9IR2> insert into t select rownum, sysdate+rownum, object_id from all_objects;

30990 rows created.

ops$tkyte@ORA9IR2> analyze table t compute statistics for table;

Table analyzed.

ops$tkyte@ORA9IR2> set autotrace on
ops$tkyte@ORA9IR2> select * from
2 ( select /*+ FIRST_ROWS */ a, b, c from t order by b desc )
3 where rownum < 2;

A B C
---------- --------- ----------
30990 17-JUN-89 129013


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=826 Card=1 Bytes=35)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=826 Card=30990 Bytes=1084650)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=30990 Bytes=619800)
4 3 INDEX (FULL SCAN DESCENDING) OF 'T_IDX' (NON-UNIQUE) (Cost=26 Card=30990)




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

ops$tkyte@ORA9IR2> set autotrace off


in this case, analytics can only be used to slow down the query! rownum is by far the most efficient solution here.

Even if you cannot index, this is true. there is special top-n processing that takes place with rownum that cannot with analytics.

rownum is right, analytics are wrong, you are probably either missing the NOT NULL on B (meaning that the index cannot be used) or the index on B


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Helena Marková, August 13, 2004 - 3:36 am UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.