Very Long Running SQL - Urgent

Yes, you should use /*+ ... */ if you want the hint to realy be a hint to database engine.

Your explain plan probably didn't show any difference as the provided syntax for the hint was wrong (there might be other reasons, though), so Oracle treated it as a normal comment. The correct syntax is:

SELECT /*+ INDEX(table_name index_name) */ .....

Note that there is no comma between table name and index name inside the hint.

Oracle 8.1.6 NT

Thanks for all your replies,

We have now discovered other queries failing to run correctly and have identified that the problem lies with PS_SHIP_INF_INV table.

All things being equal, and with no impact on other queries, can anyone suggest what columns should be in an index on that table to assist it to run more successfully.

We are using a cost based optimizer so I would rather not hint the index.

Many thanks in advance

Allie

Quote:

---

Originally posted by helpme
You can force an index like:

SELECT /*+ INDEX(table_name, index _name) */ column1,column2 FROM table WHERE column LIKE '%SAM%'

---

Your hint syntax is wrong. It's /*+ INDEX(table_alias index_name) */

(note the space between you table alias and your index name)

Quote:

---

Originally posted by jmodic
Yes, you should use /*+ ... */ if you want the hint to realy be a hint to database engine.

Your explain plan probably didn't show any difference as the provided syntax for the hint was wrong (there might be other reasons, though), so Oracle treated it as a normal comment. The correct syntax is:

SELECT /*+ INDEX(table_name index_name) */ .....

Note that there is no comma between table name and index name inside the hint.

---

Doh - I should be the head of the office of redundancy office.

Thanks,

I got that to work.

But I would rather not hint the index if possible, I would like to create a new index that would speed up this query specifically.

I was wondering based on the query above and the execution plan what would the best columns and in which order should they be in the index.

Kind thanks

Alison