sql_limit_under_oracle

This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.



Subject: Limiting # of records returned
From: Mike Baranski <mbaranski@celito.net>
Date: Wed, 06 Dec 2000 15:34:56 -0500

I've got an import program written in perl, which connects to an Oracle
database and generates a flat file for peoplesoft to do an import.  Does
anyone know of an sql statement that will allow me to limit hte number
of records returned for oracle?  

select * from theDB.badge limit 100

or something like that?

===

Subject: RE: Limiting # of records returned
From: "Sterin, Ilya" <Isterin@ciber.com>
Date: Wed, 6 Dec 2000 12:37:37 -0700 

Either use the where clause on a field that will limit to amount you want or
use $sth->finish() in your fetch loop to stop the loop operation and exit
the loop with next();

Ilya Sterin

-----Original Message-----
From: Mike Baranski
To: dbi-users@isc.org
Sent: 12/06/2000 1:34 PM
Subject: Limiting # of records returned


I've got an import program written in perl, which connects to an Oracle
database and generates a flat file for peoplesoft to do an import.  Does
anyone know of an sql statement that will allow me to limit hte number
of records returned for oracle?  

select * from theDB.badge limit 100

or something like that?

===

Subject: RE: Limiting # of records returned
From: "Tim Harsch" <harsch1@llnl.gov>
Date: Wed, 6 Dec 2000 11:43:52 -0800

select * from theDB.badge where rowid <= 100

===

Subject: RE: Limiting # of records returned
From: Kuo David <dkuo@orchid.com>
Date: Wed, 6 Dec 2000 14:48:45 -0500 

I think it should be rownum instead of rowid. Isn't it?

select * from theDB.badge where rownum <= 100

David Kuo

-----Original Message-----
From: Tim Harsch [mailto:harsch1@llnl.gov]
Sent: Wednesday, December 06, 2000 2:44 PM
To: Mike Baranski; dbi-users@isc.org
Subject: RE: Limiting # of records returned



select * from theDB.badge where rowid <= 100

===

Subject: RE: Limiting # of records returned
From: "Tim Harsch" <harsch1@llnl.gov>
Date: Wed, 6 Dec 2000 12:07:53 -0800

Also,	I lack the experience to know whether the pseudo column "rowid" would
help in multi-table joins, sorts, etc.

$sth->finish is a good method.

There could be a battle for performance between the two.  For instance on a
sort finish would have to wait for the server to do the complete sort, but
would the server be able to do a partial sort for a query limited by row id?

Probably too much detail for this question.  $sth->finish is a good method.

===

Subject: FYI: Limiting # of records returned
From: Mike Baranski <mbaranski@celito.net>
Date: Wed, 06 Dec 2000 16:16:10 -0500

Thanks for all of the help, just fyi, this was a view, and the 
... rowid <= nnn 
did not work, I don't exactly know why...
But, I did put a counter into the fetchrow_array loop, and it works
great!

===

Subject: RE: Limiting # of records returned(OT: Oracle-specific)
From: "Sarnowski, Chris" <csarnows@CuraGen.com>
Date: Wed, 6 Dec 2000 15:21:42 -0500 

Sorry for the OT post, but many people have misconceptions
about rownum, so this is intended to save some grief.

In Oracle, you can use the pseudocolumn rownum
to limit the number of rows returned.

select field from table where rownum <= 100;

But there are limitations of rownum: (!!!)

1) You can't use rownum with order by, and get the results
you might expect. The order by is applied after the result
set is obtained, and since Oracle is a relational database,
the result set may be returned in any order. If you want
a particular set of 100 rows, you have to do something like
the following:

select field from
   (select t.field, rownum rnum from table t order by 1)
where rnum <= 100;

2) You can't select the nth record with

select field from table where rownum = n;

where n > 1. 

You have to use a query similar to the nested query above.

If you want a fuller explanation, send me a message off-list.
I would not be surprised if it's in the archives of this list
(just checked: it does come up periodically), and is or ought
to be on any worthwhile Oracle DB website, since it is so
frequently asked.

On the other hand, in perl, there's no reason not to go
procedural and just stop fetching when you have as many rows
as you want.

-Chris

===

Subject: RE: Limiting # of records returned
From: "Dewis, Gordon - GEO/GEO" <gordon.dewis@statcan.ca>
Date: Wed, 6 Dec 2000 15:13:12 -0500 

On page 303 of _Programming the Perl DBI_, there is a note that there are
"some frustrating limitations" with Oracle's ROWNUM.

Personally, I'd count the number of records and do a finish() when you have
all you want.

===

Subject: RE: Limiting # of records returned
From: Kuo David <dkuo@orchid.com>
Date: Wed, 6 Dec 2000 16:00:07 -0500 

It is working for view as well as table. The key point here is you should
use the pseudocolumn rownum instead of rowid. You will get "ORA-00932:
inconsistent datatypes" error if you use "select ... from.. where rowid <=
100". 

David Kuo

===

Subject: RE: Limiting # of records returned(OT: Oracle-specific)
From: "Tim Harsch" <harsch1@llnl.gov>
Date: Wed, 6 Dec 2000 13:21:08 -0800

OK.  I get rownum to work in most cases but by this message below it looks
sort of as if ROWID is a legitimate keyword?  Is that true or is just ROWNUM
...


SQL> select * from DBA_INDEXES where rowid < 10
  2  ;
select * from DBA_INDEXES where rowid < 10
              *
ERROR at line 1:
ORA-01445: cannot select ROWID from a join view without a key-preserved
table

===

Subject: Re: Limiting # of records returned(OT: Oracle-specific)
From: Ronald J Kimball <rjk-dbi@focalex.com>
Date: Wed, 6 Dec 2000 16:29:31 -0500

On Wed, Dec 06, 2000 at 01:21:08PM -0800, Tim Harsch wrote:
> 
> OK.  I get rownum to work in most cases but by this message below it looks
> sort of as if ROWID is a legitimate keyword?  Is that true or is just ROWNUM
> ...
> 
> 
> SQL> select * from DBA_INDEXES where rowid < 10
>   2  ;
> select * from DBA_INDEXES where rowid < 10
>               *
> ERROR at line 1:
> ORA-01445: cannot select ROWID from a join view without a key-preserved
> table
> 

This is from Oracle8: The Complete Reference:

Pseudo-Columns

  ...

  RowID     Returns the row identifier for a row.  Use the RowID in
            in the UPDATE ... WHERE and SELECT ... FOR UPDATE.  This
            guarantees that only a certain row is updated, and no others.

  RowNum    Returns the sequence number in which a row was returned
            selected from a table.  The first row RowNum is 1, the
            second is 2, and so on.  An order by will affect the sequence
            of the ROWNUMs. ...

  ...


Note that row in a table has a specific RowID, while RowNum depends on a
select statement and the order the rows are returned in.


Here's what RowIDs look like:

select rowid from list

ROWID
------------------
AAAAd4AAHAAACyVAAA
AAAAd4AAHAAACyVAAB
AAAAd4AAHAAACyVAAC
AAAAd4AAHAAACyVAAD
AAAAd4AAHAAACyVAAE
AAAAd4AAHAAACyVAAF
AAAAd4AAHAAACyVAAH
AAAAd4AAHAAACyVAAI
AAAAd4AAHAAACyVAAJ
AAAAd4AAHAAACyVAAL
AAAAd4AAHAAACyVAAM


===


the rest of The Pile (a partial mailing list archive)

doom@kzsu.stanford.edu