Using Hint to optimizing the Oracle SQL Query? How about SQL SERVER or TERADATA

[#1]

Hi,

Could I include a hint like /*+ FIRST_ROWS */ in the SQL SELECT in case of Oracle as follows inorder to speed up the query processing because one of the query takes around 2 hours to complete.

LOAD *

SQL SELECT /*+ FIRST_ROWS */ A.*,B.*
FROM "Sample".table
WHERE.....

Would this reduce the processing time of the sql query to less than this time.

Apart from this, Is there any other way to optmize the query

In case of SQL SERVER or TERADATA, how we could optimize the query?

Any suggestions is appreciated

[rbecher]

Lary,

it depends. Oracle´s FIRST_ROWS hint leads into nested loop joins (on indexes) which could be a big decrease of performance. If you have Oracle 9i or higher you could try FIRST_ROWS(100) which will process faster while using hash joins.

I would optimize the query in Oracle first (sqlplus or toad, explain plan etc.) w/o hints. Keep in mind that you will retrieve all rows in the QV load, not only "first rows". So, processing time of the whole result set is crucial.

Maybe something is wrong with your join condition (between tables A and B) and/or missing indexes...

Ralf