Hi AskTom Team,
I do not know why my
view is not using the
/*+ result_cache */ hint.
Hoqw can I look for the reason?
select /*+ result_cache */ * from (
WITH pttc as
(
SELECT pttc.pttc_code AS term_code,
pttc.pttc_cts_value AS term_cts_value,
pttc.pttc_create_date AS term_create_date,
pttc.pttc_create_user AS term_create_user,
pttc.pttc_desc AS term_description,
pttc.pttc_id AS term_id,
pttc.pttc_modify_date AS term_modify_date,
pttc.pttc_modify_user AS term_modify_user,
pttc.pttc_pdtc_id AS term_pdtc_id,
pttc.pttc_prftc_id AS term_prftc_id,
pttc.pttc_sort AS term_sort,
pttc.pttc_term_id AS term_term_id,
pttc.pttc_valid_from AS term_valid_from,
pttc.pttc_valid_to AS term_valid_to,
pttc.pttc_wfttc_id AS term_wfttc_id,
proc.proc_create_date AS create_date,
proc.proc_create_user AS create_user,
proc.proc_desc AS description,
proc.proc_id AS id,
lngc.lngc_iso_639_1 AS lngc_code,
lngc.lngc_id,
proc.proc_modify_date AS modify_date,
proc.proc_modify_user AS modify_user,
proc.proc_name AS name,
proc.proc_revision_number AS rev,
proc.proc_sort AS sort,
ccsc.ccsc_code AS state_code,
ccsc.ccsc_id AS state_id,
ccsc.ccsc_name AS state_name,
proc.proc_valid_from AS valid_from,
proc.proc_valid_to AS valid_to,
PROC_EXCHANGE_DATE AS exchange_date,
PROC_version AS version
FROM cat_proc_type_term pttc
INNER JOIN cat_proc_type proc ON proc.proc_pttc_id = pttc.pttc_id
INNER JOIN ctl_language lngc ON proc.proc_lngc_id = lngc.lngc_id
INNER JOIN cat_ctl_cat_state ccsc
ON proc.proc_ccsc_id = ccsc.ccsc_id
AND TRUNC (SYSDATE) BETWEEN pttc.pttc_valid_from
AND NVL (
pttc.pttc_valid_to,
TO_DATE ('31-12-9999',
'dd-mm-yyyy'))
AND TRUNC (SYSDATE) BETWEEN proc.proc_valid_from
AND NVL (
proc.proc_valid_to,
TO_DATE ('31-12-9999',
'dd-mm-yyyy'))
AND ccsc.ccsc_code IN ('CCSC_CAT_CURR', 'CCSC_CAT_PROV')
) ,
prsc as
(
SELECT prsc.prsc_create_date AS create_date,
prsc.prsc_create_user AS create_user,
prsc.prsc_desc AS description,
prsc.prsc_id AS id,
lngc.lngc_iso_639_1 AS lngc_code,
lngc.lngc_id AS lngc_id,
prsc.prsc_modify_date AS modify_date,
prsc.prsc_modify_user AS modify_user,
prsc.prsc_name AS name,
prsc.prsc_revision_number AS rev,
prsc.prsc_sort AS sort,
ccsc.ccsc_code AS state_code,
ccsc.ccsc_id AS state_id,
ccsc.ccsc_name AS state_name,
pstc.pstc_code AS term_code,
pstc.pstc_create_date AS term_create_date,
pstc.pstc_create_user AS term_create_user,
pstc.pstc_desc AS term_description,
pstc.pstc_id AS term_id,
pstc.pstc_modify_date AS term_modify_date,
pstc.pstc_modify_user AS term_modify_user,
pstc.pstc_sort AS term_sort,
pstc.pstc_term_id AS term_term_id,
pstc.pstc_valid_from AS term_valid_from,
pstc.pstc_valid_to AS term_valid_to,
prsc.prsc_valid_from AS valid_from,
prsc.prsc_valid_to AS valid_to,
PRSC_EXCHANGE_DATE AS exchange_date,
PRSC_version AS version
FROM cat_proc_state_term pstc
INNER JOIN cat_proc_state prsc ON prsc.prsc_pstc_id = pstc.pstc_id
INNER JOIN ctl_language lngc ON prsc.prsc_lngc_id = lngc.lngc_id
INNER JOIN cat_ctl_cat_state ccsc
ON prsc.prsc_ccsc_id = ccsc.ccsc_id
AND TRUNC (SYSDATE) BETWEEN pstc.pstc_valid_from
AND NVL (
pstc.pstc_valid_to,
TO_DATE ('31-12-9999',
'dd-mm-yyyy'))
AND TRUNC (SYSDATE) BETWEEN prsc.prsc_valid_from
AND NVL (
prsc.prsc_valid_to,
TO_DATE ('31-12-9999',
'dd-mm-yyyy'))
AND ccsc.ccsc_code IN ('CCSC_CAT_CURR', 'CCSC_CAT_PROV')
),
groc as
(
select /*+ MATERIALIZE */ * from all_groc_valid
),
role as
(
select * from pty_role
),
prty as
(
select /*+ result_cache */ * from pty_party --where prty_ref_nr = :prtyRefNr
),
insf as
(
select * from ins_procedure_folder
),
ins_role_addr as
(
SELECT role.role_id,
role.role_inse_id,
prty.prty_id,
prty.prty_name,
prty.prty_ref_nr,
addr.addr_street,
addr.addr_street_number,
addr.addr_street_number_addition,
addr.addr_post_code,
addr.addr_city,
ctrc.ctrc_name,
role.role_prof_id,
lngc.lngc_code,
roltc.roltc_code
FROM role
INNER JOIN insf ON role_prof_id = insf_prof_id
INNER JOIN ctl_role_term roltc
ON roltc.roltc_id = role.role_roltc_id
AND roltc.roltc_code IN
('ROLTC_INS_STANDORT', 'ROLTC_INS_BEW_INH')
INNER JOIN prty ON role.role_prty_id = prty.prty_id
INNER JOIN pty_contact_details codt
ON codt.codt_prty_id = prty.prty_id
INNER JOIN pty_address addr ON addr.addr_codt_id = codt.codt_id
INNER JOIN ctl_country ctrc
ON addr.addr_crtc_id = ctrc.ctrc_crtc_id
INNER JOIN ctl_language lngc ON ctrc.ctrc_lngc_id = lngc.lngc_id
)
/*
SELECT DISTINCT
prfa_prcd_id,
prfa_ref_num_proc,
prcd_email_notifications,
prty_ref_nr,
LISTAGG (eligibility,
tec_utilities.select_tpar_value ('REP_2025_SEPARATOR'))
WITHIN GROUP (ORDER BY eligibility) AS eligibility,
prcd_submission_date,
verfahrenstyp AS proc_type_name,
lngc_code,
NVL (has_open_communications, 0),
NVL (cnt_running, 0),
NVL (cnt_done, 0),
max_date,
prcd_grouping_pooling,
prsc_term_code,
prsc_name,
eqpf_subject,
scope_variation,
prcd_term_code,
bewilligungsinhaber,
bewilligungsinhaber_adresse,
betriebsstandort_party_name,
betriebsstandort_adresse,
license_number
FROM
(
*/
SELECT /*+ result_cache */ DISTINCT
role.role_prcd_id
prfa_prcd_id,
prfa.prfa_ref_num_proc,
prcd_email_notifications,
prty.prty_ref_nr,
(SELECT LISTAGG (
t1.name,
tec_utilities.select_tpar_value (
'REP_2025_SEPARATOR'))
WITHIN GROUP (ORDER BY t1.name)
FROM (SELECT DISTINCT CASE
WHEN rolc_eli.term_id IN
(SELECT rtgta_roltc_id
FROM role_group_asgn
WHERE rtgta_grotc_id =
(SELECT term_id
FROM all_groc_valid
WHERE term_code =
'GROTC_INS_ROLLE_MELDEPF_FACHP')
AND rtgta_valid_to
IS NULL)
AND role_eli.role_prty_id
IS NOT NULL
THEN
(SELECT DECODE (
pers_title,
NULL, NULL,
pers_title
|| ' ')
|| pers_given_name
|| ' '
|| pers_family_name
FROM pty_person
WHERE pers_prty_id =
role_eli.role_prty_id)
|| ' - '
|| rolc_eli.name
WHEN rolc_eli.term_id IN
(SELECT rtgta_roltc_id
FROM role_group_asgn
WHERE rtgta_grotc_id =
(SELECT term_id
FROM all_groc_valid
WHERE term_code =
'GROTC_INS_ROLLE_MELDEPF_FACHP')
AND rtgta_valid_to
IS NULL)
AND role_eli.role_prty_id
IS NULL
THEN
NULL
ELSE
rolc_eli.name
END AS name
FROM role role_eli
INNER JOIN
all_rolc_valid rolc_eli
ON rolc_eli.term_id =
role_eli.role_roltc_id
AND rolc_eli.term_is_eligibility =
1
AND rolc_eli.lngc_code =
pttc.lngc_code
INNER JOIN proc_folder_asgn prfa
ON prfa_prcd_id =
role.role_prcd_id
INNER JOIN
ins_procedure_folder insf
ON insf_prof_id =
prfa_prof_id
INNER JOIN ins_eligibility inse
ON inse_id =
insf.insf_inse_id
AND role_eli.role_inse_id =
inse.inse_id
AND ROWNUM <=
tec_utilities.select_tpar_value (
'REP_2025_LIMITER'))
t1)
AS eligibility,
prcd.prcd_submission_date,
pttc.name
verfahrenstyp,
pttc.lngc_code ,
get_has_open (prcd.prcd_id, pttc.lngc_code)
AS has_open_communications,
get_running (prcd.prcd_id, pttc.lngc_code)
AS cnt_running,
get_done (prcd.prcd_id, pttc.lngc_code)
AS cnt_done,
get_newest (prcd.prcd_id, pttc.lngc_code)
AS max_date,
prcd.prcd_grouping_pooling,
prsc.term_code
AS prsc_term_code,
prsc.name
AS prsc_name,
CAST (NULL AS VARCHAR2 (255))
AS eqpf_subject,
CAST (NULL AS VARCHAR2 (255))
AS scope_variation,
pttc.term_code
AS prcd_term_code,
role_inh.prty_name
bewilligungsinhaber,
role_inh.addr_street
|| ' '
|| role_inh.addr_street_number
|| CASE
WHEN role_inh.addr_street_number_addition
IS NOT NULL
THEN
' ' || role_inh.addr_street_number_addition
END
|| DECODE (role_inh.addr_street_number, NULL, '', ', ')
|| role_inh.addr_post_code
|| ' '
|| role_inh.addr_city
|| DECODE (role_inh.addr_street_number, NULL, '', ', ')
|| role_inh.ctrc_name
bewilligungsinhaber_adresse,
role_ort.prty_name
betriebsstandort_party_name,
role_ort.addr_street
|| ' '
|| role_ort.addr_street_number
|| CASE
WHEN role_ort.addr_street_number_addition
IS NOT NULL
THEN
' ' || role_ort.addr_street_number_addition
END
|| DECODE (role_ort.addr_street_number, NULL, '', ', ')
|| role_ort.addr_post_code
|| ' '
|| role_ort.addr_city
|| DECODE (role_ort.addr_street_number, NULL, '', ', ')
|| role_ort.ctrc_name
betriebsstandort_adresse,
inse.inse_license_nr
AS license_number
--select count( distinct role_prcd_id) /*+ monitor */
FROM role
INNER JOIN prty ON role_prty_id = prty_id
INNER JOIN procedure prcd ON prcd.prcd_id = role_prcd_id and prcd.prcd_is_pub_on_ext_gui = 1
INNER JOIN ctl_role_term ON role_roltc_id = roltc_id AND roltc_code IN ('ROLTC_ANBR', 'ROLTC_BEVOLLM_VERFAHR')
INNER JOIN proc_folder_asgn prfa ON prfa.prfa_prcd_id = role_prcd_id
INNER JOIN insf ON prfa_prof_id = insf_prof_id
INNER JOIN ins_eligibility inse ON inse.inse_id = insf.insf_inse_id
LEFT JOIN pttc ON prcd.prcd_pttc_id = pttc.term_id
INNER JOIN proc_type_group_asgn ptga ON ptga.ptga_pttc_id = pttc.term_id
LEFT JOIN prsc ON prsc.term_id = prcd.prcd_pstc_id AND pttc.lngc_code = prsc.lngc_code
INNER JOIN groc /*THIS SECTION BRINGS 6 seconds more*/
ON groc.term_id = ptga.ptga_grotc_id
AND (
( groc.term_code = 'GROTC_INS_ESERVICE_ERFASST' AND prsc.term_code NOT IN ('PRSC_ABGE') AND prsc.term_code NOT IN ('PSTC_ABGEBRO') )
OR ( groc.term_code ='GROTC_INS_ESERVICE_BEARBEITUNG' AND prsc.term_code IN
( SELECT prsc2.term_code
FROM groc
INNER JOIN
proc_state_group_term_asgn
psgta
ON groc.term_id =
psgta.psgta_grotc_id
INNER JOIN
prsc prsc2
ON prsc2.term_id =
psgta.psgta_pstc_id
WHERE groc.term_code =
'GROTC_INS_ESERVICE_BEARBEITUNG'
AND groc.lngc_code =
prsc.lngc_code )