Re: Display last records in a table

In article <ce88htkpn6e0h3iv1jvb3arekbgts2hn3i_at_4ax.com>, Michael says...
>
>Good god! You're right. Sorry - there is no way to do that in
>Oracle. Too bad.... Try another DBMS.
>

don't care what RDBMS you try it on, the results will be the same. We all use heaps.

try this in a hash cluster -- bamm -- even worse results. try this on an IOT -- same as a hash cluster. try this on a cluster -- different from a hash cluster but still pretty unusable.
try this on a partitioned table -- won't work. (and so on)

>Or...
>
>I was making the assumption that someone just wanted to look at some
>recently added records, they didn't have a timestamp, or a sequence
>numbered field available.

but the only answer to that is "you can't do that" unless you do have a timestamp or a sequence numbered field.

>They didn't want to do a dump of the whole
>table to look at the later records (In real life, I've actually seen
>this done - believe it or not!)

that is NOT a reason to propagate buggy code. I've seen people not backup their rollback segments thinking "hey Oracle won't need these". Bad advice is just that.

>This was just a quick way of looking
>at the bottom of a dump.

no, no it is not. It is a rather slow way (on a large table, sort by rownum descending is not "fast") to get a random set of rows.

>Since they chose the number 10, and not
>records added since a certain date, it sounded a little arbitrary.
>

i don't understand that comment.....

>I know that Oracle does not guarantee where it will store a given
>record.

no database does...

>I like the timestamp field too. But, I often find myself
>trying to offer some solution in real life situations rather than tell
>people 'You can't do that...' It's a little like telling people who
>ask you for directions that they should use the map they should have
>packed. If they don't have the map, they obviously can't use it, they
>still need to get there somehow.
>

bad analogy. A better one in this case is:

They asked for directions to the Super Market. You didn't know how to get there so you told them how to get to the bank.

I told them, unless you go get a map, you will remain lost forever. Get the map.

If you knew this didn't really work in the first, or had serious limitations, it would have been nice to NOTE that when posting. Not doing so opens it up to criticism. Don't blame the messenger in this case -- when I see obvious inaccuracies, I will follow up.

The only answer to "how can I display the last 10 inserted records" is -- unless you have a timestamp of some sort -- you cannot. It is not possible.

>Happy Tuesday.
>

you too.

>On Sat, 26 May 2001 20:10:56 -0400, Thomas Kyte <tkyte_at_us.oracle.com>
>wrote:
>
>>A copy of this was sent to Michael Dodd <doddme_at_mindspring.com>
>>(if that email address didn't require changing)
>>On Sat, 26 May 2001 09:17:51 -0500, you wrote:
>>
>>>SQL> create table test_dates (my_int number, my_date date);
>>>
>>>Table created.
>>>
>>>SQL> declare i number;
>>> 2 begin
>>> 3 for i in 1 .. 1000 loop
>>> 4 insert into test_dates values(i,sysdate+i);
>>> 5 end loop
>>> 6 ;
>>> 7 end;
>>> 8 /
>>>
>>>PL/SQL procedure successfully completed.
>>>
>>>SQL> select count(*) from test_dates;
>>>
>>> COUNT(*)
>>>----------
>>> 1000
>>>
>>>SQL> select max(my_date) from test_dates;
>>>
>>>MAX(MY_DA
>>>---------
>>>20-FEB-04
>>>
>>>SQL> select my_date from (select my_date,rownum from test_dates order
>>>by rownum desc) where rownum <
>>> 10;
>>>
>>>MY_DATE
>>>---------
>>>20-FEB-04
>>>19-FEB-04
>>>18-FEB-04
>>>17-FEB-04
>>>16-FEB-04
>>>15-FEB-04
>>>14-FEB-04
>>>13-FEB-04
>>>12-FEB-04
>>>
>>>9 rows selected.
>>>
>>>SQL>
>>>
>>
>>that'll never work in real life. In my database, with an 8k block size:
>>
>>
>>ops$tkyte_at_ORA8I.WORLD> create table t ( x int, a char(2000) default 'x', b
>>char(2000) default 'x', c char(2000) default 'x' );
>>
>>Table created.
>>
>>ops$tkyte_at_ORA8I.WORLD>
>>ops$tkyte_at_ORA8I.WORLD> begin
>> 2 for i in 1 .. 20 loop
>> 3 insert into t ( x ) values ( i );
>> 4 end loop;
>> 5 commit;
>> 6 end;
>> 7 /
>>
>>PL/SQL procedure successfully completed.
>>
>>ops$tkyte_at_ORA8I.WORLD> delete from t where x <= 10;
>>
>>10 rows deleted.
>>
>>ops$tkyte_at_ORA8I.WORLD> commit;
>>
>>Commit complete.
>>
>>ops$tkyte_at_ORA8I.WORLD>
>>ops$tkyte_at_ORA8I.WORLD> begin
>> 2 for i in 1 .. 10 loop
>> 3 insert into t ( x ) values ( 100+i );
>> 4 end loop;
>> 5 commit;
>> 6 end;
>> 7 /
>>
>>PL/SQL procedure successfully completed.
>>
>>ops$tkyte_at_ORA8I.WORLD>
>>ops$tkyte_at_ORA8I.WORLD> select x from (select x,rownum from t order by rownum
>>desc)
>> 2 where rownum < 10;
>>
>> X
>>----------
>> 20
>> 19
>> 18
>> 17
>> 16
>> 15
>> 14
>> 13
>> 12
>>
>>9 rows selected.
>>
>>That is definitely not the last inserted set of rows.
>>
>>
>>Unless a table has some timestamp that can be used to order by data sort of by
>>order of insertion -- you cannot get the "last 10" -- there is no such concept
>>in a relational database (there is no first 10, last 10, middle 10 -- there is
>>just a heap of data).
>>
>>If you have a timestamp or a sequence on that table then:
>>
>>select *
>> from ( select * from T order by TIMESTAMP DESC )
>> where rownum <= 10
>>/
>>
>>will get the 10 records with the highest timestamps (in Oracle8i and up -- order
>>by in a subquery is new with 8i).
>>
>>
>>
>>>On Fri, 25 May 2001 17:04:59 +0200, "Thomas Stuefer"
>>><stuefer_at_halli-data.at> wrote:
>>>
>>>>How can i display the last records in a table with an select-statement ?
>>>>
>>>>For example:
>>>>
>>>>I have an table with many thousand of records and only want to display the
>>>>last 10 added records.
>>>>
>>>>Many thanks for your help !
>>>>
>>>>Tom
>>>>
>>>>
>

Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/ Oracle Magazine: http://www.oracle.com/oramag Opinions are mine and do not necessarily reflect those of Oracle Corp Received on Tue May 29 2001 - 20:04:33 CDT