Re: [eclipselink-users] Oracle: Anyway to disable FIRST_ROWS hint on pag

[Tom Ware <tom.ware@xxxxxxxxxx>]

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@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