Home » SQL & PL/SQL » SQL & PL/SQL » Paging through rows with oracle 8.0.5
Paging through rows with oracle 8.0.5 [message #198938] Thu, 19 October 2006 04:15 Go to next message
tzeweng
Messages: 5
Registered: October 2006
Location: Australia
Junior Member
I have been hunting for a solution for this for days... This should be simple but Gosh its been tough.. and still no good solution..

I have seen the usual solution below.. EVERYWHERE eg http://www.orafaq.com/faqsql.htm

SELECT *
FROM (SELECT a.*, rownum RN
FROM (SELECT *
FROM t1 ORDER BY key_column) a
WHERE rownum <=7)
WHERE rn >=5

But that doesn't work for Oracle 8.0.5 because the subquery doesnt support the ORDER BY clause.

So how can it be done?. efficently? .. Found a few other posible answers but most still use need to use ORDER BY Clause in the a subquery which is not supported in 8.0.5 . Those that i found that do not use the subquery/Orderby are VERY inefficent and would take mins or hours to run on my dataset.

I need to order by 2 or 3 columns in acending and decending orders.

I have millions of records but that can be constrained by a WHERE clause to 100 - 30,000 records. So I
still need an efficent way to page up and down this result
set.

The solution should be such that I provide the following parameters and it returns the record set.

PageNum : Page number to return
RowPerPg : Number of rows per page
WhereClause : The where clause to contrain the posible rows to < 25,000
TableName :
ColumnNames : List of columns to return
SortColPri : Primary Sort Column
SortColSec : Secondary Sort Column
SortDirection : ASC or DESC of primary sort Column

I am using JDBC to access the oracle 8.0.5

one option suggestion found on the internet.
* How efficent will it be to use cursors to retrive the first x set and ignore it then retrive the next RowPerPg ? What would that SQL code look like? Probably should be in a stored procedure.

* Using the "GROUP BY rownum" trick to order but its only acending.. and only order by one field? How would I include the primary and secondary sortcolumns in the order?

Hope you guys can help !

Thanks in advance..

Regards
TW

[Updated on: Thu, 19 October 2006 04:25]

Report message to a moderator

Re: Paging through rows with oracle 8.0.5 [message #198943 is a reply to message #198938] Thu, 19 October 2006 04:57 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I can't think of a way to do it with optional ASC/DESC. If it were only ASC, you could
GROUP BY {primary}, {secondary}, rowid


The PL/SQL may be your only option, but performance will be bad because 8.0 does not have BULK COLLECT. It might actually be quicker to perform array-fetches from the client (say, in Pro*C) and do the filtering there.

Ross Leishman
Re: Paging through rows with oracle 8.0.5 [message #199045 is a reply to message #198943] Thu, 19 October 2006 20:06 Go to previous messageGo to next message
tzeweng
Messages: 5
Registered: October 2006
Location: Australia
Junior Member
Problem is also the primary sort key (which is variable) may be non unique so your suggested solution to group by primary sort key will not work. So I need to group by the rownum first then the primary sort key. How do I do that? I guess ASC only is ok cause I can calculate the range from the bottom up if I have to.. ...

OH BTW I left out something important.. The Client is a Web Page.. in JSP...

Update: Didnt notice row ID was included in the Group By example..
Will try that and see.

[Updated on: Thu, 19 October 2006 20:09]

Report message to a moderator

Re: Paging through rows with oracle 8.0.5 [message #199047 is a reply to message #199045] Thu, 19 October 2006 20:23 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Note that I put the ROWID in my GROUP BY clause, this will stop grouping on the sort-key.

You can't group by rownum (or rowid) as the first column, because then it will sort in the order the rows were returned from disk, not in the primary sort column order. If you specify rownum/rowid last, it should work.

Note that this definitely WONT work in 9i+, because it is clever enough to know that GROUP BY A,B is semanticly the same as GROUP BY B,A. It chooses the most convenient sort order according to its own rules. I'm pretty sure v7 wasn't this smart, and I *dont think* 8i was either (never used 8.0).

This seems to work for ASC sorts:
SELECT *
  FROM (SELECT ROWNUM AS rn, a.*
          FROM (SELECT   RPAD (owner, 100, ' ') || RPAD (table_name, 100, ' ') || to_char(ROWNUM,9999990)
             , MAX (owner)
             , MAX (table_name)
                    FROM dba_tables
                GROUP BY RPAD (owner, 100, ' ') || RPAD (table_name, 100, ' ') || to_char(ROWNUM,9999990)) a
         WHERE ROWNUM <= 500)
WHERE RN >= 480


Ross Leishman.

[Updated on: Thu, 19 October 2006 20:25]

Report message to a moderator

Re: Paging through rows with oracle 8.0.5 [message #199049 is a reply to message #198938] Thu, 19 October 2006 20:29 Go to previous messageGo to next message
tzeweng
Messages: 5
Registered: October 2006
Location: Australia
Junior Member
Tried the suggestion from the FAQ at http://www.orafaq.com/faqsql.htm

the example given there is
SELECT rownum, f1 FROM t1
GROUP BY rownum, f1 HAVING rownum BETWEEN 2 AND 4;

My SQL below throws an exception.. Whats wrong?? I am no SQL Guru..

SELECT callDate, callTime, origin, destination, numberDialled, callDuration, callTypeDesc, callCharge, volume , rownum
FROM tablename
WHERE account = '9130236338' AND stmtDate = '2006/04/01' AND phone = '0283456789'
GROUP BY callDate, callTime , rownum HAVING rownum BETWEEN 0 AND 5;

ORA-00979: not a GROUP BY expression

Hope yous Gurus can help..

TW
Re: Paging through rows with oracle 8.0.5 [message #199051 is a reply to message #199049] Thu, 19 October 2006 20:32 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You have to GROUP BY all of the unaggregated columns in your SELECT list.

But easier than that:
SELECT max(callDate), max(callTime), max(origin), ... rownum


I don't think this will work though, because ROWNUM is applied to the rows BEFORE grouping (and therefore before sorting).

Try my suggestion in the earlier post.

Ross Leishman
Re: Paging through rows with oracle 8.0.5 [message #199054 is a reply to message #199047] Thu, 19 October 2006 20:40 Go to previous messageGo to next message
tzeweng
Messages: 5
Registered: October 2006
Location: Australia
Junior Member

Thanks for your reply.. But as I mentionsed I am no SQL guru..
If you dont mind too much please explain the magic you are doing with the
1) RPAD and the
2) to_char(Rownum) and the
3) MAX( .. )

Thanks
TW
Re: Paging through rows with oracle 8.0.5 [message #199098 is a reply to message #199054] Fri, 20 October 2006 02:43 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The purpose of the RPAD etc is to create a single column to group by rather than grouping by several columns. As I mentioned, later version of Oracle - even if they use a sort to resolve a GROUP BY - will not necessarily sort in the specified order.

The RPAD and TO_CHAR functions format the components from variable length to fixed length - this gives us consistent and predictable sorting.

eg. Consider a row with Primary FRED and rownum 2. Consider another row with Primary FRED1 and rownum 11.
PRIMARY || ROWNUM would be:
FRED2
FRED111
which, under the GROUP BY would be reversed. But with the RPAD and TO_CHAR
FRED            2
FRED1          11
will maintain the correct sort order after the GROUP BY.

The purpose of the MAX was to avoid having to put all of the selected columns in the GROUP BY clause. If we did that, Oracle could rightly decide to sort by one of them instead of our sort key (although unlikely in 8.0).

Ross Leishman
Re: Paging through rows with oracle 8.0.5 [message #199104 is a reply to message #199098] Fri, 20 October 2006 03:19 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You could achieve a DESC sort by running the GROUP BY key through the following function:
FUNCTION ROSS_REVERSE_SORT (p_in IN VARCHAR2)
   RETURN VARCHAR2
IS
   l_out    VARCHAR2 (2000);
   l_dump   VARCHAR2 (4000);
   l_num    INTEGER;
   i        INTEGER;
BEGIN
   SELECT DUMP (p_in)
     INTO l_dump
     FROM DUAL;

   l_dump := ',' || SUBSTR (l_dump, INSTR (l_dump, ':') + 2) || ',';
   i := 1;

   LOOP
      l_num :=
         TO_NUMBER (SUBSTR (l_dump,
                            INSTR (l_dump, ',', 1, i) + 1,
                            INSTR (l_dump, ',', 1, i+1) - INSTR (l_dump, ',', 1, i) - 1
                           )
                   );
      EXIT WHEN l_num IS NULL;
      l_out := l_out || CHR (256 - l_num);
      i := i + 1;
   END LOOP;

   RETURN l_out;
END;


For example:
SELECT ross_reverse_sort(table_name), max(table_name)
FROM user_tables
GROUP BY ross_reverse_sort(table_name)


Ross Leishman
Re: Paging through rows with oracle 8.0.5 [message #199158 is a reply to message #199104] Fri, 20 October 2006 12:07 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Group by isn't a substitute for an order by - it doesn't always work.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production

SQL> create table grp_by_tst (col1 number, col2 varchar2(5));
Table created.

SQL> create index grp_by_tst_ix1 on grp_by_tst (col2, col1);
Index created.

SQL> insert into grp_by_tst values (1, 'A1');
1 row created.
SQL> insert into grp_by_tst values (2, 'B2');
1 row created.
SQL> insert into grp_by_tst values (3, 'E3');
1 row created.
SQL> insert into grp_by_tst values (4, 'D4');
1 row created.
SQL> insert into grp_by_tst values (5, 'C5');
1 row created.

SQL> select col1, col2, count(*)
  2  from grp_by_tst
  3  where col2 >= 'A'
  4  group by col1, col2;

      COL1 COL2    COUNT(*)
---------- ----- ----------
         1 A1             1
         2 B2             1
         5 C5             1
         4 D4             1
         3 E3             1
Re: Paging through rows with oracle 8.0.5 [message #199185 is a reply to message #199158] Fri, 20 October 2006 20:36 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Andrew, hopefully the OP gets that after I harped on it for a while (albeit without a tidy example). But, in pre 10.2, if you group by a single column on a non-partitioned table without parallel query server, it can be reliable.

If it was me, I would probably just benchmark the PL/SQL solution with an ORDER BY and prove that the performance was inadequate. Then I might try array fetches from a client-side language like Pro*C (if appropriate to the environment). If both of these failed, I would be prepared to use the work-around the OP is seeking, and even to guarantee its success until partitioning, parallel query, or 10.2 screwed it up.

Ross Leishman

[Updated on: Sun, 22 October 2006 21:21]

Report message to a moderator

Re: Paging through rows with oracle 8.0.5 [message #203375 is a reply to message #198938] Tue, 14 November 2006 23:09 Go to previous messageGo to next message
tzeweng
Messages: 5
Registered: October 2006
Location: Australia
Junior Member
Hello All,

Thanks Ross for your invaluable asistance with this problem.

I sucessfully implemented the Group By solution.

For the Desc sort requirement I put an Order by clause on the outside eg.
"
SELECT * 
  FROM (
    SELECT a.*, ROWNUM AS RN 
      FROM (
        SELECT " + cSqpColumnNamesMin 
        + ", " + groupbyCl + " sortcols"
        + " FROM " + cSqpTableName
        + " WHERE " + baseWhereCl 
        + " GROUP BY " + groupbyCl 
        + " ) a WHERE ROWNUM <= " + pageRowHi
        + " ) WHERE RN > " + pageRowLow   
        + " ORDER BY sortcols " + ((sorting == 1) ? " ASC" : " DESC") 



where
* sorting = is a variable to indicate ascending or descending order.
* pageRowHi and pageRowLow = are calculated from a known number of rows and known number of rows per page and the current page required and the sort order.
the rest of the "variables" are self explanatory I think...

Again thanks for all the help.
Re: Paging through rows with oracle 8.0.5 [message #203405 is a reply to message #203375] Wed, 15 November 2006 01:04 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you have two or more grouping cols, then your DESC will only apply to last one.

ie.
ORDER BY empno, deptno DESC

is the same as
ORDER BY empno ASC, deptno DESC

not
ORDER BY empno DESC, deptno DESC


Ross Leishman
Previous Topic: How to findout the table and column name..(Urgent)
Next Topic: RDBMS BASIC Concepts PDF needed urgently
Goto Forum:
  


Current Time: Fri Dec 09 13:55:48 CST 2016

Total time taken to generate the page: 0.08495 seconds