0

I have a query joining lots of fields. For some strange reason the index for one table is not being used at all( I use the index key clearly), instead it is doing a FULL table scan. I would like to force the index. We used to do optimizer hints in sybase. Is there a similar hint available in oracle? For example, in sybase to join tables a, b, c and use myindex in table a, I would do :

SELECT a.*
FROM     a(INDEX myindex),
         b,
         c
WHERE    a.field1 = b.field1
AND      b.field1 = c.field1

Question is how do I do this in oracle. Thanks Saro

markus
  • 6,258
  • 13
  • 41
  • 68
Saro Khatchatourian
  • 1,047
  • 3
  • 14
  • 24
  • 1
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements006.htm#SQLRF50405 – NullUserException Sep 14 '11 at 15:28
  • Could you post the query plan ? – Kevin Burton Sep 14 '11 at 15:28
  • 4
    It is not a good idea to try and outsmart the query optimizer. A short explanation: http://stackoverflow.com/questions/7358137/oracle-full-text-search-with-condition/7379753#7379753 – NullUserException Sep 14 '11 at 15:29
  • I've definitely seen situations where Oracle chose a full table scan when an index was more efficient, and vice versa. The first situation usually arose when selecting from a large table where most of the rows would *not* be selected, and the second situation usually arose when selecting from a large table where most of the rows *would* be selected. – Vanessa MacDougal Sep 14 '11 at 16:45
  • 1
    @Vanessa perhaps you needed to generate stats to handle the skewed distribution of data see: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:707586567563 rather than use a hint. – Kevin Burton Sep 14 '11 at 17:05

1 Answers1

4

Yes, there is a hint like that in Oracle. It looks something like this:

select /*+ index(a my_index) */ from a
Vanessa MacDougal
  • 982
  • 6
  • 14
  • You need the tablespec (see the [docs](http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements006.htm#SQLRF50405)); the index name is optional if you want to suggest Oracle uses an index but don't want to specify which one. So it should be `INDEX(a my_index)`. And if the table has an alias in the query, you have to use the alias as the tablespec. – Alex Poole Sep 14 '11 at 15:33
  • Thanks. So are you saying I should do something like this: SELECT /*+ index(a my_index) */ , a.* FROM a(INDEX myindex), b, c WHERE a.field1 = b.field1 AND b.field1 = c.field1 – Saro Khatchatourian Sep 14 '11 at 16:57
  • 1
    @Saro - no: `SELECT /*+ index(a my_index) */ a.* FROM a, b, c WHERE a.field1 = b.field1 AND b.field1 = c.field1`. (Preferably with ANSI joins, but that's another subject...) – Alex Poole Sep 14 '11 at 17:52