I found an issue with the SQL command, the second table has a where clause
which is always true:
*where T3_T1_FID = T3_T1_FID *
The redacted file with the exception has it wrong, I made a mistake when
renaming the tables for sending here. Sorry for that. I will run my
application again with the correct statement and see if it's OK now.
Anyway, even the wrong SQL shouldn't cause this behavior so in case you
want to have a look into it, the explain plan (for the buggy query that was
used) says:
DELETE FROM "PUBLIC"."T1"
/* PUBLIC.PRIMARY_KEY_C: T1_ID IN(SELECT DISTINCT
T1_ID
FROM PUBLIC.T1
/* PUBLIC.T1_INDEX */
WHERE NOT EXISTS( (SELECT
NULL
FROM PUBLIC.T2
/* PUBLIC.CONSTRAINT_INDEX_9A0: T2_T1_FID = T1_ID */
WHERE T2_T1_FID = T1_ID)
UNION
(SELECT
NULL
FROM PUBLIC.T3
/* PUBLIC.CONSTRAINT_INDEX_1ED */
WHERE T3_T1_FID = T3_T1_FID)))
*/
WHERE "T1_ID" IN( SELECT DISTINCT
"T1_ID"
FROM "PUBLIC"."T1"
/* PUBLIC.T1_INDEX */
WHERE NOT EXISTS( (SELECT
NULL
FROM "PUBLIC"."T2"
/* PUBLIC.CONSTRAINT_INDEX_9A0: T2_T1_FID = T1_ID */
WHERE "T2_T1_FID" = "T1_ID")
UNION
(SELECT
NULL
FROM "PUBLIC"."T3"
/* PUBLIC.CONSTRAINT_INDEX_1ED */
WHERE "T3_T1_FID" = "T3_T1_FID")))
By the way, when stopping the process of deleting, the DB had 148GB. After
reopening the database and shutting it down with SHUTDOWN COMPACT, it has
1.4GB.
so 19. 11. 2022 v 11:25 odesílatel Noel Grandin <noelgran...@gmail.com>
napsal:
> What does
> EXPLAIN PLAN <your query>
> show?
>
> --
> You received this message because you are subscribed to a topic in the
> Google Groups "H2 Database" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/h2-database/DckQhsI73i0/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> h2-database+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/h2-database/CAFYHVnVEfV9czquzb-nXy4j8kWeytZN4AjXg7TpCoz0yA8wO-w%40mail.gmail.com
> <https://groups.google.com/d/msgid/h2-database/CAFYHVnVEfV9czquzb-nXy4j8kWeytZN4AjXg7TpCoz0yA8wO-w%40mail.gmail.com?utm_medium=email&utm_source=footer>
> .
>
--
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 h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/CAMOSmwGTPrdpWzV5zBGXzhFBC7PmQ3dF7UA%2B7hktScdN292nXg%40mail.gmail.com.
