Systimestamp In oracle

Question

I am using below SQL statement. The below systimestamp displays the same seconds values for all the records. The table contains one million records. How can we displays the seconds or nano seconds variations for each records? OR any other methods to differentiate the seconds in time for each record?

SELECT ITEM,ITEM_NO,DESCRIPTON,SYSTIMESTAMP FROM ITEM_MASTER ORDER BY ITEM_NO;

`systimestamp` returns the current timestamp. So it'll be the same for each row the query returns. Do you want to select a different column from the table? People commonly add `create_timestamp` or `update_timestamp` columns to their tables to store when a row was created or updated. — Justin Cave, May 07 '23 at 04:41
actually i am trying to update the records in the same order how the select statement returns. But it is not happening. Suppose the select statement returns item_id 1,item_id 3 and item_id 4.i am using update statement after these select statement.The update statement is not updating in the same order how select statement returns.Update date time is not updating correctly based on these order — Ram, May 07 '23 at 09:54
Please include *all* of your related code, a reproduceable example, and your expected outcome. Note that `systimestamp` will return the current time that the query was executed, not the time that the row was returned. All rows will show the same timestamp as that is the time that the data was logically consistent. Your use of `order by` on `item_no` also appears to make the *time* of each row retrieval irrelevant. — pmdba, May 07 '23 at 10:31
Also, why is the order of update important? An `update` command will apply changes to all affected records *at the same time*, logically. The only way to apply updates in a specific order would be using a loop and processing them row-by-row - this is inherently *slow* and unscalable in comparison to batch operations like `update`. Is there a business requirement that makes order of update important? — pmdba, May 07 '23 at 10:39

MT0 · Accepted Answer · 2023-05-07T13:05:47.190

SYSTIMESTAMP returns the same time for all rows; it does not return the time each row was read from a data file or put into the result set. Therefore what you are asking is impossible.

If you want to have a unique identifier for the order that the rows were put into the result set then use the ROWNUM pseudo-column:

SELECT ITEM,
       ITEM_NO,
       DESCRIPTON,
       ROWNUM
FROM   ITEM_MASTER
ORDER BY ITEM_NO;

If you want the rows to be numbered in the same order as the output then order first and then apply ROWNUM:

SELECT t.*,
       ROWNUM
FROM   (
  SELECT ITEM,
         ITEM_NO,
         DESCRIPTON
  FROM   ITEM_MASTER
  ORDER BY ITEM_NO
) t;

or use the ROW_NUMBER analytic function:

SELECT ITEM,
       ITEM_NO,
       DESCRIPTON,
       ROW_NUMBER() OVER (ORDER BY item_no) AS rn
FROM   ITEM_MASTER
ORDER BY ITEM_NO;

If you want to convert that to a timestamp that is artificially incremented by a micro-second for each row then:

SELECT ITEM,
       ITEM_NO,
       DESCRIPTON,
       SYSTIMESTAMP + ROWNUM * INTERVAL '0.000001' SECOND AS rn_time
FROM   ITEM_MASTER
ORDER BY ITEM_NO;

actually i am trying to update the records in the same order how the select statement returns.

This appears to be an XY-problem; you do not need to use a time and the order of a result set is not guaranteed without an ORDER BY clause so if you want to use the ITEM_NO order then you already have a pre-existing column you can use to order the rows and do not need to artificially generate a "time" column.

Systimestamp In oracle

1 Answers1