Re: Problem with Order by of a date

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: Thu, 10 Nov 2005 21:18:37 +0000 (UTC)
Message-ID: <dl0dfd$jb1$1@nntp.init7.net>

On 2005-11-09, ChrisF <chris.freel_at_daibasel.ch> wrote:
> As everyone says, the problem is that your query takes the specified
> number or rows, and then does the SORT on those, but you need it to do
> the sort first.
> Malcolm's method above works, but the disadvantage is that if you have
> 10000 rows in the table it sorts all 10000, which is inefficient if you
> just want one returned.
>
> Another method (using your table name):
>
> SELECT a.lista_campi_tpr, date_import
> FROM CELL_PR cp
> WHERE cp.id_cella = 'PZ81U1'
> AND date_import =
> (SELECT MAX(date_import)
> FROM CELL_PR cp
> WHERE cp.id_cella = 'PZ81U1')
> AND ROWNUM < 2
>
> This passes through the table twice (once to find the MAX(), and a
> second time to find the row it wants), but it should still be faster
> than sorting the whole table unnecessarily. You still need the "ROWNUM
>< 2", in case there are two rows with identical date_import, then it
> returns the first one it finds.

This is not true, at least not in Oracle 10g.

Consider:

create table some_dates (
  dt date,
  txt varchar2(100)
);

begin
  for i in 1 .. 100000 loop
    insert into some_dates values (

      sysdate - dbms_random.value(1,5000),
      dbms_random.string('p', 100));

  end loop;
end;
/

• Setting outotrace on (with statistics only)
• The important thing to watch for is
• consistent gets

set autotrace on statistics;

select *
  from (
    select dt, txt from some_dates
     order by dt desc
  )
  where rownum < 2;

Statistics

---

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

select *
  from (
    select dt, txt from some_dates
     order by dt desc
  )
  where rownum < 2;

Statistics

---

          0  recursive calls
          0  db block gets
       3236  consistent gets
          0  physical reads
          0  redo size
        564  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)
          1  rows processed

select dt, txt
  from some_dates sd
 where dt = (
   select max(dt)
     from some_dates
 )
 and rownum < 2;

Statistics

---

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


--
-- As can be seen, the row_number() and the one with the
-- inner select are both equally efficient, having
-- 1635 consistent gets each.
--
-- The one with the inner select to find the maximum
-- value and then the outer select to find the row
-- (with both selects performing a full table scan)
-- is less efficient: 3236 consistent gets, almost
-- the double amount. Which is not surprising because
-- it scans the table twice instead of once.
--
-- Yet, why is that?

--
-- Explain plan helps:


explain plan for
 select * 
   from (
     select dt, txt from some_dates
      order by dt desc
   )
   where rownum < 2;

select * from table(dbms_xplan.display);


------------------------------------------------------------------------ cut 
| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CP cut 
------------------------------------------------------------------------ cut 
|   0 | SELECT STATEMENT        |            |     1 |    61 |   364   ( cut 
|*  1 |  COUNT STOPKEY          |            |       |       |           cut 
|   2 |   VIEW                  |            |   122K|  7282K|   364   ( cut 
|*  3 |    SORT ORDER BY STOPKEY|            |   122K|  7282K|   364   ( cut 
|   4 |     TABLE ACCESS FULL   | SOME_DATES |   122K|  7282K|   364   ( cut 
------------------------------------------------------------------------ cut 

-- The key here is the 'SORT ORDER BY STOPKEY'

explain plan for
 select dt, txt 
   from some_dates sd
  where dt = (
    select max(dt) 
      from some_dates
  )
  and rownum < 2;

select * from table(dbms_xplan.display);

---------------------------------------------------------------------- cut
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU cut
---------------------------------------------------------------------- cut
|   0 | SELECT STATEMENT     |            |     1 |    61 |   728   (2 cut
|*  1 |  COUNT STOPKEY       |            |       |       |            cut
|*  2 |   TABLE ACCESS FULL  | SOME_DATES |     1 |    61 |   365   (2 cut
|   3 |    SORT AGGREGATE    |            |     1 |     9 |            cut
|   4 |     TABLE ACCESS FULL| SOME_DATES |   122K|  1074K|   363   (2 cut
---------------------------------------------------------------------- cut

-- In fact, here: two TABLE ACCESS FULL

explain plan for
 select dt, txt 
   from (
     select dt, txt,
      row_number() over (order by dt desc) r
     from some_dates
   )
  where r < 2;

select * from table(dbms_xplan.display);

------------------------------------------------------------------------cut
| Id  | Operation                | Name       | Rows  | Bytes |TempSpc| cut
------------------------------------------------------------------------cut
|   0 | SELECT STATEMENT         |            |   122K|  8834K|       | cut
|*  1 |  VIEW                    |            |   122K|  8834K|       | cut
|*  2 |   WINDOW SORT PUSHED RANK|            |   122K|  7282K|    16M| cut
|   3 |    TABLE ACCESS FULL     | SOME_DATES |   122K|  7282K|       | cut
------------------------------------------------------------------------cut

-- Only one full table scan as well.


hth
Rene
-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/

Received on Thu Nov 10 2005 - 15:18:37 CST