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
------------------------------
Original Message:
Sent: Wed November 22, 2023 03:07 PM
From: Jan Nelken
Subject: Querying column populated by generate_unique()
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
Original Message:
Sent: Wed November 22, 2023 08:27 AM
From: Snorri Bergmann
Subject: Querying column populated by generate_unique()
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
Original Message:
Sent: Wed November 22, 2023 08:08 AM
From: Jan Nelken
Subject: Querying column populated by generate_unique()
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
Original Message:
Sent: Wed November 22, 2023 06:01 AM
From: Snorri Bergmann
Subject: Querying column populated by generate_unique()
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
------------------------------