You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
On other databases (same structure, same plan produced) on the same
server (different data of course) it works fine. Backup & restore didn't
help. I can provide the database, but given it's a customer data it
needs to be handled as private data.
The minimum query I can replicate it looks like this:
with DAYS
as (select distinct(DR.F_DA_DATE)
from T_DAILY_RAW DR
)
select count(*)
from T_EMAILS_WEBDB_RAW EWR
inner join DAYS DR on DR.F_DA_DATE = EWR.F_EWR_SENT
inner join T_EMAIL_TYPE_DIM EMTD on EMTD.F_EMT_VALUE = EWR.F_EWR_TYPE;
And the plan is "PLAN HASH (EMTD NATURAL, JOIN (SORT (DR DR NATURAL), EWR INDEX(IDX_EWR_SENT_A_TEMP)))".
The keys cache for hash join algorithm is limited by 1GB and seems to be reached in this case. Longish UTF-8 VARCHARs as a join key and many records (about 1 million?) together cause this issue.
summary: block size exceeds implementation restriction at JStatement::openCursor => Error "block size exceeds implementation restriction" while inner joining large datasets with a long key using the HASH JOIN plan
Submitted by: @cincuranet
I have a query that on one particular database throws:
2017-08-24T16:29:11.5140 (2704:0000000001620040) ERROR AT
JStatement::openCursor
I:\DOWNLOADS\BI2.FDB (ATT_16, SYSDBA:NONE, NONE,
TCPv6:::1/51084)
C:\Users\Jiri\Documents\devel\bin\Firebird-3.0.2.32703-0_x64\isql.exe:7116
335544381 : Implementation limit exceeded
335544388 : block size exceeds implementation restriction
On other databases (same structure, same plan produced) on the same
server (different data of course) it works fine. Backup & restore didn't
help. I can provide the database, but given it's a customer data it
needs to be handled as private data.
The minimum query I can replicate it looks like this:
with DAYS
as (select distinct(DR.F_DA_DATE)
from T_DAILY_RAW DR
)
select count(*)
from T_EMAILS_WEBDB_RAW EWR
inner join DAYS DR on DR.F_DA_DATE = EWR.F_EWR_SENT
inner join T_EMAIL_TYPE_DIM EMTD on EMTD.F_EMT_VALUE = EWR.F_EWR_TYPE;
And the plan is "PLAN HASH (EMTD NATURAL, JOIN (SORT (DR DR NATURAL), EWR INDEX(IDX_EWR_SENT_A_TEMP)))".
Commits: 4638dc1 92ce56c
The text was updated successfully, but these errors were encountered: