Where clause van buiten view wordt niet in juiste volgorde toegepast

Vragen
, , ,
1

Bij het ophalen van Twinfield BTW-aangiftes via “Query 1”:

--
-- Selecteer random 10 administraties.
--
use select code, 'tfd' from offices@tfd limit 10

select /*+ http_disk_cache(false) http_memory_cache(false) */ count(*)
from   Twinfield.Views.VatReturnsAsXbrl@tfd
where  DECLARATION_YEAR = 2024

is de looptijd meerdere minuten.

Echter, de volgende query (“Query 2”) met identiek resultaat is in minder dan 2 seconden klaar:

select /*+ http_disk_cache(false) http_memory_cache(false) */ count(*)
from   Declarations@tfd m
join   VatReturnAsXbrlByDocumentId@tfd(m.company_code, m.Id) d
where  m.DocumentCode in ('VATICT', 'VATTURNOVER', 'YEARLYSUPPLETION')
and    m.year=2024

Query blijkt volgens de sessie I/O’s in SessionIOs@DataDictionary ook voor BTW-aangiftes in andere jaren alle documenten op te halen en daarna pas te filteren.

De problematiek speelt niet alleen bij provider-specifieke views, maar ook bij custom views (“Query 3”):

create or replace view test
as
select m.company_code
,      m.company_name
,      m.* except m.company_code, m.company_name prefix with 'DECLARATION_'
,      d.xbrl
from   Declarations@tfd m 
join   VatReturnAsXbrlByDocumentId@tfd(m.company_code, m.Id) d
where  m.DocumentCode in ('VATICT', 'VATTURNOVER', 'YEARLYSUPPLETION') 

select *
from   test@DataDictionary
where  Declaration_Year=2024

Het herschrijven van de view naar “Query 4” lost het probleem op:

create or replace view test
as
select m.company_code
,      m.company_name
,      m.* except m.company_code, m.company_name prefix with 'DECLARATION_'
,      d.xbrl
from   ( select * from Declarations@tfd m where  m.DocumentCode in ('VATICT', 'VATTURNOVER', 'YEARLYSUPPLETION') ) m
join   VatReturnAsXbrlByDocumentId@tfd(m.company_code, m.Id) d

select *
from   test@DataDictionary
where  Declaration_Year=2024

Vreemd genoeg zijn dan bij raadpleging van de view Query 4 met een filter op jaar net zo weinig sessie I/O’s nodig als bij Query 2, terwijl vrijwel alle DocumentCodes betrekking hebben op VATTURNOVER en YEARLYSUPPLETION.

Het blijkt dat de volgende query (“Query 5”) ook snel blijft zolang de where clause maar niet in de buitenste schil van de view komt:

create or replace view test
as
select m.company_code
,      m.company_name
,      m.* except m.company_code, m.company_name prefix with 'DECLARATION_'
,      d.xbrl
from   ( select * from Declarations@tfd m  where m.DocumentCode like 'VAT%') m
join   VatReturnAsXbrlByDocumentId@tfd(m.company_code, m.Id) d

Het toevoegen van een willekeurige niet-optimaliseerbare where clause leidt niet tot het enorme performanceverlies, maar specifiek een filter op DocumentCode wel, terwijl alle rijen hieraan voldoen hier in “Query 6”:

create or replace view test
as
select m.company_code
,      m.company_name
,      m.* except m.company_code, m.company_name prefix with 'DECLARATION_'
,      d.xbrl
from   ( select * from Declarations@tfd m  where m.DocumentCode like 'VAT%') m
join   VatReturnAsXbrlByDocumentId@tfd(m.company_code, m.Id) d
where  m.DocumentCode in ('VATICT', 'VATTURNOVER', 'YEARLYSUPPLETION')

Vereenvoudiging van de kolomnaam DECLARATION_YEAR naar YEAR maakt geen verschil in deze “Query 7”:

create or replace view test
as
select m.*
,      d.xbrl
from   ( select * from Declarations@tfd m  where m.DocumentCode like 'VAT%') m
join   VatReturnAsXbrlByDocumentId@tfd(m.company_code, m.Id) d
where  m.DocumentCode in ('VATICT', 'VATTURNOVER', 'YEARLYSUPPLETION')