Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement
Steve
asking properly, they are not getting the answers they are actually
seeking.
MySQL has a very nice LIMIT clause. You can pass 2 arguments to the
LIMIT clause.
--> LIMIT <beginning offset row>, <number of rows to return>
So for example, in the following SQL statement,
--> SELECT username FROM users_table ORDER BY username LIMIT 0,10
the usernames are sorted and the top 10 are returned. Now if you want
the next 10 on the sorted list, use
--> SELECT username FROM users_table ORDER BY username LIMIT 10,10
then the next 10 on the sorted list, use
--> SELECT username FROM users_table ORDER BY username LIMIT 20,10
Note this is very nice especially when you are creating a web page
where you want to navigate through the sorted usernames (displaying
only 10 users per page). you can hit next page and get the next 10.
notice in this case, the only differences between the webpages, is
just one number in the SQL statment; all the pages get 10 exact
records and all you need to do is display them.
On the 51th page, all you need to use is the following SQL statment:
--> SELECT username FROM users_table ORDER BY username LIMIT 500,10
*************
Now on to Oracle, MSSQL, and Others, which I know very little about.
In MSSQL, people tell me to use the TOP statement. But this only can
retrieve the top of the sorted list - I dont have the ability to
choose the offset. So if I want to display the 51th page, I would have
to use
--> SELECT TOP 500 username FROM users_table ORDER BY username
Which grabs 500 records! not efficient for only wanting 10 records in
a sorted list. Imagine if there was enough records for 10,000 pages.
Then I would have to grab many records for only wanting 10. There must
be a better way. How are you MSSQL people doing it?? What is the most
efficient way possible for this case? I did see somewhere else in a
posting, you can use
--> SELECT username
FROM (SELECT TOP 10 username
FROM (SELECT TOP 500 username
FROM users_table
ORDER BY username) as a1
ORDER BY username desc) as a2
ORDER BY username asc
But damn how efficient is this? I may be wrong (depending how
internally MSSQL is efficient), but it looks like its better to grab
the records from the first MSSQL SQL statement.
How about Oracle? How can you do it in Oracle? I know there is rownum,
but i read somewhere
--> SELECT username FROM users_table where rownum>=500 AND
rownum<=510 ORDER BY username
will not work. So what is the best way in Oracle??
I am not looking for LIMIT equivalents in other Databases, because I
know there isnt. Every database has different SQL syntax and
functionality. But what I am asking is what is the best possible
efficient way for the other databases to come close to match the
MySQL's LIMIT clause. Place yourself into an administrator's shoes and
ask yourself which way would you use? If you know, then share it here
please (Share database name, version, examples if possible, and so
on). It does not have to be limited to only Oracle and MSSQL, it can
be about any other popular database that you know. And if you know any
other databases that do have the LIMIT clause, then share it here
also. Hopefully this will be not only a reference to me, but to anyone
else that seeks to know this question. This scenerio comes up a lot!
Thanks for your time and patience,
Steve
Pablo Sanchez
news:aae1fe62.02091...@posting.google.com:
> [ how to efficiently browse in an RDBMS ]
Hey Steve,
I've been meaning to write a white paper on this very topic. I'll
give you a 50,000' view... :)
The idea is to create a '[pad]' table which houses a list of 'id' (I
use surrogate ID's in all my tables! <G>) The [pad] table is a
recursive table which allows us to have as many rows as needed to
store the ID's that we need:
/* avoid fragmentation by using CHAR's. Map the size of each row
to create even multiples of rows per data page */
[pad]
id NUMERIC(16)
scratch_1 CHAR(...)
scratch_2 CHAR(...)
parent_id NUMERIC(16)
When we wish to browse through a list of rows across many different
tables, we generate a list of surrogate ID's off of the main
'driver' table. Using Oracle's bulk-collects, we can efficient
snarf, say 400 id's -- after all, who wants to browse through more
than 400 rows??! <g>
We create an entry or entries in the [pad] table:
233, 444, 2333
We return to the front-end the first page of rows _and_ the [pad].id
value.
If the front-end wishes to view the next page, they send the stored
procedure the [pad].id and the row ranges to view.
The underlying packages fetch from the [pad] table the list of ID's
and use dynamic SQL (Oracle and Sybase ASE support this) to retrieve
that set of data:
/* Use an ORDER BY to reflect the orignal set of data */
select .... from .... where ... a.id in ('233, 444, 2333')
order by ...
HTH! :)
ps: My plan was to really write this up by Oct 1st:
http://www.hpdbe.com/white_papers/index.html
--
Pablo Sanchez, High-Performance Database Engineering
http://www.hpdbe.com
BP Margolin
--> SELECT TOP 500 username FROM users_table ORDER BY username
My suggestion ... specific to Microsoft SQL Server ...
First, if all you are dealing with is 500 names at a shot, consider sending
them all to the front-end and do paging locally. This is probably going to
give you the best performance, rather than going back and forth for every 10
names.
However, since you are ordering by username, you can also have your
front-end application return to you the **last** of the username's that was
just sent, and then modify your query to:
SELECT TOP 10 username
FROM users_table
WHERE username > @last_username
ORDER BY username
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"Steve" <use...@hotmail.com> wrote in message
news:aae1fe62.02091...@posting.google.com...
Martin Doherty
MySQL select statements. It seems to me that each one is a separate query,
unrelated to the previous or next query, and therefore the result set seen
by the user could be corrupted by the occurrence of inserts and deletes
committed in the users_table while this page-by-page browsing is ongoing.
(Unless the equivalent of SET TRANSACTION READ ONLY is in effect to ensure
that multiple queries have the same read-consistent data view).
The approach I would take in Oracle is to open a cursor with my complete
query (select username from users_table order by username), then fetch
rows from the cursor 10 at a time. The view of the data seen by the user
would be read-consistent with the moment in time that the cursor was
opened (that is, the effect of any inserts or deletes by other sessions
would not be visible to the user browsing the list). My program logic
might include a message to the user on each page to reveal the defining
moment ("Information current as at 9/16/02 17:01 pm") and a timer to close
the cursor if no browsing activity detected within the threshold period.
Since Oracle does not block readers or writers, holding the query open is
not preventing access to the users_table for concurrent reading and
writing.
Regarding ROWNUM, the numeric values of this pseudo-column are assigned to
each result row before sorting, but you can work around this by using an
inline view, viz:
select *
from (select username from users_table order by username) u
where u.rownum between 501 and 510
Note: the ability to include ORDER BY in a subquery was introduced in
Oracle8i (not sure which exact version)
Martin Doherty
LLCeder
In Oracle you would use a statement like :
SELECT username FROM
(SELECT username
FROM users_table
ORDER BY username)
WHERE rownum BETWEEN 1 AND 10
But I think it's safe to say that it will not be as efficient as the LIMIT
clause in MySQL.
Steve <use...@hotmail.com> wrote in message
news:aae1fe62.02091...@posting.google.com...
Marshall Spight
>
> In Oracle you would use a statement like :
>
> SELECT username FROM
> (SELECT username
> FROM users_table
> ORDER BY username)
> WHERE rownum BETWEEN 1 AND 10
>
> But I think it's safe to say that it will not be as efficient as the LIMIT
> clause in MySQL.
Why? I'd imagine they look very much the same internally.
Marshall
Marshall Spight
> asking properly, they are not getting the answers they are actually
> seeking.
>
> MySQL has a very nice LIMIT clause. You can pass 2 arguments to the
> LIMIT clause.
>
> --> LIMIT <beginning offset row>, <number of rows to return>
>
> So for example, in the following SQL statement,
>
> --> SELECT username FROM users_table ORDER BY username LIMIT 0,10
>
> the usernames are sorted and the top 10 are returned. Now if you want
> the next 10 on the sorted list, use
>
> --> SELECT username FROM users_table ORDER BY username LIMIT 10,10
>
> then the next 10 on the sorted list, use
>
> --> SELECT username FROM users_table ORDER BY username LIMIT 20,10
>
>
> In MSSQL, people tell me to use the TOP statement. But ...
> ... I did see somewhere else in a
> posting, you can use
>
> --> SELECT username
> FROM (SELECT TOP 10 username
> FROM (SELECT TOP 500 username
> FROM users_table
> ORDER BY username) as a1
> ORDER BY username desc) as a2
> ORDER BY username asc
>
> But damn how efficient is this? I may be wrong (depending how
> internally MSSQL is efficient), but it looks like its better to grab
> the records from the first MSSQL SQL statement.
What makes you think this is going to be any different from how the
MySQL version works? What you are seeing is pretty much just
two different ways of expressing the same idea. One might be faster
or the other might be, or they might be about the same, but how can
you tell by looking at the SQL statement? You can't.
Marshall
Stephan Herschel
Isn't this a thing that maybe the web-server should take care of? Zope,
for instance, handles it that way: the query is issued once to the
database for the first page, retrieving the result set and caching it
(there is an upper limit of rows retrieved which can be set to a
sufficiently high number). Now for subsequet pages the cached values are
used. Seems to be rather elegant as it's a solution not dependent on a
specific database.
regards,
stephan
Vladimir M. Zakharychev
> query (select username from users_table order by username), then fetch
> rows from the cursor 10 at a time. The view of the data seen by the user
> would be read-consistent with the moment in time that the cursor was
> opened (that is, the effect of any inserts or deletes by other sessions
> would not be visible to the user browsing the list). My program logic
> might include a message to the user on each page to reveal the defining
> moment ("Information current as at 9/16/02 17:01 pm") and a timer to close
> the cursor if no browsing activity detected within the threshold period.
> Since Oracle does not block readers or writers, holding the query open is
> not preventing access to the users_table for concurrent reading and
> writing.
This approach won't work for stateless web applications. Oracle session is
disconnected or reused by other requests as soon as current request is
serviced, it does not maintain state during the logical session established
between client and HTTP server. This is just one of the many challenges
of web applications database developers face. Another one is that there is
no easy way for detecting logical session end - client simply closes browser
window and that's it - server never knows if another request will come from
that client or not. Thus maintaining a pool of dedicated connections to the
database to maintain stateful logical sessions is not reasonable - they are
never closed gracefully and you will end up with a multitude of dead
connections eating up your valuable server resources. For a site with 10
visitors per hour this is not a big problem, but what if you get thousands
of hits per hour? Stateless sessions deal with this problem, but they introduce
their own - you can't use database transaction and concurrency controls
in such applications. You have to deal with lost updates, you have to deal
with incomplete queries (like the one in question), and with a lot of other
things you normally do not see in classic client-server apps where database
session is permanent.
> Regarding ROWNUM, the numeric values of this pseudo-column are assigned to
> each result row before sorting, but you can work around this by using an
> inline view, viz:
>
> select *
> from (select username from users_table order by username) u
> where u.rownum between 501 and 510
Actually, the most effective way to do ranged queries I found to date is
select *
from ( select q.*, rownum rnum
from ( YOUR_QUERY_GOES_HERE ) q
where rownum <= :MAX_ROW )
where rnum >= :MIN_ROW;
since it does not require to rewrite the inner query, just wrap two views
around it.
--
Vladimir Zakharychev (b...@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
Philip Nelson
Here's (from memory) what DB2 offers in this area.
It's called the "FETCH FIRST n ROWS" clause.
So you would code -
SELECT username FROM users_table ORDER BY username FETCH FIRST 10 ROWS
ONLY
On the Linux, Unix and Windows platform you should also code "OPTIMIZE
FOR 10 ROWS" to make the access path efficient. On the OS/390 (z/OS =
mainframe) platform you would not need this clause (the optimizer is
clever enough to work out that if you ask for 10 rows it should optimize
for 10 rows <G>).
There isn't a way to get the next 10 rows as far as I know. But what you
could do (and what we have done in the past) is write the code like -
SELECT username
FROM users_table
WHERE username > ? (host variable)
ORDER BY username
FETCH FIRST 10 ROWS ONLY
OPTIMIZE FOR 10 ROWS
By plugging the 10th value into the host variable in the WHERE clause
(and assuming that this value is unique) you can get the next ten.
I've heard that there were changing afoot to allow cursor processing in
batches of x records but don't know the details.
Phil
Lennart Jonsson
Here is one:
SELECT username FROM (select username, rownumber() over (order by
username) as rownum from users_table) as x where rownum>=500 AND
rownum<=510 ORDER BY username
since I had a table with 300k rows around, I took the liberty to try
it:
time db2 "select systemname from (select systemname, rownumber() over
(order by systemname) as rn from phoenix.actor) as x where rn between
100000 and 100009"
SYSTEMNAME
--------------------------------------------------
0251928
0251929
0251930
0251931
0251932
0251933
0251934
0251935
0251936
0251937
10 record(s) selected.
real 0m2.286s
user 0m0.010s
sys 0m0.020s
/Lennart
Jim Kennedy
when someone leaves? You can't tell so you have cached this set into memory
and you have to wait x number of minutes fot it to time out. In the
meantime you are wasting this ram. Now multiply by the huge number of
connections and you are eating up a lot of memory.
Jim
"Stephan Herschel" <ste...@procryon.at> wrote in message
news:cIBh9.6$864.3...@news.salzburg-online.at...
crackhitler
article:
http://www.tek-tips.com/gviewthread.cfm/lev2/4/lev3/31/pid/333/qid/324945
Why nobody other than the folks at MySQL have implemented a 'limit'
clause (it's in the ANSI standard) is beyond me. I expect Microsoft to
behave like this but not Oracle.
Dieter Nöth
> For those of you looking to simulate a MySQL limit statement, try this
> article:
>
> http://www.tek-tips.com/gviewthread.cfm/lev2/4/lev3/31/pid/333/qid/324945
>
> Why nobody other than the folks at MySQL have implemented a 'limit'
> clause (it's in the ANSI standard) is beyond me.
Which ANSI standard? Definitly not SQL ;-)
There's no LIMIT clause up to the new drafts for SQL:200x, LIMIT's not even
a keyword.
And it will probably never be a part of standard SQL, because you can
achieve the same using ROW_NUMBER.
Dieter
Knut Stolze
> Why nobody other than the folks at MySQL have implemented a 'limit'
> clause (it's in the ANSI standard) is beyond me.
I am a bit curious... Where did you find it in the SQL standard?
--
Knut Stolze
DB2 Spatial Extender
IBM Germany / University of Jena
Jeremy Rickard
> Steve,
>
> Here's (from memory) what DB2 offers in this area.
>
> It's called the "FETCH FIRST n ROWS" clause.
>
> So you would code -
>
> SELECT username FROM users_table ORDER BY username FETCH FIRST 10 ROWS
> ONLY
>
It's also possible to fetch any "window", rather than just the first n
rows, using an OLAP function, e.g.:
SELECT
a.username
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY username) AS row_number,
username
FROM
users_table
) AS a
WHERE
a.row_number BETWEEN 11 AND 20;
Jeremy Rickard
Serge Rielau
>
>Why nobody other than the folks at MySQL have implemented a 'limit'
>clause (it's in the ANSI standard) is beyond me. I expect Microsoft to
>behave like this but not Oracle.
>
Rownumber function (also ANSI)?
Given that this is also in the standard there is no need to waste
resources....
Cheers
Serge
--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Toronto
Try the DB2 UDB V8.1 beta at
http://www-3.ibm.com/software/data/db2/
Christopher Browne
That is very curious. In which ANSI standard do you think LIMIT was
introduced?
I presume you can tell us what section of the standard it is described
in?
Alternatively, maybe 'nobody' has implemented it because it's NOT in
any of the standards documents, and thus ISN'T standardized.
--
(concatenate 'string "cbbrowne" "@acm.org")
http://cbbrowne.com/info/rdbms.html
Some people are only alive because it is illegal to kill.
Peter Gulutzan
crackh...@hotmail.com (crackhitler) wrote in message news:<3783cad7.02091...@posting.google.com>...
> Why nobody other than the folks at MySQL have implemented a 'limit'
> clause (it's in the ANSI standard) is beyond me. I expect Microsoft to
> behave like this but not Oracle.
Everybody has implemented something like that, but it is not in the
ANSI standard. Limiting is possible in the API rather than the
statement itself (look up for instance the SQL_ATTR_MAX_ROWS option
for SQLSetStmtAttr in ODBC documentation). It is a feature which sets
a selection criterion that is not dependent on the row contents, so
some might find it distasteful.
Peter Gulutzan
Co-Author of SQL Performance Tuning
Jesus Christ's Evil Twin
to be two major hurdles.
This statement works just ducky:
SELECT * FROM
(SELECT TOP 10 * FROM
(SELECT TOP 150 * FROM TEMP_ERIC ORDER BY COL1 ASC)
AS T1 ORDER BY COL1 DESC)
AS T2 ORDER BY COL1 ASC
But the problem is fairly obvious. You cannot use input paramaters for
top values, and as such either have to:
a) hardwire in the top value in a procedure, which is absurd
b) write an if statement allowing for every possible combination
possible, the ability to do so being equiv to item (a)
or
c) pass dynamic sql after a string function tweaks things the way you
need them.
All three are poor, with c being the only way I can imagine things to
work, and that is a horrible alternative.
Anyone have any ideas for item (d)?
And please refrain from making item (d) get mysql! or anything of the
sort.
:-)
ben brugman
"BP Margolin" <bpm...@attglobal.net> wrote in message
news:3d867...@news1.prserv.net...
> Steve,
>
> --> SELECT TOP 500 username FROM users_table ORDER BY username
>
> My suggestion ... specific to Microsoft SQL Server ...
>
> First, if all you are dealing with is 500 names at a shot, consider
sending
> them all to the front-end and do paging locally. This is probably going to
> give you the best performance, rather than going back and forth for every
10
> names.
>
> However, since you are ordering by username, you can also have your
> front-end application return to you the **last** of the username's that
was
> just sent, and then modify your query to:
>
> SELECT TOP 10 username
> FROM users_table
> WHERE username > @last_username
> ORDER BY username
This assumes that each username hase only a limited number of
occurances, in this instance a same username can be skipped if
the first appears in position 10. If greater equal is used then if
there are more then 10 same usernames a loop is created.
So some more information has te be added, (PK form example).
Specifically for reusable (general) code this is not very suetable.
ben brugman
Tony Wright
MSSQL. It appears to operate about 3 times faster than populating
temp tables (unless you are doing joins across linked servers, where
temp tables/views appear to be better).
If you use sp_executesql to execute the dynamic queries, it also saves
the execution plan, so if someone happens to execute the same (exact)
query, then it will be able to reuse the plan. We have modified the
query so that we can add a where clause (filter) and dynamically
choose fields to sort by.
The where clause only needs to be applied to the inner most query, and
you will need to have a unique row identifier to do the sort by - this
was easy for us, because we use guid's for IDs which are fast enough
for our purposes, but a timestamp should also be good enough.
It appears that this issue only effects people with large result sets
otherwise I would suggest that this would already be in the standard!
The interesting thing about returning a particular page of a table
where a page has a certain pagesize is that I wouldn't have expected
such a query to alter the query execution plan.
consider this:
SELECT PAGE(2,25) Col1, Col2, Col3
FROM Table
WHERE....
ORDER BY 2
how would the query plan be any different to
SELECT PAGE(7,25) Col1, Col2, Col3
FROM Table
WHERE...
ORDER BY 2
as you would still have to sort the entire table (unless the order the
records were returned was guaranteed without sorting, of course), and
apply the where clause.
But we also need to be able to dynamically change the page and
pagesize. So in MSSQL we might need:
SELECT PAGE(@PageNumber,@PageSize) Col1, Col2, Col3
FROM Table
WHERE...
ORDER BY 2
This code doesn't work; it's just a suggestion...
Surely the standards committee have considered paging?
rad...@hotmail.com (Jesus Christ's Evil Twin) wrote in message news:<2a0f8137.02091...@posting.google.com>...
Serge Rielau
this page() function you propose exposes the physical layout of data on
the disk to the application.
SQL is all about not doing that.
From a more pragmatic angle dealing with pagenumbers and rownumbers can
cause quite some confusion:
1) What if your favorite DBMS (starts to) support rows crossing pages
2) What if your row is stores outside of the DBMS (external table,
nickname, ....)
3) What if your DBMS wants to support online reorg... Yoru rowids will
chanhe in mid flight
4) What if someone deletes a record in the page you're on..
The way to walk through resultsets securly are scrollable cursors.
Tony Wright
darned slow, then I guess it would be viable. If a server side cursor
is used, then a connection to the database is held, which would effect
scalability (too many simultaneous connections, etc, effects
performance on the database server). The security you refer to
indicates a pessimistic locking strategy, because you require
guarantees.
If it is a client cursor, which involves disconnecting from the
database, then the entire contents of the resultset are transfered to
the client for manipulation, which causes a lot of unneccessary
traffic, especially if a paging strategy was good enough. This is also
an optimistic strategy, because someone could change a record in the
database while you were scrolling through the records in your
resultset on the client.
(1) I'm not sure what you mean by "rows across pages". Can you please
explain this.
(2) M$ already handles externally linked tables, which I am also
currently using. Provided the linked table also has a unique row
identifier then I can sort by that and so perform paging (perhaps
having a unique row identifier would also need to be a requirement of
a proposed standard)
(3) Could you please explain a scenario where the db would do an
online reorg on-the-fly. Changing ids doesn't seem normal to me?
Anyway, I would have thought that was something that you would
schedule, etc. We use Guid's to identify each row, and so sorting by
that ID would always guarantee a particular order, even if the
contents of particular pages changes.
(4) The paging solution is really part of an optimistic strategy. If
someone deletes a record that exists on the page, the strategy would
be designed to handle it. For example, we generate all our IDs on the
client, and we know whether an object is new, or an updated object
(retrieved from the database). We also have a timestamp, so that we
can tell if someone else has modified the record, and have a strategy
for handling this. If we hold an object that was retrieved from the
database, and when an attempt is made to write that object back to the
disk, the object is found to no longer exist, then the strategy would
also handle this (ie, why are they updating an object that someone
else deleted? Should this user be given the option to re-insert the
object in this case?).
As for exposing the physical layout of the data, well, I guess to do
paging, there would always be a need to order the data to ensure it
was returned in a consistent fashion. I personally don't see a problem
with this, as my strategy handles this well enough for our
circumstances.
I am not convinced that anyone who implements a paging strategy would
need the so called security that a scrollable cursor might provide.
Most people implementing a paging strategy know that this is an
optimistic stratgey, but they can live with it.
And I stand by my suggestion, that this somehow become part of the
standard, as there appears to be enough of us that want this to
warrant it (as the MySql implementation demonstrates). Should we
really be suggesting that it not be part of a standard, and let M$
implement their own custom solution? That seems to be half the
problem - if you don't make it standard then those who only implement
standards will be playing catchup again, with products that cater for
what the developer wants...
Serge Rielau <sri...@ca.ibm.com> wrote in message news:<3D982CD3...@ca.ibm.com>...
Knut Stolze
> As for exposing the physical layout of the data, well, I guess to do
> paging, there would always be a need to order the data to ensure it
> was returned in a consistent fashion. I personally don't see a problem
> with this, as my strategy handles this well enough for our
> circumstances.
The whole idea behind SQL is that you, as the user do not have to
worry/care/know about the physical layout of the data. SQL is designed in
such a way that you describe _what_ you want, and the DBMS has to worry about
how to give you the results.
Also, I don't quite follow you what you mean with "consistent fashion". Do
you mean the order of the rows in a table? If yes, then remember that a
table contains a _set_ of rows, and sets are not sorted per definition. If
you want to have a certain order of the rows when you retrieve them using a
cursor, then you have to use the ORDER BY clause. Again, this is based on
the underlying concept of SQL of separating external (views), internal
(tables), and physical schema (tablespaces, containers, ...) of the data.
Serge Rielau
GUID provide you with symbolic column value. That is OK. Other DBMS
support similar functions (e.g. generate_unique()). Sequences, and
IDENTITY/SERIAL columns fall into the same category.
SQL has no problem with that concept since it refers to data values of
the rows.
Your earlier post mentioned ROWID and PAGE. That suggested to me that
you refered to locations on a disc structure.. That is not OK in the SQL
world.
Navigating through data is such an ordered fashion has literally been
done since man set foot on the moon. It's called IMS. I don't say it's
bad.. It's just not relational.
Scrollable cursors don't have to be client side. DB2 V8 supports server
side scrollable cursors (I thought SQL Server does as well). They do
support an optimistic strategy. You are correct that this requires
resources, though.
However, with both client and server side scrollable cursors the app can
limit itself to collecting e.g. primary keys (such as the GUID). It is
the DBMSs job (well, and the DBA's maybe) to provide fast access to the
data underneath when the app requires the data for the stored subset of
the IDs.
This can be done with index start stop keys quite efficiently. Both DBMS
and App can provide optimistic locking strategies by using some "last
touched timestamp" in this scenario.
Online Reorg on the fly is very important to improve availability. The
trend is to go towards reorg demons. I.e. your tables would be contantly
reorg-ed in the background. It is therefore not acceptable to have any
app hold a bookmark of some sort or other on a physical location.
Richard
>
> Online Reorg on the fly is very important to improve availability. The
> trend is to go towards reorg demons. I.e. your tables would be contantly
> reorg-ed in the background. It is therefore not acceptable to have any
> app hold a bookmark of some sort or other on a physical location.
What is all this reorg stuff? What does it do? Why do you have to do it?
Do you have to do a lot of it?
Christopher Browne
It is about reorganizing the physical structure of the database.
You have to do it because over time the 'random' additions and
deletions of data that take place lead to it being not optimally
structured, which hurts performance and may even lead to the DBMS
running out of "space" even though there is considerable space not
being used.
--
(reverse (concatenate 'string "ac.notelrac.teneerf@" "454aa"))
http://cbbrowne.com/info/finances.html
"MS apparently now has a team dedicated to tracking problems with
Linux and publicizing them. I guess eventually they'll figure out
this back fires... ;)" -- William Burrow <aa...@DELETE.fan.nb.ca>
Richard
> Richard <stupidd...@postmaster.co.uk> wrote:
>
>>Serge Rielau wrote:
>>
>>>Online Reorg on the fly is very important to improve
>>>availability. The trend is to go towards reorg demons. I.e. your
>>>tables would be contantly reorg-ed in the background. It is
>>>therefore not acceptable to have any app hold a bookmark of some
>>>sort or other on a physical location.
>>
>>What is all this reorg stuff? What does it do? Why do you have to do
>>it? Do you have to do a lot of it?
>
>
> It is about reorganizing the physical structure of the database.
>
> You have to do it because over time the 'random' additions and
> deletions of data that take place lead to it being not optimally
> structured, which hurts performance and may even lead to the DBMS
> running out of "space" even though there is considerable space not
> being used.
So it doesn't re-use space from deletes?
Christopher Browne
/What/ doesn't re-use space from deletes?
It is quite likely that space relinquished by a deletion will not be
generally reusable for all purposes.
Consider a DBMS using 8K pages, where each page contains records for a
particular table. If you delete a record, that leaves space free on
that page. But that space can only be used for records in that same
table and will likely only be used if the records are in a certain
range within the table.
If 500 records get deleted, spread out across 50 pages, there might
not be a single page that gets /totally/ emptied and thereby returned
to the DBMS for general use.
A reorganization would go through those pages, compressing the live
records into a smaller number of pages and returning a set of empty
pages back for general use.
That is, at a rudimentary level, what a reorg looks like.
--
(concatenate 'string "aa454" "@freenet.carleton.ca")
http://cbbrowne.com/info/spiritual.html
"...It is also possible to post imbecilic articles with any browser,
especially when you toppost and omit snippage." -- CBFalconer
<cbfal...@yahoo.com> - seen on comp.lang.c
Sybrand Bakker
wrote:
>If 500 records get deleted, spread out across 50 pages, there might
>not be a single page that gets /totally/ emptied and thereby returned
>to the DBMS for general use.
an Oracle block will end up on the freelist when the usage falls below
PCTUSED. The PCTUSED can be changed, to reduce the wastage.
Regards
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address
Serge Rielau
When an update causes a row to grow (e.g. my storing a longer variable
length value). Then the DBMS will try to shuffle the other rows in the
page to squeeze teh row in. When that fails the row has to move to
another page and it leaves an indirection pointer (like the forwarding
address you leave when you move :-). This indirection hurts performance
(one more IO).
Reorg will remove those inderections and eliminate free space between
rows that occurs if a row gets inserted in the place where a bigger row
got deleted, or where a rows' variable fields shrank.
Reorg may also try to enhance any chosen clustering of the data.
Tony Wright
of records?
Surely the only things that I would need to worry about is that the
order is consistent ie. record 1 is before record 2 is before record 3
etc; I have to be aware that an addition of a record or deletion of a
record may result in records moving between subsets (what I called
"pages", but that may be confusing people, as I am not refering to
physical disk pages). That doesn't really concern me, because it is
unusual to be looking for a record that hasn't been entered - it's all
a matter of process, I suppose.
Whatever happens behind the scenes is not really my concern, surely?
If the system decides to reorganise the records internally, then
provided my (sorted) records are returned the same way as they would
have been returned before the reorg, then there should be no
difference, other than perhaps performance.
Serge Rielau <sri...@ca.ibm.com> wrote in message news:<3D9A4615...@ca.ibm.com>...
Norman Dunbar
When you store rows in a relational database, the order that are
inserted need not be the order that they are returned in. That is the
joy/purpose of RDBMS.
If you absolutley must have the rows returned to you in some order, then
you must have a column in each row upon which you do an 'order by' on
your selects.
Because Oracle will reuse space in a block from a deleted row, you might
find that you get rows 1, 2, 3, 4, 9, 10, 7, 8 if the two rows 5 and 6
were deleted before rows 9 and 10 were insterted if you do not specify
an order clause in your query.
As for what happens behind the scenes, I'm afraid that as a developer
(or dba) then is *is* your concern - this is why so many ports of
applications from SQLServer to Oracle (or the other way) just don't work
- the developers 'know' how to do things in one RDBMS and try to apply
those parctices to the others - result, failure.
Cheers,
Norman.
-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman...@LFS.co.uk
Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------
-----Original Message-----
From: tonyz....@consultant.com (Tony Wright)
[mailto:tonyz....@consultant.com]
Posted At: Wednesday, October 02, 2002 6:48 AM
Posted To: server
Conversation: Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT
Statement
Subject: Re: Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT
Statement
But how would this all effect my desire to retrieve an ordered subset
of records?
Surely the only things that I would need to worry about is that the
order is consistent ie. record 1 is before record 2 is before record 3
etc; <SNIP>
Whatever happens behind the scenes is not really my concern, surely?
<SNIP>
Serge Rielau
some generated GUID that is stored in a column, an IDENTITY, a
timestamp, or some name.
So semantically you could do the following:
SELECT touched, some_key FROM T WHERE some_predicate;
The result you store in your app, or you keep a scrollable cursor
(server or client side, whatever your product supports). A server side
scrollable cursor may have the advantage that it materializes only on
demand, so you don't get all 30000 hits when you just look at the first
10 and discard the rest.
Now your app can use an outer join on the primary key to do fetches
(sensitive or insensitive).
You can also perform updates using the optimistic locking provided by
your "touched" marker.
(compare the marker on update/delete)
Note that a scrollable cursor on your DBMS may provide all this logic
natively.
Daniel Morgan
Are you talking Oracle or some other database?
Pages are not exatly a term with any relevance to Oracle ... perhaps you
meant to respond with respect to SQL Server. And your conclusion that empty
blocks in Oracle are not reused is quite remote from what actually happens.
Daniel Morgan
Serge Rielau
Most DBMSs (some OS) support some kind of reorg (including Oracle).
One way or another the physical structure experiences fragmentation or
other unwelcomed changes.
Christopher Browne
>
> Pages are not exatly a term with any relevance to Oracle ... perhaps
> you meant to respond with respect to SQL Server. And your conclusion
> that empty blocks in Oracle are not reused is quite remote from what
> actually happens.
I'm not sure that you actually read what I wrote, because you
certainly didn't didn't comment on anything that I wrote.
A careful reading would show that I most certainly did /not/ write the
absurd statement that "empty blocks in Oracle are not reused."
What I /actually wrote/ was that:
"... there might not be a single page that gets /totally/ emptied
and thereby returned to the DBMS for general use."
I can't fathom how you drew your absurd statement from what I wrote.
As for the terminology matter, Oracle calls those "blocks;" other
DBMSes often call them "pages," as do operating systems, the initial
question was not specific to any database, so I see no reason to toady
to Oracle's terminology.
--
(reverse (concatenate 'string "gro.gultn@" "enworbbc"))
http://www.ntlug.org/~cbbrowne/emacs.html
MICROS~1 has brought the microcomputer OS to the point where it is
more bloated than even OSes from what was previously larger classes of
machines altogether. This is perhaps Bill's single greatest
accomplishment.
Daniel Morgan
Likely because I hadn't noticed that this thread was multiposted across multiple groups
relating to entirely different environments.
Either way, block or page, Oracle reuses space. And does so rather efficiently.
Daniel Morgan
http://www.extension.washington.edu/extinfo/certprog/oad/oad_crs.asp
Serge Rielau
Howard J. Rogers
(What, pray, are you thinking of... not the 'move' command, perchance?? And
surely not the export-truncate-import shennanigans?)
The need to re-organise anything in Oracle is miniscule. Not saying the
product is perfect, naturally, but PCTFREE/PCTUSED is a pretty efficient
mechanism to ensure the re-use of "pages" vaccated by deletes.
Regards
HJR
"Serge Rielau" <sri...@ca.ibm.com> wrote in message
news:3D9B7435...@ca.ibm.com...
Serge Rielau
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/em.920/a86647/reorg.htm#1006543
I'm not sure why Oracle would spend money on features that aren't useful
in one way or another.
Howard, please note that this thread has in no means been an attack on
any product, nor on the relative need for reorg between produts. However
minuscule the need for reorg of some sorts is in any given product
(inluding Oracle) it exists and that makes reorg one of the many reasons
to stick to separation of an abstract access to the data withot exposing
any physical properties.
.. which coincidently was is the topic of this thread.
Now if you insist we can cut out all the other cross-posted groups and
turn to a reorg war in the Oracle newsgroup.
Howard J. Rogers
> Naturally my knowlegde of Oracle is mostly book-smarts....
>
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/em.920/a86
647/reorg.htm#1006543
> I'm not sure why Oracle would spend money on features that aren't useful
> in one way or another.
Note the use of the word 'Wizard' in that product! It's for people who like
Windows, and thus weren't able to set it up properly in the first place (and
I'm being semi-serious).
>
> Howard, please note that this thread has in no means been an attack on
> any product, nor on the relative need for reorg between produts.
Wasn't suggesting that it was. I was picking up, specifically with regard to
Oracle, on the claim that reorganisations are required routinely to ensure
efficient use of space. I imagine other products have a similarly miniscule
need for reorganisation when they're set up properly to begin with.
>However
> minuscule the need for reorg of some sorts is in any given product
> (inluding Oracle) it exists and that makes reorg one of the many reasons
> to stick to separation of an abstract access to the data withot exposing
> any physical properties.
> .. which coincidently was is the topic of this thread.
>
> Now if you insist we can cut out all the other cross-posted groups and
> turn to a reorg war in the Oracle newsgroup.
>
Nah, I was just addressing a specific issue I saw raised that isn't really
true for Oracle. Quite happy to let sleeping dogs lie.
Regards
HJR
Richard Foote
This thread began to lose it's relevance from an Oracle point of view when
the discussion of reorg started (at the point when my name sake started
questioning the reasons for a re-org).
As Daniel, Sybrand, Norman and Howard have all since stated, if tables have
been set-up correctly (with appropriate values for PCTUSED/PCTFREE), a reorg
is generally not required as tables do not become fragmented. Emptyish
blocks get placed back on the freelists and can be subsequently reused very
efficiently.
Fragmentation occurs when PCTUSED/PCTFREE have not been set appropriately or
if a table permanently reduces in size.
The issue of a table no longer meeting a clustering factor is not relevant
per se in Oracle as Oracle doesn't have the concept of a 'clustering index'
on a table. The closest Oracle gets to this is it's implementation of
Clusters (which is somewhat different) and Index Organised Tables (which is
even more somewhat different). Rows in Heap tables are 'effectively'
randomly distributed, especially so if deletes occur. This is good in that
it's one less reason for a reorg but bad in that maintaining a desirable
clustering factor is difficult.
Having such a discussion across so many groups is difficult as each DB does
things and implements things differently.
BTW (sorry Norman), this is by no means the "Ultimate Question". My vote for
the real Ultimate Question is "Will you marry me" ;)
Cheers
Richard
"Serge Rielau" <sri...@ca.ibm.com> wrote in message
news:3D9C3BFE...@ca.ibm.com...
Norman Dunbar
>> BTW (sorry Norman), this is by no means the "Ultimate Question".
I'm not sure why you are apologising to me, I never started the thread,
so it isn't my conversation title :o)
>> My vote for the real Ultimate Question is "Will you marry me" ;)
It's kind of you to ask, but I have to turn you down on this occasion !
It's not that I'm old fashioned or anything, but you see, we've never
met so I'm unable to make a valued judgement.
And anyway, I'm already married :o)
Richard Foote
news:E2F6A70FE45242488C86...@lnewton.leeds.lfs.co.uk...
> Hi Richard,
>
> >> BTW (sorry Norman), this is by no means the "Ultimate Question".
> I'm not sure why you are apologising to me, I never started the thread,
> so it isn't my conversation title :o)
For the abbreviation, the abbreviation !!
>
>
> >> My vote for the real Ultimate Question is "Will you marry me" ;)
> It's kind of you to ask, but I have to turn you down on this occasion !
> It's not that I'm old fashioned or anything, but you see, we've never
> met so I'm unable to make a valued judgement.
> And anyway, I'm already married :o)
Thank God. If my wife finds out I've been flirting again, she'll have me ;)
Serge Rielau
tool for beginner's mistakes... What about repartitioning of data?
Putting aside all the marketing it is still desirable to partition data
in some cases in a cluster. Naturally, as data evolves there comes a
need to re-partition to balance the load.
Howard: I to prefer academic talk over flame-war games :-)
Cheres
TurkBear
Clearly we have all forgotten that the Ultimate Question was "How much is 6 X 7?"
The answer was 49 - OOPS!, no wonder stuff does not work...
Don't Panic!
"Richard Foote" <richar...@bigpond.com> wrote:
-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------
http://www.newsfeed.com The #1 Newsgroup Service in the World!
-----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =-----
Daniel Morgan
I can see numerous reasons in Oracle to reorg. Most of them irrelevant if a
competent DBA is involved in database creation. Among the ones you mention ...
appropriate settings for extent size, pctfree, pctused, pctincrease, etc. If one
uses locally managed tablespaces with uniform extents ... the pctincrease and
extent size issues go away.
But the one remaining issue that does not go away is balancing I/O. There is no
way anyone can know how I/O will happen in the real world before an application
goes into production. We can all make educated guesses but end-users have a way
of surprising us on an ongoing basis.
So I can see a reason to move things around from time-to-time to balance I/O.
But other than that ... you are correct. It is not an issue in Oracle.
Daniel Morgan
Brian Peasland
Thanks,
Brian
Serge Rielau wrote:
>
> OK, I'm no Orcale DBA, so I have to believe you that reorg is a patch
> tool for beginner's mistakes... What about repartitioning of data?
> Putting aside all the marketing it is still desirable to partition data
> in some cases in a cluster. Naturally, as data evolves there comes a
> need to re-partition to balance the load.
>
> Howard: I to prefer academic talk over flame-war games :-)
>
> Cheres
Serge Rielau
scale tip towards flame the moment I do so.. Maybe someone less likely
to sound biased can pick up the reigns here...
Cheers
Jim Kennedy
mean by cluster? (That isn't a flame item, just a fact. I suspect that DB2
might have a different meaning for cluster than Oracle - not better or
worse, just different.)
Jimnews:3D9C98A...@ca.ibm.com...
Alexander Kuznetsov
> Why would you put a partition in a cluster?
Phil Singer
>
> I think we might be assuming different definitions for cluster. What do you
> mean by cluster? (That isn't a flame item, just a fact. I suspect that DB2
> might have a different meaning for cluster than Oracle - not better or
> worse, just different.)
> Jim
> "Serge Rielau" <sri...@ca.ibm.com> wrote in message
> news:3D9C98A...@ca.ibm.com...
> > I prefer not to be the one answering that question since I can see the
> > scale tip towards flame the moment I do so.. Maybe someone less likely
> > to sound biased can pick up the reigns here...
>
Having read thru all of this (and having once worked with
DB2 in an earlier life), I thought I might have something
to contribute.
DB2 is designed around IBM hardware. On an IBM mainframe,
there is special hardware support to do a sequential read
'real fast'. Because the database is so tuned to the hardware,
one has to jump thru hoops to avoid making sequential reads
be anything other than fast. Consequently, DB2 goes out
of its way to make sequential reads (in Oracle read: Full
Table Scan) do as much as possible.
Consequently, it has the construction of a 'cluster index'.
Forget everything you know from Oracle, it is unrelated.
If an index is the cluster index for a table (which can
have only one) it means that the data is physically ordered
on the disk in the order implied by the index. This means,
that if you do a full table scan, the rows come out in
the same order as they would if you retrieved each row
by the cluster index.
This means that queries which are ordered by this index
can be done quite easily (the index may not even have to
be read). Index range scans also go fast.
Now couple this with the fact that DB2 programs typically
use what Oracle now calls optimizer plan stability (you
cannot change the execution plan of a compiled program)
and you see that it is essential to keep the table
physically ordered. And, if you have a lot of inserts
and updates, the only way to do this is to unload and
reload the table. When I was a DB2 DBA most of the
nightly production cycle was based on doing exactly
that.
I believe that SQL Server has something similar, although
I know I am likely to be in error.
This is really the only need to reorganize a DB2 table.
It was designed to be as re-org free as possible, in
contrast to IMS and other early DBMS which sometimes
had to be re-org'd twice a day. Of course, some of
these IMS guys were booted up to manager, where they
were put in charge of Oracle systems....
In defense of those who claim that Oracle databases
need to be re-org'd: they may well have found that
in practice it works. When it does, it probably is
because the export/import process also cleaned up
row chaining and rebuilt unbalanced indexes, items
which could have been prevented in the first place
with a little thought in the table definition
(or at least could have been fixed with a lot less work).
--
Phil Singer | psinger1ATchartermiDOTnet
Oracle DBA
Replace the obvious to reply
Serge Rielau
implementation than DB2 for Unix et. Al.
DB2 for Unix also has clustered indexes, but they are on a "best effort"
basis.
I'm not a DBA, but I could imagine that row-chaining would be a common
req'd to do reorgs.
How much of that can be prevented by design (other than by avoiding
VARCHAR() ;-) I do not know.
Come to think of it, recovering extends (collections of pages) after a
table shrinks, might be another reason for reorg in DB2 for Unix...
Jim Kennedy
Thanks for the info. Sounds similar to an IOT in Oracle (index organized
table). In Oracle a clustered table is something else. One type is where
you frequently get a parent and the child records (eg an employee's
demographic information and their pay history). In Oracle you can cluster
this information together so a block has the parent record, the child
records (without repeating the key that joins them). You can still refer to
them as seperate tables, but if you are going to frequently get the parent
and all the related child records then it can be advantagouse to use a
cluster. The other type of Oracle cluster is a hash cluster where you have
a constant set of rows (preferably) and choose a column that you are going
to look up by frequently. Then Oracle constructs a hash and is very
efficient at going right to the block. (not good if the row count is always
increasing, good for static look up tables with equality conditions.)
Jim
"Phil Singer" <psin...@chartermi.invalid> wrote in message
news:3DA24A4E...@chartermi.invalid...
Richard Foote
"Phil Singer" <psin...@chartermi.invalid> wrote in message
news:3DA24A4E...@chartermi.invalid...
From my distant memories of DB2 this reorg was always an issue. When
inserting data DB2 would attempt to keep the clustered key order but as free
space ran out, the order was maintained by pointers and the clustering
factor become worse and worse. Eventually, reading via the clustering key
becomes too inefficient and a reorg is required.
The key benefits here are of course being able to read the data in a
predefined order, hence eliminating a sort. Also range scans via the
clustered index are efficient as fewer pages need to be visited. The bummer
though were those damn reorgs (although it was always good for overtime).
I going back many years here so things could well be quite different.
>
> I believe that SQL Server has something similar, although
> I know I am likely to be in error.
>
> This is really the only need to reorganize a DB2 table.
But if much data is inserted/updated, these reorgs could be pretty regular
(a database on high fibre diet so to speak ;)
Oracle doesn't have this as an issue per se and hence Oracle's needs for
reorgs are somewhat less (as mentioned earlier in this thread).
> It was designed to be as re-org free as possible, in
> contrast to IMS and other early DBMS which sometimes
> had to be re-org'd twice a day. Of course, some of
> these IMS guys were booted up to manager, where they
> were put in charge of Oracle systems....
>
> In defense of those who claim that Oracle databases
> need to be re-org'd: they may well have found that
> in practice it works. When it does, it probably is
> because the export/import process also cleaned up
> row chaining and rebuilt unbalanced indexes, items
> which could have been prevented in the first place
> with a little thought in the table definition
> (or at least could have been fixed with a lot less work).
I would suggest it's not that common a practice to regularly export/import
an Oracle database. And for some sites that do, they do so for somewhat
unnecessary reasons (such as keeping extents down to one, in the belief it's
an effective backup, etc.).
I would also suggest that rebuilding indexes because they become unbalanced
is also not a common practice on account that Oracle's indexes are always
balanced. Indexes with oddish characteristics (that may have wasted space
that's unlikely to be reused) may benefit from an occasional rebuild but the
requirement to do so is somewhat rare (although admittedly it doesn't
prevent some from doing so more regularly).
Even row migration could be "cured" by a simple reinsertion of the effected
rows rather than a full table reorg as such.
Cheers
Richard