I've seen that clause 'hint' does not work on baan c4 for SQL queries while programming.

We have been suffering a performance problem with some baan code which was forcing Oracle to use a wrong index because baan was creating a hint for the SQL.

I just discovered a work-around. Our problem was on this query located inside a Baan function:

select tfacr200.*, tccom010.cuno, tccom010.eded
from tfacr200 ,tccom010
where tfacr200._index6 = {:main.year, :main.btno, " ", 0, 0, :main.ttyp}
and tfacr200.cuno refers to tccom010
order by tfacr200._index1
selectdo
...
endselect

although index6 is being used in the where clause, the order by (which is required by the later code) was forcing Baan to introduce a hint so that Oracle would use index1 for the where clause, forcing a full scan on the table. This was taking several minutes to process the query.

index1 is composed of 6 fields, the first of them being tfacr200.ttyp, this is the modified SQL that has boosted the performance to less than 0.01 secs (it was more than 3 minutes before):

select tfacr200.*, tccom010.cuno, tccom010.eded
from tfacr200 ,tccom010
where tfacr200._index6 = {:main.year, :main.btno, " ", 0, 0, :main.ttyp}
and tfacr200.cuno refers to tccom010
order by tfacr200.ttyp, tfacr200._index1
selectdo
...
endselect

i.e., the sorting has not been altered but somehow specifying field tfacr200.ttyp alone prevented baan from introducing the hint and Oracle worked fine!


I hope this helps to somebody!

My 2 cents:
- Databse hints are only available in BaanERP
- As in your query the first six fields of index 6 are fixed you are able to use the order by on tfacr200._index6 as well. It is in your case giving the data exactly in the same oreder as in your situation.
;)

Kind regards,
Dick