Hello
I have a query which lasts 2:50 Minutes...
SELECT COUNT(*)
FROM (SELECT a.c_mandant,
f.kre_key,
a.n_finanzprodnummer,
a.b_rate AS amo_betrag,
fa.n_freq_in_monaten,
fa.d_durchf_erste AS amo_erste_faelligkeit,
fa.d_gueltig_bis AS amo_letzte_ausfuehrung
, P1.fpr_finanzprodnummer
FROM
T1 F
JOIN T2 P1 -- 285 T
ON
p1.c_mandant = f.c_mandant
AND p1.fko_key = f.fko_key
JOIN
T3 z
ON
z.c_mandant = f.c_mandant
AND z.n_finanzierungsnummer = f.fin_finanzierungsnummer
JOIN T4 p --200 T
ON p.c_mandant = z.c_mandant
AND p.n_finanzierungsnummer = z.n_finanzierungsnummer
JOIN T5 p3
ON p.c_mandant = p3.c_mandant
AND p.n_finanzprodnummer = p3.n_finanzprodnummer
JOIN kbu_kf2_kr_nr_amortisation_kon a
ON a.c_mandant = p.c_mandant
AND a.n_finanzprodnummer = p.n_finanzprodnummer
JOIN T6 fa
ON a.c_mandant = fa.c_mandant
AND a.id_kf_kr_nr_amort_faelligk = fa.id_kf_kr_nr_amortisation_faell
WHERE p1.intf_stat = '1'
-- AND P.N_FINANZPRODNUMMER = P1.FPR_FINANZPRODNUMMER
AND z.d_aufloesung IS NULL
AND (p3.d_bis BETWEEN SYSDATE AND TO_DATE('31.12.2028', 'DD.MM.YYYY')/*:l_dat*/ OR p3.d_bis IS NULL)
AND p3.d_von < TO_DATE('31.12.2028', 'DD.MM.YYYY')--:l_dat
AND fa.d_durchf_erste <= TO_DATE('31.12.2028', 'DD.MM.YYYY')--:l_dat
)
WHERE n_finanzprodnummer = fpr_finanzprodnummer
;
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
COUNT(*)
----------
30129
Elapsed: 00:02:50.26
SQL>
I could tuned it with using the subquery factoring.
See below:
WITH TEMP_TAB
AS (SELECT /*+ MATERIALIZE */
a.c_mandant,
f.kre_key,
a.n_finanzprodnummer,
a.b_rate AS amo_betrag,
fa.n_freq_in_monaten,
fa.d_durchf_erste AS amo_erste_faelligkeit,
fa.d_gueltig_bis AS amo_letzte_ausfuehrung
, P1.fpr_finanzprodnummer
FROM
T1 F
JOIN T2 P1 -- 285 T
ON
p1.c_mandant = f.c_mandant
AND p1.fko_key = f.fko_key
JOIN
T3 z
ON
z.c_mandant = f.c_mandant
AND z.n_finanzierungsnummer = f.fin_finanzierungsnummer
JOIN T4 p --200 T
ON p.c_mandant = z.c_mandant
AND p.n_finanzierungsnummer = z.n_finanzierungsnummer
JOIN T5 p3
ON p.c_mandant = p3.c_mandant
AND p.n_finanzprodnummer = p3.n_finanzprodnummer
JOIN T6 a
ON a.c_mandant = p.c_mandant
AND a.n_finanzprodnummer = p.n_finanzprodnummer
JOIN T7 fa
ON a.c_mandant = fa.c_mandant
AND a.id_kf_kr_nr_amort_faelligk = fa.id_kf_kr_nr_amortisation_faell
WHERE p1.intf_stat = '1'
-- AND P.N_FINANZPRODNUMMER = P1.FPR_FINANZPRODNUMMER
AND z.d_aufloesung IS NULL
AND (p3.d_bis BETWEEN SYSDATE AND TO_DATE('31.12.2028', 'DD.MM.YYYY')/*:l_dat*/ OR p3.d_bis IS NULL)
AND p3.d_von < TO_DATE('31.12.2028', 'DD.MM.YYYY')--:l_dat
AND fa.d_durchf_erste <= TO_DATE('31.12.2028', 'DD.MM.YYYY')--:l_dat
)
SELECT COUNT(*)
FROM TEMP_TAB
WHERE n_finanzprodnummer = fpr_finanzprodnummer
;
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
COUNT(*)
----------
30129
Elapsed: 00:00:01.23
SQL>
It is good... BUT... IF I do not want to materialize the query-result how could I give the command to the optimizer that he has to execute the query-block firstly and after that he has to use the filter on the executed result without using the "with" hint?
I tried already the following hints: PUSH_SUBQ, NO_MERGE etc.. but it did not help me..
We can set the execution order of joins with the hints (ORDERED, LEADING..)
-> but how can we set the filter order if we want it or in other words how can we prioritize the execution of a query block as opposed to a filter?
Example:
SELECT COUNT(*)
FROM (---- Firstly this query has to be executed ----
SELECT n_finanzprodnummer
, fpr_finanzprodnummer
FROM ....
)
WHERE ---- This Filter can not be merged into the query seen above! ----
n_finanzprodnummer = fpr_finanzprodnummer
;