mview on demand refresh auxiliary selects with high executions

Jun 17, 2022 9:20AM

hello

could you be so kind please and explain the reason of these selects

CREATE MATERIALIZED VIEW JL.MY_TEST_MVIEW ("DOMAIN_ID", "INSTANCE_ID")

SEGMENT CREATION IMMEDIATE

ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS"

BUILD IMMEDIATE

USING INDEX

REFRESH COMPLETE ON DEMAND START WITH sysdate+0 NEXT (sysdate+1/1440) <<<<<

USING DEFAULT LOCAL ROLLBACK SEGMENT

USING ENFORCED CONSTRAINTS DISABLE ON QUERY COMPUTATION DISABLE QUERY REWRITE

AS (

select * from DOMAIN_TEST );

-------

when i frequently updating (1 000 000) the source table

fd51hx5m8guju UPDATE JL.DOMAIN_TEST SET DOMAIN_ID=:B4 , INSTANCE_ID=:B3 WHERE DOMAIN_ID=:B2 AND INSTANCE_ID=:B1

SQL Performance (MOSC)