B
bartroose
Guest
We would very much appreciate the possibility to have the ORDERED hint present by default in every join (perhaps to enable with a parameter like -addorderedhint). Here's why: One can assume that an ABL OPEN QUERY or FOR EACH always starts with the most effective/selective table in the join: FOR EACH A… WHERE ..(Aclauses) (EACH/FIRST) B WHERE AND should be more selective than the . When dataserver translates this to SQL we get something like: SELECT * FROM A, B WHERE AND AND The hierarchic, procedural structure from the FOR EACH is gone and the Oracle optimizer in some cases starts with table B as driving table for the join. One can hint Oracle to honour the ordering in the FROM clause with the ‘ORDERED’ hint (or QUERY-TUNING(ORDERED-JOIN)), but it sure would be a nice thing if dataserver could always (or configurable by parameter) add the ORDERED hint. By default, there is no hint present in the dataserver-generated sql that establishes a join (no index hint). When the oracle optimizer evaluates the sql, it sometimes starts with the second or third table in the FROM clause. Assuming that the ABL developer did his job properly and started the FOR EACH with the most selective table selection, the ORDERED hint would be a natural thing to add. This would make life a whole lot easier and avoid a lot of analyzing queries using sqltrace/tkprof
Continue reading...
Continue reading...