Re: Tricky SQL Problem: Top "n" of queried records

In article <6famuk$tnf$1_at_nnrp1.dejanews.com>,   Sherilyn <Sherilyn_at_sidaway.demon.co.uk> wrote:
>
> In article <6f95kq$jhk$1_at_nnrp1.dejanews.com>,
> bmuller_at_kenan.com wrote:
> >

 <snip>
> >
> > In Sybase, this session:
> >
> > > set rowcount 3
> > > go
> > > select id, name from sysobjects
> > > order by id
> > > go
> >
> > returns:
> >
> > id name
> > ---------- ------------
> > 1 sysobjects
> > 2 sysindexes
> > 3 syscolumns
> >
> > And it runs very quickly - definitely subsecond.
> >
> > In Oracle, this session:
> >
> > SQL> select object_id, object_name from all_objects
> > 2> where ROWNUM < 4
> > 3> order by object_id
> >
> > returns:
> >
> > OBJECT_ID OBJECT_NAME
> > ---------- ---------------
> > 78 ACCESS$
> > 934 ALL_ARGUMENTS
> > 984 ALL_ALL_TABLES
> >
> > very quickly - also subsecond, but it does not represent the correct
> > dataset.
> > The first three object_id values should be 3, 4, and 5.
> >
> Granted that it's not correct, it is however the limit of what you can
> expect from SQL where ordering is an operation carried out after
> selection and aggregation. Sybase's workaround is quite elegant and
> does the job. In Oracle, you should create an ordered cursor, open it,
> and fetch only the first N rows. There is no way to do this in Oracle's
> command-line query tool, sqlplus, without resorting to a PL/SQL block
> and using the DBMS_OUTPUT package if the results are to be displayed;
> this practical problem is not experienced in embedded code, where
> opening a cursor and fetching rows is normal procedure.
>

This is correct, actually. There doesn't seem to be any guaranteed way to make it work from Oracle server release to release, except to use a cursor.

> There is a trade-off between the convenience of a Sybase-style "set
> rowcount" fix and the danger that you might accidentally set it and
> forget it, resulting in loss of data (not knowing the details of
> Sybase's solution I don't know whether this is an applicable problem).
> Whatever, if Oracle thought of this, they presumably rejected it
> because of the potential danger.

There are so many ways bugs can be written into code that it's always important to code responsibly. I don't see "set rowcount" as potentially dangerous, unless you write an application which depends upon it heavily and then you need to port it to another RDBMS. It is not a portable feature, and therefore I encourage people not to use it except for ad-hoc queries and development purposes. Who knows what Oracle Corp is thinking at any given time. Probably since it's not an ANSI or industry standard, they haven't paid much attention to it. The squeaky wheel gets the oil, and a cursor will do the job here.

Brenda Muller

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Wed Mar 25 1998 - 00:00:00 CST