1 Reply Latest reply: Apr 24, 2009 4:59 PM by Ralf Becher RSS

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

    larymano

      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

       

       

       

       

       

       

       

       

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

          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