Db2

 View Only
  • 1.  Querying column populated by generate_unique()

    Posted Wed November 22, 2023 06:02 AM

    Hi.

    DB2 LUW 11.5.

    I have a table with > 230 million rows.

    One column in the table is INSERTED_AT CHAR(13) FOR BIT DATA NOT NULL and is populated with generate_unique(). There is an UNIQUE INDEX on that column.

    Now I need to find all records that have been inserted in the last hour. Is there a way to do this other than using timestamp(INSERTED_AT) >= ....?

    Using the timestamp() function causes a sequential scan but the query MUST use the index.

    Thanks in advance,

    -S



    ------------------------------
    Snorri Bergmann
    ------------------------------


  • 2.  RE: Querying column populated by generate_unique()

    IBM Champion
    Posted Wed November 22, 2023 08:09 AM
    Edited by Jan Nelken Wed November 22, 2023 08:13 AM

    Did you try whether this construct:

    where INSERTED_AT > CURRENT TIMESTAMP - 1 HOUR

    also generates table scan?

    Other option may require INSERT trigger inserting CURRENT TIMESTAMP into IMPLICITLY HIDDEN column TIME_OF_INSERT of type timestamp reducing predicate to:

    where INSERTED_AT > TIME_OF_INSERT - 1 HOUR

    ------------------------------
    Jan Nelken
    ------------------------------



  • 3.  RE: Querying column populated by generate_unique()

    Posted Wed November 22, 2023 08:27 AM
    Edited by Snorri Bergmann Wed November 22, 2023 08:27 AM

    Hi Jan and thanks.

    This query also generates a table scan.

    Unfortunately, I'm unable to modify the trigger (that actually assigns generate_unique) or to create a functional index on timestamp(INSERTED_AT) (if that's even possible).

    Regards,

    -S



    ------------------------------
    Snorri Bergmann
    ------------------------------



  • 4.  RE: Querying column populated by generate_unique()

    IBM Champion
    Posted Wed November 22, 2023 03:08 PM
    Edited by Jan Nelken Wed November 22, 2023 03:11 PM

    Hi!

    I played with this example a little:

    1. I used table T1 populated with over 81 millions rows

    2. I created unique index I1 on INSERTED_AT column

    3. Simple query:

    select
      count(*)
    from
      t1
    where
      inserted_at < x'202311221912'

    *is using* index I1 only:

    Access Plan:
    -----------
            Total Cost:             31543.5
            Query Degree:           1


          Rows
         RETURN
         (   1)
          Cost
           I/O
           |
            1
         GRPBY
         (   2)
         31543.5
         32606.1
           |
      9.78026e+006
         IXSCAN
         (   3)
         31354.8
         32606.1
           |
      8.13315e+007
     INDEX: DB2ADMIN
           I1
           Q1

    In your case depending on what are you selecting optimizer may choose table scan - if cost of tablescan is deemed to be less than cost of index scan plus cost of retrieving qualified rows.

    I choose '<' just for convenience - you may use '>' to find rows inserted in last hour (just build literal to compare with properly).



    ------------------------------
    Jan Nelken
    ------------------------------



  • 5.  RE: Querying column populated by generate_unique()

    Posted Thu November 23, 2023 05:22 AM

    Thanks Jan for your help.

    Indeed,  inserted_at >= x'202311221912' uses the index and gives the desired results.

    However, I am a bit confused how to use this with a timestamp. I have tried

    inserted_at >= hex(VARCHAR_FORMAT(CURRENT TIMESTAMP - 1 HOUR, 'YYYYMMDDHH24MI')) 

    but that obviously does not work because 

    SELECT hex('202311230819'), x'202311230819' FROM SYSIBM.SYSDUMMY1;

    Gives totally different results (the former a hexadecimal string, the latter some binary data). Is there a function I can use instead of x'string' ?

    I really need to this on the sql-level (rather than writing a program).

    Best regards,

    -S



    ------------------------------
    Snorri Bergmann
    ------------------------------



  • 6.  RE: Querying column populated by generate_unique()

    Posted Thu November 23, 2023 05:43 AM

    I found it:

    varchar_bit_format(VARCHAR_FORMAT(CURRENT TIMESTAMP - 1 HOUR, 'YYYYMMDDHH24MI'));

    gives the desired results and uses the index.

    Thank you again for your help.

    Best regards,

    -Snorri



    ------------------------------
    Snorri Bergmann
    ------------------------------