Display last records in a table
Thomas Stuefer
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
Sybrand Bakker
"Thomas Stuefer" <stu...@halli-data.at> wrote in message
news:5luP6.5$NU2.1...@news.salzburg-online.at...
If you don't have a timestamp in your record, this is not possible. Records
are inserted physically at random, there's no guarantee at all they will be
at the physical end of the table.
Hth,
Sybrand Bakker, Oracle DBA
Michael Dodd
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>
Jørn Hansen
You say, it is not possible to find the last rows inserted into a table, as
the rows are inserted at random. And of course, you are right. But there are
ways to get a sense of it...
- If the table has only been used for insertions, the rows should be
inserted in an ordered fashion and it should be possible to find the last
rows inserted simply by doing a 'SELECT * ...' and wait for the last rows
fetched.
- By looking for the SCN of the datablock, we should be able to find the
block where the last commit was performed.
- If the database is running in archive mode. One could use logminer to find
the last insert-statements performed against the table.
/Jørn
"Sybrand Bakker" <pos...@sybrandb.demon.nl> wrote in message
news:tgt1q3c...@beta-news.demon.nl...
Jim Kennedy
information) and to using logminer, but no to number 1. If you do not
specify an order by in your select clause then you cannot guarrentee what
order the data will come back in. The optimizer may in fact change how it
gets the data. Also the "last rows" in a table may not be the last inserted
rows in the table. By "last rows" I mean the ones with the highest rowid or
largest block number in the table. Case in point. I insert a 1,000 rows in
a table, commit, delete 10, commit, insert 20 rows. The last 20 rows could
very well be scattered amongst where the 10 were that I deleted. So the
"last rows" could be anything.
Also if it is important to know what the last rows were that were inserted
then you need to make that an attribute of the row. e.g. a timestamp or
sequence number.
Jim
"Jørn Hansen" <j...@stibo.dk> wrote in message
news:99091318...@radon.stibo.dk...
Thomas Kyte
(if that email address didn't require changing)
that'll never work in real life. In my database, with an 8k block size:
ops$tk...@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$tk...@ORA8I.WORLD>
ops$tk...@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$tk...@ORA8I.WORLD> delete from t where x <= 10;
10 rows deleted.
ops$tk...@ORA8I.WORLD> commit;
Commit complete.
ops$tk...@ORA8I.WORLD>
ops$tk...@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$tk...@ORA8I.WORLD>
ops$tk...@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"
><stu...@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 (tk...@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
fumi
"Jřrn Hansen" <j...@stibo.dk> źśźgŠóślĽó news:99091318...@radon.stibo.dk...
> Dear Sybrand
>
>
> You say, it is not possible to find the last rows inserted into a table, as
> the rows are inserted at random. And of course, you are right. But there are
> ways to get a sense of it...
>
> - If the table has only been used for insertions, the rows should be
> inserted in an ordered fashion and it should be possible to find the last
> rows inserted simply by doing a 'SELECT * ...' and wait for the last rows
> fetched.
No, even though there are only insertions occur on the table.
There are many counterexamples:
1. The DBA changes the PCTFREE storage parameter.
2. The table is a clustered table.
3. The table is an index-ogranized table.
4. The table is a partitioned table.
5. Some one uses the append, or parallel insert statement.
6. The table has multiple free lists.
You may say that you don't use or know what are listed above,
but, in a multi-user environment, the following case must occur:
7. Multiple users insert data into a block,
another user inserts data into the next available block,
when they commit, the transaction entries for each transaction
in the previous block are freed,
so the previous block may becomes available for insertion later.
8. Some one inserts data, the other one inserts data and commits,
the pervious user rollbacks, then the space occpuied by the previous user
may become available for insertion later.
So, without a timestamp column in every record,
it's impossible to guarantee the order of insertion.
> - By looking for the SCN of the datablock, we should be able to find the
> block where the last commit was performed.
> - If the database is running in archive mode. One could use logminer to find
> the last insert-statements performed against the table.
The SCN is irrational here.
A SCN is correspondent to a commit, not to a row.
Michael Dodd
Oracle. Too bad.... Try another DBMS.
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. 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!) This was just a quick way of looking
at the bottom of a dump. Since they chose the number 10, and not
records added since a certain date, it sounded a little arbitrary.
I know that Oracle does not guarantee where it will store a given
record. 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.
Happy Tuesday.
On Sat, 26 May 2001 20:10:56 -0400, Thomas Kyte <tk...@us.oracle.com>
wrote:
Michael Dodd
that dumping isn't a guarantee of order, that's what is funny about
it, people do the dump THINKING this is the best way to view the last
records. There is no good way to do this - live with it or without
it. You can't fool mother nature or Larry Ellison.
Thomas Kyte
>
>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.
Thomas Kyte (tk...@us.oracle.com) Oracle Service Industries
Niall Litchfield
news:e0a8htgisn8gi0gpg...@4ax.com...
> BTW: Before anyone jumps on my statement about the table dump- I know
> that dumping isn't a guarantee of order, that's what is funny about
> it, people do the dump THINKING this is the best way to view the last
> records. There is no good way to do this - live with it or without
> it. You can't fool mother nature or Larry Ellison.
But there is a good way to do this. Get the design right in the first place.
If it is a design requirement to be able to view the last n records added a
sequence number or timestamp is the right way to do it.
An alternative (though a nasty one in terms of resources) is to use some
sort of auditing scheme.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
Jørn Hansen
news:9etqvj$hue$1...@news.seed.net.tw...
> > - By looking for the SCN of the datablock, we should be able to find the
> > block where the last commit was performed.
> > - If the database is running in archive mode. One could use logminer to
find
> > the last insert-statements performed against the table.
>
> The SCN is irrational here.
> A SCN is correspondent to a commit, not to a row.
>
I don't follow you? The SCN is stored with the datablock.
Michael Dodd
I agree. I believe too many projects are rushed to completion before
a design is finished, let alone a design review. A logical model,
physical model. This is not something new, it isn't something
that's going away soon. Business does not care about requirement
gathering, planning, designing, testing, they just want to know what
the bottom line is, even if they know the answer is hog-wash- they
still want that number. I've seen it time and time again.
I think we all agree that the right thing to do is either a sequence
number or a timestamp, I like the timestamp because it gives you a
little more information for not a lot more space.
I think there is an academic discussion going on here, when I felt the
original discussion was geared toward grabbing some (10) records from
the top of the pile, without too much concern if they were the
absolute last 10 records. It's easy to pull from the first few
entered with rownum < 11. (yeah, theoretically the records retrieved
may not be the absolute first entered).
If your users requirement is for pulling the last 10 entered records,
then you have a real problem on your hands, it can't be done, I can
see log miner perhaps, but this is not something you want to do on any
regular basis, not something you'd want to program. Yell at the
designers, yell at the management that rushed it to completion. But
if someone just wants to check under the hood and see what some
recently entered records look like, you can do what I suggest.