Oracle query optimization for select-CodePudding

Below is my query and i want to know if any optimization is possible in this query or not ?

FYI, I have applied index on C_NUMBER , CA_NUMBER and D_TIMESTAMP column.

SELECT
  NAM, RSON, URL
FROM TM_CAM
WHERE C_NUMBER = A_C_NUMBER
  AND CA_NUMBER = A_CA_NUMBER
  AND (SYSTIMESTAMP) <= D_TIMESTAMP
ORDER BY D_TIMESTAMP DESC
FETCH FIRST 1 ROWS ONLY

There is a ORDER BY so i think its not good from a performance perspective.

I tried below but it has degraded performance.

SELECT
  NAM, RSON, URL
FROM TM_CAM
WHERE D_TIMESTAMP = (
  SELECT MAX(D_TIMESTAMP )
  FROM TM_CAM
  )
  AND C_NUMBER = A_C_NUMBER
  AND CA_NUMBER = A_CA_NUMBER
  AND (SYSTIMESTAMP) <= D_TIMESTAMP

Any inputs please ?

CodePudding user response：

Sorting slows things down, that's for sure.

Your 1st query looks OK to me; how many rows are involved? What kind of optimization do you expect? I guess you're concerned about execution time; if so, how long does that query take? Did you check explain plan?

Meanwhile, try

WITH
   temp
   AS
      (SELECT nam,
              rson,
              url,
              ROW_NUMBER () OVER (ORDER BY d_timestamp DESC) rn
         FROM tm_nam
        WHERE     c_number = a_c_number
              AND ca_number = a_ca_number
              AND SYSTIMESTAMP <= d_timestamp)
SELECT nam, rson, url
  FROM temp
 WHERE rn = 1;

WITH
   temp
   AS
      (  SELECT nam, rson, url
           FROM tm_nam
          WHERE     c_number = a_c_number
                AND ca_number = a_ca_number
                AND SYSTIMESTAMP <= d_timestamp
       ORDER BY d_timestamp DESC)
SELECT nam, rson, url
  FROM temp
 WHERE ROWNUM = 1;

CodePudding user response：

Your first and second queries are not the same as the second query is the equivalent of:

SELECT NAM, RSON, URL
FROM   TM_CAM
WHERE  C_NUMBER     =  A_C_NUMBER
AND    CA_NUMBER    =  A_CA_NUMBER
AND    SYSTIMESTAMP <= D_TIMESTAMP
ORDER BY D_TIMESTAMP DESC
FETCH FIRST ROW WITH TIES

You could rewrite it using ROWNUM:

SELECT *
FROM   (
  SELECT NAM, RSON, URL
  FROM   TM_CAM
  WHERE  C_NUMBER     =  A_C_NUMBER
  AND    CA_NUMBER    =  A_CA_NUMBER
  AND    SYSTIMESTAMP <= D_TIMESTAMP
  ORDER BY D_TIMESTAMP DESC
)
WHERE  ROWNUM = 1;

But if you compare the execution plan you will probably find that Oracle uses very similar (if not identical) plans for FETCH FIRST ROW ONLY, filtering on ROWNUM = 1 or for filtering on the ROW_NUMBER() analytic function so it is likely that you can pick whichever query is easiest to maintain and use that.

fiddle