The below was the Database(SQL Server/Oracle) question asked by Interviewer to me.
How do i call a specific index In case if i have multiple indexes?
I have no idea on it. I searched in google but failed.
Asked
Active
Viewed 272 times
-4
Billa
- 5,226
- 23
- 61
- 105
-
2Index hints. That's it. One question per post. – ta.speot.is Feb 06 '13 at 11:50
-
These are two questions and so must be two posts. Especially as they're not related to each other, and so rreally demand separate answers. – APC Feb 06 '13 at 12:48
-
1@ta.speot.is now only 1 question – Billa Feb 06 '13 at 13:01
-
possible duplicate of [Forcing an index in oracle](http://stackoverflow.com/questions/7418860/forcing-an-index-in-oracle) – Klas Lindbäck Feb 06 '13 at 13:31
-
Refer Table Hints here http://msdn.microsoft.com/en-us/library/ms181714(v=sql.105).aspx. Apply Table Hint like `OPTION (TABLE HINT ( e, INDEX( IX_Index_Name) , NOLOCK, FORCESEEK ))` – Murali Murugesan Mar 26 '13 at 09:00
1 Answers
2
You can provide hints to the optimiser.
Index hints look like this:
SELECT /*+ INDEX(<table name> <index name> <optional free text comment> */ field1, field2...
Example:
SELECT /*+ INDEX(patients sex_index) use sex_index because there are few
male patients */ name, height, weight
FROM patients
WHERE sex = 'm';
Klas Lindbäck
- 33,105
- 5
- 57
- 82
-
Can you give some scenario and elaborate it. showing me like a commented code. :( i am confusing – Billa Feb 06 '13 at 13:34
-
I have given an example telling you the syntax. If you add a specific SQL query and the name and table for the index you wish to use I can show you how to add the hint. – Klas Lindbäck Feb 06 '13 at 13:49