Forum Stats

  • 3,781,547 Users
  • 2,254,529 Discussions
  • 7,879,751 Comments

Discussions

KEY_COLUMN ='VALUE' and DATE_COLUMN < SYSTIMESTAMP AT TIME ZONE 'UTC'

rcc50886
rcc50886 Member Posts: 474 Bronze Badge

we are saving UTC timezone time into a oracle table. The column is defined as TIMESTAMP only.

We insert UTC time into this column.

We need to query to select all records which has a specific key value and timestamp less than the current UTC time.

select * from TABLE

where KEY_COLUMN ='VALUE' and DATE_COLUMN < SYSTIMESTAMP AT TIME ZONE 'UTC' ;


We have composite index on KEY_COLUMN and DATE_COLUMN.


It's using range scan on this index but it is using only KEY_COLUMN to access and using DATE_COLUMN for filter.


 16 - access("KEY_COLUMN"='VALUE')

    filter(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("DATE_COLUMN"))<SYS_EXTRACT_UTC(SYSTIMESTAMP(6) AT TIMEZONE 'UTC'))


As you see above, oracle applying an INTERNAL_FUNCTION.


I understand that TIMESTAMP datatype column doesn't save TIMEZONE, so my input will be TIMESTAMP WITH TIMEZONE as i am using SYSTIMESTAMP AT TIME ZONE 'UTC' .


What will be the best solution to use index effectively ?


This is within pl/sql procedure, can i use a variable of type timestamp and use it for query ?


date_variable timestamp ;

select SYSTIMESTAMP AT TIME ZONE 'UTC' into date_variable ;


select * from TABLE

where KEY_COLUMN ='VALUE' and DATE_COLUMN < date_variable ;

Best Answers

  • mathguy
    mathguy Member Posts: 10,221 Blue Diamond
    edited Nov 22, 2021 5:29PM Accepted Answer

    You are comparing your timestamp column to a timestamp with time zone. YOU know that you can discard the time zone info from the right-hand side, but Oracle doesn't; it converts your column values to timestamp with time zone before performing the comparison, and that prevents the full use of the index.

    Help the optimizer by converting the right-hand side back to a simple timestamp:

    ... < cast(systimestamp at time zone 'UTC' as timestamp)
    
    Jonathan Lewis
  • User_H3J7U
    User_H3J7U Member Posts: 814 Gold Trophy
    Accepted Answer

    First result will cast timestamp to timestamptz with session time zone. If session time zone differs from a OS time zone, then result will be different.

    alter session set time_zone='+05:15';  -- systimestamp is +03:00
    with t(t) as (select cast(systimestamp as timestamp) from dual)
    select 'no   cast' how, systimestamp at time zone 'UTC' - t diff from t union all
    select 'with cast' how, cast(systimestamp at time zone 'UTC' as timestamp) - t diff from t;
    
    HOW       DIFF               
    --------- -------------------
    no   cast +00 02:15:00.000000
    with cast -00 03:00:00.000000
    
«1

Answers

  • mathguy
    mathguy Member Posts: 10,221 Blue Diamond
    edited Nov 22, 2021 5:29PM Accepted Answer

    You are comparing your timestamp column to a timestamp with time zone. YOU know that you can discard the time zone info from the right-hand side, but Oracle doesn't; it converts your column values to timestamp with time zone before performing the comparison, and that prevents the full use of the index.

    Help the optimizer by converting the right-hand side back to a simple timestamp:

    ... < cast(systimestamp at time zone 'UTC' as timestamp)
    
    Jonathan Lewis
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,848 Gold Crown


    Are you allowed to create a new (alternative) index on the table?

    Demo on 12.2 (since you didn't mention a version and timestamp behaviour is still evolving):

    create index t1_i2 on t1(key_column , sys_extract_utc(date_value));
    
    SQL> set autotrace traceonly explain
    SQL> select small_vc from t1 where key_column = '    10' and date_value < current_timestamp at time zone 'UTC';
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3366011349
    
    ---------------------------------------------------------------------------------------------
    | Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                    |       |    39 |   975 |     3   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |    39 |   975 |     3   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN                  | T1_I2 |    39 |       |     2   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("KEY_COLUMN"='    10' AND SYS_EXTRACT_UTC(INTERNAL_FUNCTION("DATE_VALUE
                  "))<SYS_EXTRACT_UTC(CURRENT_TIMESTAMP(6) AT TIME ZONE 'ReUTC'))
    
    
    

    Regards

    Jonathan Lewis

  • rcc50886
    rcc50886 Member Posts: 474 Bronze Badge

    here is how the query looks like:



    SELECT COLA

    FROM  TABLE

    WHERE

        (

            (

                KEY_COLUMN IN ('VAL1', 'VAL2')

                AND COL_1_TMS < SYSTIMESTAMP AT TIME ZONE 'UTC'

            )

            OR

            (

                KEY_COLUMN IN ('VAL1', 'VAL2')

                AND COL_3 < :B2

            )

            OR

            (

                KEY_COLUMN IN ('VAL1', 'VAL2')

                AND COL_2_TMS < SYSTIMESTAMP AT TIME ZONE 'UTC'

            )

        )

        AND ROWNUM < :B1 +1 FOR UPDATE OF COLB ;

    we have 3 composite indexes on (KEY_COLUMN, COL_1_TMS), (KEY_COLUMN, COL_3) and (KEY_COLUMN, COL_2_TMS)


    -------------------------------------------------------------------------------------------------------------------

    | Id | Operation             | Name             | Rows | Bytes | Cost (%CPU)| Time   |

    -------------------------------------------------------------------------------------------------------------------

    |  0 | SELECT STATEMENT         |                | 2156K|  84M|  398K (1)| 01:19:45 |

    |  1 | FOR UPDATE            |                |    |    |      |     |

    |  2 |  BUFFER SORT           |                |    |    |      |     |

    |* 3 |  COUNT STOPKEY         |                |    |    |      |     |

    |  4 |   CONCATENATION         |                |    |    |      |     |

    |* 5 |   FILTER            |                |    |    |      |     |

    |  6 |    INLIST ITERATOR       |                |    |    |      |     |

    |  7 |    TABLE ACCESS BY INDEX ROWID| TABLE             | 1858K|  72M| 83334  (1)| 00:16:41 |

    |* 8 |     INDEX RANGE SCAN     | IDX1  |  334K|    | 3343  (1)| 00:00:41 |

    |* 9 |   FILTER            |                |    |    |      |     |

    | 10 |    INLIST ITERATOR       |                |    |    |      |     |

    |* 11 |    TABLE ACCESS BY INDEX ROWID| TABLE     | 1138 | 46658 |  186  (1)| 00:00:03 |

    |* 12 |     INDEX RANGE SCAN     | IDX2  | 1152 |    |  164  (1)| 00:00:02 |

    |* 13 |   FILTER            |                |    |    |      |     |

    | 14 |    INLIST ITERATOR       |                |    |    |      |     |

    |* 15 |    TABLE ACCESS BY INDEX ROWID| TABLE     |  296K|  11M|  315K (1)| 01:03:03 |

    |* 16 |     INDEX RANGE SCAN     | IDX3  |  300K|    | 50169  (1)| 00:10:03 |

    -------------------------------------------------------------------------------------------------------------------


    Predicate Information (identified by operation id):

    ---------------------------------------------------


      3 - filter(ROWNUM<TO_NUMBER(:B1)+1)

      5 - filter(ROWNUM<TO_NUMBER(:B1)+1)

      8 - access(("KEY_COLUMN"=:B4 OR "KEY_COLUMN"=:B3) AND "COL_3"<:B2)

      9 - filter(ROWNUM<TO_NUMBER(:B1)+1)

     11 - filter(LNNVL("KEY_COLUMN"=:B4) AND LNNVL("KEY_COLUMN"=:B3) OR LNNVL("COL_3"<:B2))

     12 - access("KEY_COLUMN"='VAL1' OR "KEY_COLUMN"='VAL2')

        filter(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("COL_1_TMS"))<SYS_EXTRACT_UTC(SYSTIMESTAMP(6) AT TIME ZONE 'UTC'))

     13 - filter(ROWNUM<TO_NUMBER(:B1)+1)

     15 - filter((LNNVL("KEY_COLUMN"='VAL1') AND LNNVL("KEY_COLUMN"='VAL2') OR

           LNNVL(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("COL_1_TMS"))<SYS_EXTRACT_UTC(SYSTIMESTAMP(6) AT TIME

           ZONE 'UTC'))) AND (LNNVL("KEY_COLUMN"=:B4) AND LNNVL("KEY_COLUMN"=:B3) OR LNNVL("COL_3"<:B2)))

     16 - access("KEY_COLUMN"='VAL1' OR "KEY_COLUMN"='VAL2')

        filter(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("COL_2_TMS"))<SYS_EXTRACT_UTC(SYSTIMESTAMP(6) AT TIME ZONE 'UTC'))

  • rcc50886
    rcc50886 Member Posts: 474 Bronze Badge

    This code is in a procedure, so it's easy to change the code rather than creating a new index.

    I am planning to use the CAST as mention in above post.

     cast(systimestamp at time zone 'UTC' as timestamp)
    


    Also, my other concern is about OR expansion. Very often oracle switching plans and not using OR expansion.


    Do we think it's better to rewrite this as UNION ALL ? Is this work's ?


    select COLA

    from

    (

    SELECT COLA,

        COLB

    FROM  TABLE

    WHERE (   KEY_COLUMN IN ('VAL1', 'VAL2')

         AND COL_1_TMS < cast(systimestamp at time zone 'UTC' as timestamp)

        )

    UNION ALL

    SELECT COLA,

        COLB

    FROM  TABLE

    WHERE (   KEY_COLUMN IN ('VAL1', 'VAL2')

         AND AND COL_3 < :B2

        )

    UNION ALL

    SELECT COLA,

        COLB

    FROM  TABLE

    WHERE (   KEY_COLUMN IN ('VAL1', 'VAL2')

         AND COL_2_TMS < cast(systimestamp at time zone 'UTC' as timestamp)

        )

    ) WHERE ROWNUM < :B1 +1 FOR UPDATE OF COLB ;

  • User_H3J7U
    User_H3J7U Member Posts: 814 Gold Trophy

    Stored timestamp have a datatype 180. cast as timestamp returns an expression timestamp datatype 187. Function sys_extract_utc() returns a true timestamp datatype 180.

    sys_extract_utc may be not better, but it's shorter.

  • rcc50886
    rcc50886 Member Posts: 474 Bronze Badge

    It's using both columns to access the index by using - cast(systimes tamp at time zone 'UTC' as timestamp)

    Do you think below two conditions are not equivalent.

    1) KEY_COL='VAL' and COL_TIMESTAMP < systimestamp at time zone 'UTC'

    2) KEY_COL='VAL' and COL_TIMESTAMP < cast(systimes tamp at time zone 'UTC' as timestamp)


    Doesn't 1 and 2 results same ?

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,848 Gold Crown

    You haven't said which version you're running - but since your plan shows CONCATENATION it looks like it's 12.1.0.2 or earlier (OR_EXPAND didn't appear until 12.2.0.1), and the version matters. The thing about Oracle, though, is that sometimes a feature that's available for a SELECT isn't necessarily available for an UPDATE - so you shouldn't leave us guessing.

    If you look at the predicate section of the plan you will notice that there are a number of lnnvl() predicates confusing the issue. This is to deal with the possibility that the second branch of the concatenation may identify rows already identified in the first branch, and the third branch may identify rows already identified by the first and second branches. The lnnvl() specifically is to handle the case where the columns involved are allowed to be NULL - so if you write an explicit UNION ALL you have to cater for them as well.

    Another detail about the plan is that there's only one "COUNT STOPKEY" - this means that Oracle MIGHT get all the data from each branch, and then use the "rownum" predicate, and you'd have to check that with a real run-time test after enabling rowsource execution stats. Some versions are clever enough to do make the STOPKEY happen early but it may be necessary in a manual case to include the same rownum in every branch.

    ANother detail about the execution plan - the row estimates for the access predicates are very large (in excess of 300K in two of the cases). The instability of the plans may be due to bad statistics, and you might find that carefully controlled statistics will stop the plan changing. Some indication of total data size, selectivity of key_column and the values of interest might give us some ideas for helpful suggestions.

    Can you give us an idea of how many rows this query is supposed to select for update and way it's it's selecting a subset? It's a little odd to update after a "rownum" predicate, since it means the rows you update are effectively a random subset of all the candidate rows.

    Regards

    Jonathan Lewis

  • User_H3J7U
    User_H3J7U Member Posts: 814 Gold Trophy
    Accepted Answer

    First result will cast timestamp to timestamptz with session time zone. If session time zone differs from a OS time zone, then result will be different.

    alter session set time_zone='+05:15';  -- systimestamp is +03:00
    with t(t) as (select cast(systimestamp as timestamp) from dual)
    select 'no   cast' how, systimestamp at time zone 'UTC' - t diff from t union all
    select 'with cast' how, cast(systimestamp at time zone 'UTC' as timestamp) - t diff from t;
    
    HOW       DIFF               
    --------- -------------------
    no   cast +00 02:15:00.000000
    with cast -00 03:00:00.000000
    
  • rcc50886
    rcc50886 Member Posts: 474 Bronze Badge
    edited Nov 22, 2021 9:11PM

    I am using 11.2.0.4 version. It will be upgraded to 19.12.0 next year but we currently on 11204.


    It returns 0-60 records for update. Here are the stats per exec. With current version of query, it's doing 100k buffer gets per execution. Our goal is to reduce the CPU usage of this sql.


                                 
    
       EXECS    IO_EXEC   CPU_EXEC   ROWS_EXEC  Elapsed_Exec
    
    ---------- ---------- --------- ---------- ---------- 
    
      115,882   100213.689  3.0036   13.6293   3.1993
    
    


    By using cast function, i see significant improvement.

    COL_2_TMS < cast(systimestamp at time zone 'UTC' as timestamp)

    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
           1857  consistent gets
              1  physical reads
            424  redo size
            716  bytes sent via SQL*Net to client
            524  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
             12  rows processed
    
    
    


    But the main problem is i need to create a baseline to pin this plan even after changing the code. Oracle chnagin plan very often. Does it make sense to use UNION as follow's> does both of them are going to provide same result's ? Both COLA and KEY_COLUMN are not null.

    SELECT COLA
    FROM  TABLE
    WHERE
        (
            (
                KEY_COLUMN IN ('VAL1', 'VAL2')
                AND COL_1_TMS < SYSTIMESTAMP AT TIME ZONE 'UTC'
            )
            OR
            (
                KEY_COLUMN IN ('VAL1', 'VAL2')
                AND COL_3 < :B2
            )
            OR
            (
                KEY_COLUMN IN ('VAL1', 'VAL2')
                AND COL_2_TMS < SYSTIMESTAMP AT TIME ZONE 'UTC'
            )
        )
        AND ROWNUM < :B1 +1 FOR UPDATE OF KEY_COLUMN ;
    
    		
    
    	Planning to reqrite as follows:
    	
    
    select COLA
    from
    (			
    SELECT COLA,
        KEY_COLUMN
    FROM  TABLE
    WHERE (   KEY_COLUMN IN ('VAL1', 'VAL2')
         AND COL_1_TMS < cast(systimestamp at time zone 'UTC' as timestamp)
           )
    UNION ALL
    SELECT COLA,
        KEY_COLUMN 
    FROM  TABLE
    WHERE (   KEY_COLUMN IN ('VAL1', 'VAL2')
         AND AND COL_3 < :B2
        )
    UNION ALL
    SELECT COLA,
        KEY_COLUMN 
    FROM  TABLE
    WHERE (   KEY_COLUMN IN ('VAL1', 'VAL2')
         AND COL_2_TMS < cast(systimestamp at time zone 'UTC' as timestamp)
        )
    ) WHERE ROWNUM < :B1 +1 FOR UPDATE OF KEY_COLUMN ; 
    
    
    


  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,848 Gold Crown

    Have you tried the rewrite - even a simplified version?

    Since it's a "select for update" it will probably raise this error:

    ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
    

    You could try adding a use_concat hint to the original form (corrected for timestamp details) of the code; this might force it to concatenation. It may be necessary to use /*+ use_concat(1) */ or /*+ use_concat(8) */ rather than just /*+ use_concat */

    Regards

    Jonathan Lewis