Re: Difference b/w 2 ways of fetching record

I would certainly recommend method 1. Temporary tables, even though they generate less redo than ordinary tables, certainly require some database overhead (look for space in the temp tablespace, clean up old temp segments if necessary, extend the temp tablespace if necessary, ...). By contrast, your select statement requires much less database overhead.

Daniel

spraveen2001_at_yahoo.com (Praveen) wrote in message news:<98d8ec76.0302130059.7f5a0e69_at_posting.google.com>...
> Hi All,
>
> I have a USR table with username,first_name,last_name,nick_name and email_address
> columns. Here the requirement is to fetch records in ranges like 1-100,101-201...
> and so on. I have done this using two(2) ways, They are:
>
> 1.
>
> select username,first_name,last_name,nick_name,email_address from (
> select rownum recno,first_name,,last_name,nick_name,email_address
> from USR) where recno >= 1 and recno < 101;
>
> 2. In this case, I created a GLOBAL TEMPORARY TABLE called TMP_USR with
>
> seq_id,username,first_name,last_name,nick_name and email_address columns.
>
> Insert into tmp_usr(
> seq_id,username,first_name,last_name,nick_name,email_address)
> select rownum,username,first_name,last_name,nick_name,email_address
> from USR;
>
> then,
> select username,first_name,last_name,nick_name,email_address
> from TMP_USR where seq_id >=1 and seq_id < 101;
>
> Here, which approach is best one, can anyone sugges me?
>
> Thanks in Advance,
> Praveen
Received on Thu Feb 13 2003 - 15:30:00 CET