Hi guys, simple use case:
CREATE TABLE RISKBOX.RISK.EXT_RATING_MIGRATION_DATA (
VALUE_DATE DATE NOT NULL
, ID_INSTRUMENT VARCHAR(40) NOT NULL
, ID_INSTRUMENT_TYPE VARCHAR(12) NOT NULL
, PRODUCT VARCHAR(40) NULL
, ID_COUNTER_PARTY VARCHAR(40) NULL
, ID_PORTFOLIO VARCHAR(40) NULL
, MASTER_RATING VARCHAR(2) NULL
, RISK_CLASSIFICATION VARCHAR(10) NULL
, OVERDUE_DAYS DECIMAL(4) NULL
, DEFAULT_FLAG VARCHAR(1) NULL
, ID_CURRENCY VARCHAR(3) NOT NULL
, BOOK_VALUE DECIMAL(23,5) NOT NULL
, UNDRAWN_AMOUNT DECIMAL(23,5) NOT NULL
);
CREATE INDEX EXT_RATING_MIGRATION_DATA_IDX1
ON RISKBOX.RISK.EXT_RATING_MIGRATION_DATA(VALUE_DATE, MASTER_RATING);
CREATE UNIQUE INDEX EXT_RATING_MIGRATION_DATA_IDX2
ON RISKBOX.RISK.EXT_RATING_MIGRATION_DATA(VALUE_DATE, ID_INSTRUMENT);
CREATE INDEX EXT_RATING_MIGRATION_DATA_IDX3
ON RISKBOX.RISK.EXT_RATING_MIGRATION_DATA(VALUE_DATE);
Table has 1 Mill. rows, I have 16 cores with 32 GB RAM and SSD. 8GB case
assigned to the H2 database.
However, the following query NEVER returns and also getting the EXPLAIN does
never return:
EXPLAIN ANALYZE SELECT ex.VALUE_DATE
, a.ID_INSTRUMENT
, a.OVERDUE_DAYS
, a.DEFAULT_FLAG
, a.BOOK_VALUE
FROM risk.ext_rating_migration_data ex
INNER JOIN risk.ext_rating_migration_data a
ON a.value_date = ex.value_date;
--> no results even after an hour, I can't even get the query plan. Neither any
results.
I have tried to fully analyze the DB already, compacted it. Tried many times,
various statements.
As long as I join *two different tables, performance is good*. As soon as I
*join a table with itself, I do not get results*.
H2 Version is recent 1.4.201-pre.
Any idea what the problem could be and how to solve it?
Best regards
Andreas
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/22447f18-e7b7-4699-b2fa-fdc8c360e140%40googlegroups.com.
