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