0

I tried executing the below Query as a NativeSql query and I see it works well without any error and I could retrieve the the records. But the issue is that Oracle hint LEADING is not recognized.

When I copy the query and run it directly in SQL Developer it works well with the hint and the order of this result differs from the order of results I received from application.

https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#BABJIHCI

SELECT /*+ LEADING(e j) */ e.name, d.department FROM
employees e, departments d, job_history j
WHERE e.department_id = d.department_id
AND e.hire_date = j.start_date;

The issue is that the use of hint LEADING is silently ignored by hibernate and so it produces the results in the order as If no hint is used. So the question is how and why the hint LEADING is silently ignored by application?

Alagammal P
  • 829
  • 5
  • 19
  • 43
  • Are the results of these queries different ? Or rather do you mean **the plans** of both queries are different? The first case - different results - is unlikely, and if so, it is not caused by the hint. – krokodilko May 08 '19 at 18:04
  • Yes, the order of the results are different. I copied the same query(with hint) from logs and I executed on SQL Developer and the result is as expected. If I remove the hint and execute same query on SQL developer then I see it gives the same order of results as I get via application. – Alagammal P May 08 '19 at 19:09
  • 1
    **the order of the results are different** - well, if you want to get rows in some order you **must use ORDER BY clause**, there is no other way. I vote to close your question as a duplicate of these answers (only a few there are more) [answer 1](https://stackoverflow.com/questions/43434906/how-to-fetch-continuous-occurrence-count-of-a-column-value-in-sql/43435277#43435277), [answer 2](https://stackoverflow.com/questions/47959568/set-order-of-column-by-sequence-sql/47959780#47959780), [answer 3](https://stackoverflow.com/questions/45943409/rolling-moving-median-in-greenplum/45945833#45945833) – krokodilko May 08 '19 at 19:46
  • Possible duplicate of [how to fetch continuous occurrence count of a column value in sql?](https://stackoverflow.com/questions/43434906/how-to-fetch-continuous-occurrence-count-of-a-column-value-in-sql) – krokodilko May 08 '19 at 19:47
  • The issue is that the use of Hint is silently ignored by hibernate and so it produces the results in the order as If no hint is used. So the question is how and why the hint is silently ignored by application? – Alagammal P May 09 '19 at 05:09
  • do you have `use_sql_comments=true` in hibernate configuration ? if not try enabling it. As hints are treated as sql comments. – Amit Naik May 09 '19 at 07:48
  • I tried by adding use_sql_comments=true and it doesnt work. Meaning the hint is still ignored. The hint is igonred even when I dodnt enable "use_sql_comments" – Alagammal P May 09 '19 at 08:59

0 Answers0