How to switch off the generation of FIRST_ROWS sql hint in SQLDataSource?

[mpretorian]

Hi,

I'm using smartgwt-power-3.0p.2012-07-17, oracle-11g and several sql datasources that are used to fetch data with pagination.
The generated query looks like here:

SELECT * FROM (SELECT /*+ FIRST_ROWS(75) */ a.*, rownum myrownum FROM (
	SELECT COLUMN1,COLUMN2
	FROM TABLE1
	WHERE COLUMN1='value'
	ORDER BY 
	COLUMN1 DESC
) a where rownum <=76) WHERE myrownum >= 1

While analyzing the performance of one such query our DBA figured out that the FIRST_ROWS hint causes performance problems because of the Oracle bug (Bad cardinality in FIRST_K rows mode [ID 13594712.8]).

How can I switch off the generation of the FIRST_ROWS hint in an SQLDataSource?

Regards,
Mihnea

[Isomorphic]

You can use operationBinding.sqlPaging to set the paging strategy to something other than sqlLimit, then add your own rowNum limiting or just use the built-in jdbcScroll behavior.

[mpretorian]

How can I add my own rownNum limiting other than using customSQL tag?
I would like to still use the sqlLimit paging strategy, but without that hint.

[Isomorphic]

There would be no way to add your own rowNum-based limiting other than using the <customSQL> tag and there's not currently a way to turn off the hinting other than turning off generation of rowNum-based limiting in general.

[mpretorian]

The solution using <customSQL> looks to me harder to implement and to maintain.

Would it be possible to implement a new attribute like operationBinding.sqlPagingHint to be able to specify that no hint should be used?
If yes, could you please tell me when can be done and how much it costs?

Thanks,
Mihnea

[Isomorphic]

We have implemented a new flag, sqlUsePagingHint. You can set it at at DataSource or OperationBinding level; it defaults to true, so the intended usage is that you set it to false by exception for individual DataSources or operations that need paging hints switched off.

This is now committed in 3.1d, and will be present in nightly builds of that release as of tomorrow.

[mpretorian]

I tested it with smartgwtpower-3.1d-20121012 and works well.
Thanks!