Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] Oracle: Anyway to disable FIRST_ROWS hint on paginated queries?
If you want to disable this for individual queries, with a little work, it may be possible. (I haven't tried it, but the constructs are there and it shouldn't take much work to try)
The first thing to do would be to subclass the OraclePlatform version you are using. (OraclePlatform, Oracle9Platform or Oracle10Platform)
The code that deals with the filtering is in OraclePlatform in a method called printSQLSelectStatement(). You could override that code in your subclass.
As an argument, that method takes a SQLSelectStatement and that SQLSelectStatement has a handle on the ReadQuery that built it. Here's what you do:
- When you come to a query where you want to alter the behavior, unwrap the underlying EclipseLink DatabaseQuery and set a property on it. (e.g. JpaHelper.getDatabaseQuery(query).getProperties().put("myhint", myValue))
- In your overridden printSQLSelectStatement() method, check that property on the ReadQuery you get from the SQLSelectStatement and alter the behavior based on the value.
- In theory, when you execute the query, the value of your hint will be available through the ReadQuery 

-Tom

Tim Hollosy wrote:
Didn't find a way to fix this, I think I'd have to override OraclePlatform and make my own to do custom paging. I ended up resolving the problem by placing a hint inside my view (bad practice i guess) to help the optimizer overcome whatever wonky stats it was looking at to make it go sideways.
I may enter a bug to add a sessioncustomizer method to disable the hint, since on 9i the first_rows hint forces CBO, it completely changes the plan of the query which can be disorienting. 


./tch
On Thu, Jun 16, 2011 at 4:26 PM, Tim Hollosy <hollosyt@xxxxxxxxx <mailto:hollosyt@xxxxxxxxx>> wrote: 

    Thanks Tom, what about customizing the SQL for a particular call. A
    bit more detail:

    It's a select off of a view, using the expression API. The
    ReadAllQuery that's built with the expression then gets the
    setFirstResult stuff applied. Could I somehow intercept that SQL or
    customize that SQL to remove the hint, or change the first_rows to
    an all_rows?

    ./tch



    On Thu, Jun 16, 2011 at 4:22 PM, Tom Ware <tom.ware@xxxxxxxxxx
    <mailto:tom.ware@xxxxxxxxxx>> wrote:

        We have two ways of doing row number filtering.

        1. with the hint.

        2. Using JDBC

        The unfortunately thing about JDBC is it doesn't really let us
        control the first result that comes back.  We just move the
        cursor to get the firstResult when the hint is off. (the reason
        we use the hint as a default).  That means as you get deeper
        into the result set, you'll end up with bigger and bigger sets
        of results.

        Outside of the JDBC API, we have support for Scrollable Cursors,
        so that might be an option for you.

        -Tom

        Tim Hollosy wrote:

            I added the hint, now I don't see the rownum things at all
            in my paging queries. What's actually happening here, would
            all rows be brought back into memory or is JDBC filtering
            them out?

            I suspect I have some bad stats somewhere that are causing
            the CBO to go nuts with the first_rows on, and I just need
            to figure out which one and tweak it. That would be
            preferable to me than making such a large change globally in
            our app, unless of course I could tell Eclipselink to use
            rownum filtering, just without the hint....

            ./tch


            On Thu, Jun 16, 2011 at 4:07 PM, Tim Hollosy
            <hollosyt@xxxxxxxxx <mailto:hollosyt@xxxxxxxxx>
            <mailto:hollosyt@xxxxxxxxx <mailto:hollosyt@xxxxxxxxx>>> wrote:

               Won't that not use rownum filtering for pagination
            though, then how
               would it paginate?

               ./tch



               On Thu, Jun 16, 2011 at 3:52 PM, Tom Ware
            <tom.ware@xxxxxxxxxx <mailto:tom.ware@xxxxxxxxxx>
               <mailto:tom.ware@xxxxxxxxxx
            <mailto:tom.ware@xxxxxxxxxx>>> wrote:

                   Hi Tim,

                    Try using a session customizer and calling:
((DatabasePlatform)session.____getDatasourcePlatform()).____setShouldUseRownumFiltering(____false) 

                   -Tom

                   Tim Hollosy wrote:

                       We've run into a situation where the FIRST_ROWS
            hint is
                       causing major pain, selecting against a view.
            Worked fine on
                       our 10g database, production is 9i though and
            it's just
                       dying on 9i, any clue on how to tell eclipselink
            to leave
                       the hint out? When I leave it out, the query runs
            fine. I
                       saw a post from 2009 that said it wasn't possible
            to disable
                       it without modifying EL source.

                       As an alternative, any oracle wizards now how I
            might tell
                       Oracle to ignore that hint?

                       Thanks,
                       Tim
------------------------------____----------------------------__--__------------ 

                       ___________________________________________________
                       eclipselink-users mailing list
                       eclipselink-users@xxxxxxxxxxx
            <mailto:eclipselink-users@xxxxxxxxxxx>
                       <mailto:eclipselink-users@__eclipse.org
            <mailto:eclipselink-users@xxxxxxxxxxx>>
https://dev.eclipse.org/____mailman/listinfo/eclipselink-____users 
            <https://dev.eclipse.org/__mailman/listinfo/eclipselink-__users>
<https://dev.eclipse.org/__mailman/listinfo/eclipselink-__users 
            <https://dev.eclipse.org/mailman/listinfo/eclipselink-users>>

                   ___________________________________________________
                   eclipselink-users mailing list
                   eclipselink-users@xxxxxxxxxxx
            <mailto:eclipselink-users@xxxxxxxxxxx>
            <mailto:eclipselink-users@__eclipse.org
            <mailto:eclipselink-users@xxxxxxxxxxx>>
https://dev.eclipse.org/____mailman/listinfo/eclipselink-____users 
            <https://dev.eclipse.org/__mailman/listinfo/eclipselink-__users>
<https://dev.eclipse.org/__mailman/listinfo/eclipselink-__users 
            <https://dev.eclipse.org/mailman/listinfo/eclipselink-users>>




            ------------------------------__------------------------------__------------

            _________________________________________________
            eclipselink-users mailing list
            eclipselink-users@xxxxxxxxxxx
            <mailto:eclipselink-users@xxxxxxxxxxx>
            https://dev.eclipse.org/__mailman/listinfo/eclipselink-__users
            <https://dev.eclipse.org/mailman/listinfo/eclipselink-users>

        _________________________________________________
        eclipselink-users mailing list
        eclipselink-users@xxxxxxxxxxx <mailto:eclipselink-users@xxxxxxxxxxx>
        https://dev.eclipse.org/__mailman/listinfo/eclipselink-__users
        <https://dev.eclipse.org/mailman/listinfo/eclipselink-users>




------------------------------------------------------------------------

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

Back to the top