This discussion is archived
14 Replies Latest reply: Nov 25, 2012 7:21 AM by fac586 RSS

Preventing apex from fetching all rows of a sql statement

797707 Newbie
Currently Being Moderated
Hi all,

we have a apex site with an interactive report, that fetches 20000 rows. rows per page is adjusted to 25 rows. But apex always fetches the complete 20000 rows though only 25 rows are displayed. so our statement takes a long time (up to 2 mins) to complete and only 25 results are there. i be aware of the max_row_count feature but we have a need that all results are displayed.
is there a possibility that apex fetches first 25 results, after pagination the next 25 and so on, but not all on the first page?
the only trick i can remember is to wright the ROWNUM directly in the sql statement, but this "trick" isn´t the most beautiful solution...
seeing:
SELECT *
  FROM (SELECT rownum,
               emp.lastname
          FROM emp
         WHERE emp.empno BETWEEN 1 AND 100
         ORDER BY  emp.lastname)
 WHERE rownum <= 5
thanks for your help!

- daniel

Edited by: danielh on 22.11.2012 07:22

Edited by: danielh on 22.11.2012 07:23

Edited by: danielh on 22.11.2012 07:26
  • 1. Re: Preventing apex from fetching all rows of a sql statement
    jariola Guru
    Currently Being Moderated
    danielh wrote:
    Hi all,

    we have a apex site with an interactive report, that fetches 20000 rows. rows per page is adjusted to 25 rows. But apex always fetches the complete 20000 rows though only 25 rows are displayed. so our statement takes a long time (up to 2 mins) to complete and only 25 results are there. i be aware of the max_row_count feature but we have a need that all results are displayed.
    is there a possibility that apex fetches first 25 results, after pagination the next 25 and so on, but not all on the first page?
    the only trick i can remember is to wright the ROWNUM directly in the sql statement, but this "trick" isn´t the most beautiful solution...
    seeing:
    SELECT *
    FROM (SELECT rownum,
    emp.lastname
    FROM emp
    WHERE emp.empno BETWEEN 1 AND 100
    ORDER BY  emp.lastname)
    WHERE rownum <= 5
    Hi,

    What is your report pagination schema?
    If it is Row Ranges X to Y of Z APEX need query how many rows you have.



    Regards,
    Jari
    -----
    My Blog: http://dbswh.webhop.net/htmldb/f?p=BLOG:HOME:0
    Twitter: http://www.twitter.com/jariolai
  • 2. Re: Preventing apex from fetching all rows of a sql statement
    797707 Newbie
    Currently Being Moderated
    it´s "row ranges x to y"...
  • 3. Re: Preventing apex from fetching all rows of a sql statement
    560962 Newbie
    Currently Being Moderated
    In the Layout and Pagination you have a "Maximum number of rows" input, have you set any value there?
  • 4. Re: Preventing apex from fetching all rows of a sql statement
    797707 Newbie
    Currently Being Moderated
    hi atanask,

    yes, as discribed above i know about the max_row_count input, has also set this to 40000, because we need such a huge value...the problem is that apex fetches truly the complete statement (20000 rows), not only the ones that are visible, in my scenario 25 rows. I talked to my colleagues from the oracle forms section, and forms can handle with that problem, whilst fetching only the visible rows, then on pagination the next visible rows and so on...apex gets ALL rows and after that i can click through the pages.

    maybe some background info helps:
    - the statement is dynamic, and i build this in a sql function that returns varchar2
    - after that i create a collection from query in a before header process: apex_collection.create_collection_from_query(my_function_returning_query)
    - and the statement in the region source looks like this:
    SELECT c001,
               c002,
               c003,
               c004,
               c005,
               c006,
               ...
      FROM apex_collections
     WHERE collection_name = 'MY_COLLECTION_NAME';
    - daniel
  • 5. Re: Preventing apex from fetching all rows of a sql statement
    797707 Newbie
    Currently Being Moderated
    Does nobody has an idea to solve this?

    Maybe it´s really the apex behaviour itself, that apex fetches all results and after that i can click through the pages...but this would be the worst case...
  • 6. Re: Preventing apex from fetching all rows of a sql statement
    jariola Guru
    Currently Being Moderated
    Hi,

    What I understand when page loads, IR fetch only first 25 first row in your case.
    When you paginate to next set, APEX will fetch next 25 rows using Ajax call.

    Maybe I do not understand what you mean or how you come conclusion that all rows from your query are fetch when page is loaded.

    Please explain more details your problem and maybe you set some example case to apex.oracle.com

    Regards,
    Jari
    -----
    My Blog: http://dbswh.webhop.net/htmldb/f?p=BLOG:HOME:0
    Twitter: http://www.twitter.com/jariolai
  • 7. Re: Preventing apex from fetching all rows of a sql statement
    797707 Newbie
    Currently Being Moderated
    hi jarola,

    thanks for reply!

    No the problem is that apex fetches all rows (20000) on the first page load, whilst only 25 are visible...it would be nicer that apex fetches the first 25 then the next 25 and so on.
    and because apex fetches the hole statement first, the complete page hanges 2-3 mins. After that, or when the page is finally there i can click through the report very fast, because apex has already all results cached...

    an example instance on apex.oracle.com is difficult because the main software is written in oracle forms, an apex is just an extra software...

    - daniel

    Edit: maybe the reason for this behaviour is the dynamic sql in combination with apex collection? or only the apex collection that i create before header? I don´t knopw what apex do when creating a collection, does it fetch trhe complete statement?

    Edited by: danielh on 23.11.2012 00:53
  • 8. Re: Preventing apex from fetching all rows of a sql statement
    jariola Guru
    Currently Being Moderated
    Hi,

    How you have check that APEX fetch all rows when page loads ?
    Do you have other region, page / application computation or process that might take time on page load?

    See how debug application
    http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35125/debug.htm#sthref2428

    Regards,
    Jari
    -----
    My Blog: http://dbswh.webhop.net/htmldb/f?p=BLOG:HOME:0
    Twitter: http://www.twitter.com/jariolai
  • 9. Re: Preventing apex from fetching all rows of a sql statement
    fac586 Guru
    Currently Being Moderated
    danielh wrote:

    yes, as discribed above i know about the max_row_count input, has also set this to 40000, because we need such a huge value...the problem is that apex fetches truly the complete statement (20000 rows), not only the ones that are visible, in my scenario 25 rows. I talked to my colleagues from the oracle forms section, and forms can handle with that problem, whilst fetching only the visible rows, then on pagination the next visible rows and so on...apex gets ALL rows and after that i can click through the pages.
    What evidence do you have that APEX is fetching all 20&thinsp;000 rows?
    maybe some background info helps:
    - the statement is dynamic, and i build this in a sql function that returns varchar2
    - after that i create a collection from query in a before header process: apex_collection.create_collection_from_query(my_function_returning_query)
    - and the statement in the region source looks like this:
    SELECT c001,
    c002,
    c003,
    c004,
    c005,
    c006,
    ...
    FROM apex_collections
    WHERE collection_name = 'MY_COLLECTION_NAME';
    How long does this take? A debug trace for the rendering of this page would be illustrative.

    I am not (and never have been) happy with this (or any other) approach to "dynamic" interactive reports. The fact that Oracle have never provided a "function body returning SQL query" IR source option like that for standard reports is intentional (a moment's thought will show how this could break IRs in many ways).

    Why are you trying to build dynamic IRs?

    With the data in APEX collections: it's duplicated for every user; it's got no indexes for use by IR filters; the optimizer has no statistics based on your data (and will produce unsuitable execution plans using those it does have for the underlying tables).
  • 10. Re: Preventing apex from fetching all rows of a sql statement
    797707 Newbie
    Currently Being Moderated
    thanks for that hint^^
    but now the reason for bad performance is clear:
    Collection - create_collection_from_query, Begin fetch rows - 58 secs

    the problem is that i use an intercative report, and there i can´t use function returning query, the only solution i know is apex_collection...
    the statement is highly dynamically, with union, dynamic where clause and so on. Do you have any idea or a hint to solve such a problem or limit the fetching of apex_collection?

    many thanks!

    - daniel
  • 11. Re: Preventing apex from fetching all rows of a sql statement
    jariola Guru
    Currently Being Moderated
    danielh wrote:
    Do you have any idea or a hint to solve such a problem or limit the fetching of apex_collection?
    Hi,

    I'm not again sure what you mean.
    I do not have any idea what query you use for create_collection_from_query API.

    I think you did understand that problem is not that IR query all rows from collection when page loads.

    Regards,
    Jari
    -----
    My Blog: http://dbswh.webhop.net/htmldb/f?p=BLOG:HOME:0
    Twitter: http://www.twitter.com/jariolai
  • 12. Re: Preventing apex from fetching all rows of a sql statement
    fac586 Guru
    Currently Being Moderated
    danielh wrote:
    thanks for that hint^^
    but now the reason for bad performance is clear:
    Collection - create_collection_from_query, Begin fetch rows - 58 secs
    Exactly how is the collection created? Try using the <tt>apex_collection.create_collection_from_query_b</tt> method which offers significantly higher performance. Make sure the source query is fully optimized. (If possible, use <tt>apex_collection.create_collection_from_queryb2</tt> to get columns of the correct data types in the collection.)
    the problem is that i use an intercative report, and there i can´t use function returning query, the only solution i know is apex_collection...
    Another possible dynamic IR source is a pipelined function. This may (or may not) perform better than using collections. Search the forum for examples of this.
    the statement is highly dynamically, with union, dynamic where clause and so on. Do you have any idea or a hint to solve such a problem or limit the fetching of apex_collection?
    We'd need full access to the data model and requirements to determine if the IR could be created without a dynamic source.
  • 13. Re: Preventing apex from fetching all rows of a sql statement
    797707 Newbie
    Currently Being Moderated
    Thanks for the answers so far!

    I now tested the apex_collection.create_collection_from_query_b function and it´s up to 10 times faster than the other method. I don´t expected such a increase of speed. So now my statement is finishing in about 5-10 secs. Still not the fastest but for a query with 20000 results quite ok! Thanks for that tip!

    Unfortunately I can´t offer the application and the data model, because the hole software has round about 400 tables with a lot of dependences and java in the db, too...so it can´t run on oracle xe (apex.oracle.com). But the dynamic sql part is proofed, it doesn´t work without that...

    To summarize:
    - for IR in combination with dynamic SQL there are 2 solutions: collections and pipelined functions, is that right?
    - Can collections be limited to amount of data, so they don´t fetch all results? My idea is to limit the source statement of the collection wit ROWNUM to the MAX_ROW_COUNT of the IR...
    - The statement itself should be best optimzed, so thats the next part on my todo list

    - daniel
  • 14. Re: Preventing apex from fetching all rows of a sql statement
    fac586 Guru
    Currently Being Moderated
    danielh wrote:
    Thanks for the answers so far!

    I now tested the apex_collection.create_collection_from_query_b function and it´s up to 10 times faster than the other method. I don´t expected such a increase of speed. So now my statement is finishing in about 5-10 secs. Still not the fastest but for a query with 20000 results quite ok! Thanks for that tip!

    Unfortunately I can´t offer the application and the data model, because the hole software has round about 400 tables with a lot of dependences and java in the db, too...so it can´t run on oracle xe (apex.oracle.com).
    apex.oracle.com is most definitely NOT Oracle XE!
    select * from v$version
    
    BANNER
    ------
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    PL/SQL Release 11.2.0.3.0 - Production
    CORE     11.2.0.3.0     Production
    TNS for Linux: Version 11.2.0.3.0 - Production
    NLSRTL Version 11.2.0.3.0 - Production
    To summarize:
    - for IR in combination with dynamic SQL there are 2 solutions: collections and pipelined functions, is that right?
    If you must persist with a dynamic IR source, yes. There are many reasons not to.
    - Can collections be limited to amount of data, so they don´t fetch all results? My idea is to limit the source statement of the collection wit ROWNUM to the MAX_ROW_COUNT of the IR...
    Of course the number of rows inserted into the collection can be limited. But on what basis? How will this affect your users? What if the rows they are looking for are not included in the set in the collection?

    I'd be looking at the basics here. Does this really need to be an interactive report? If so, does it have to use a dynamic query (considering alternatives that enable static SQL to be used: <tt>UNION ALL</tt> with exclusive <tt>WHERE</tt> clauses, views, or materialized views.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points