1

I'm new to the concept of using the hint optimizer in oracle and I would like to understand if I'm using the hint FULL properly

I have a query 

SELECT COUNT(*) FROM MyTable
WHERE MyTable.name='RandomName'

So let's say the result got me 12345

Now if I want to use the hint FULL , is that how it supposed to be written below ? 

SELECT /*+ FULL(e) */ count(*) 
FROM MyTable e
 WHERE MyTable.name='RandomName'

The result is also 12345 is that normal?

Thank you

napi15
  • 2,354
  • 2
  • 31
  • 55
  • 2
    That appears correct. Hints don't (well, barring bugs) change the results of a query, just the query plan. You'd really want to see the query plan to determine whether the hint is correctly formatted in general. Assuming there is an index on `name` and the first query used that index, the query plan for the second query should show you that the plan changed to a table scan. If there was a problem with the hint, the plan wouldn't change. – Justin Cave Nov 08 '16 at 00:37

1 Answers1

2

Yes, this is how would specify a hint to Oracle. In this case, it should result in a Full Table Scan (not using any index you may have on the name column).

And the result will of course be the same. If using indexes changed the result, it would make them pretty useless.

Thilo
  • 257,207
  • 101
  • 511
  • 656
  • Thanks a lot , very appreciated , I was not sure at first because while consulting the documentation the use of hints is very similar to the use of comments ( usually comments aren't supposed to be executed if you know what I mean ) but now I'm starting to get a better idea . Again thanks a lot ! – napi15 Nov 08 '16 at 00:42