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?
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> 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> 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>> 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>> 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@eclipse.org>

           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@eclipse.org>

       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
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users


Back to the top