selectoracle11gsysdatesystimestamp

Systimestamp In oracle

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;
Solution

  • 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.