This is the Oracle DBMS Version I have.
select * from v$version;
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Below are the sample codes.
create table ORDERS (
ord_no NUMBER
,cust_id varchar(20)
,commentS varchar(100)
,ord_date varchar(8));
ALTER TABLE ORDERS ADD CONSTRAINT PK_ORDERS PRIMARY KEY(ORD_NO);
drop table orders_detail purge;
CREATE TABLE ORDERS_DETAIL(
ORD_LINE_NO NUMBER NOT NULL
,ORD_NO NUMBER NOT NULL
,PROD_ID VARCHAR(10) NOT NULL
,COMMENTS VARCHAR(100)
,ORD_AMT NUMBER);
ALTER TABLE ORDERS_DETAIL ADD CONSTRAINT PK_ORDERS_DETAIL PRIMARY KEY(ORD_LINE_NO);
CREATE INDEX orders_detail_x01 ON orders_detail (prod_id); --INDEX
CREATE TABLE PROD (PROD_ID VARCHAR(10) NOT NULL,PROD_NM VARCHAR(100) NOT NULL);
ALTER TABLE PROD ADD CONSTRAINT PK_PROD PRIMARY KEY(PROD_ID);
INSERT INTO PROD
SELECT PROD_ID
, MAX(ORD_NO)||'TEST_NAME'
FROM ORDERS_DETAIL
GROUP BY PROD_ID;
insert into ORDERS
select rownum as ord_no
, 'C'||mod(rownum,10) as cust_id
, lpad('X',10,'Y') as commentS
, to_char(to_date('20191001','YYYYMMDD')+mod(rownum,60),'yyyymmdd') as ord_date
from XMLTABLE('1 to 1000000');
commit;
INSERT INTO ORDERS_DETAIL
SELECT rownum as ORD_LINE_NO
, mod(rownum,1000000) AS ORD_NO
, 'PP'||MOD(rownum,5) AS PROD_ID
, lpad('X',10,'Y') as comments
, case when rownum < 1000 then rownum*100 else rownum end as prod_amt
FROM xmltable ('1 to 10000000');
CREATE OR REPLACE FUNCTION F_GET_PROD_NM(I_PROD_ID VARCHAR)
return varchar
as
O_PROD_NM VARCHAR(100);
BEGIN
SELECT PROD_NM
INTO O_PROD_NM
FROM PROD
WHERE PROD_ID = I_PROD_ID;
RETURN O_PROD_NM;
END;
/
I executed the query below.
SELECT A.ORD_LINE_NO, A.ORD_NO, B.COMMENTS
FROM ORDERS_DETAIL A, ORDERS B
WHERE A.ORD_NO = B.ORD_NO
AND B.ORD_NO BETWEEN 1006 AND 2005
AND (SELECT F_GET_PROD_NM(A.PROD_ID) FROM DUAL) = '999999TEST_NAME';
Predicate Information (identified by operation id):
1 - filter(='999999TEST_NAME')
2 - access("A"."ORD_NO"="B"."ORD_NO")
4 - access("B"."ORD_NO">=1006 AND "B"."ORD_NO"<=2005)
5 - filter(("A"."ORD_NO"<=2005 AND "A"."ORD_NO">=1006))
--10046 trace file
QL ID: 0atxy6tz14ppg Plan Hash: 2720159117
SELECT PROD_NM
FROM
PROD WHERE PROD_ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 9999 0.04 0.04 0 0 0 0
Fetch 9999 0.02 0.02 0 19998 0 9999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19998 0.07 0.07 0 19998 0 9999
Question 1:
Looking at the 10053 trace file, the UDF was executed 9999 times.
I expected scalar subquery caching to kick in.
How can I make scalar subquery caching work?
select count(distinct prod_id) --the result is 5.
from orders_detail;
Question 2:
I changed the SQL into the one below in order to reduce the number of UDF calls.
It seems that the buffer cache stores only one input value and output value.
How can I make the scalar-subquery operate?
I had change the size of "_query_execution_cache_max_size" to 40 Mbytes but the results are the same.
SELECT ORD_LINE_NO, ORD_NO, COMMENTS
FROM (
SELECT /*+ NO_MERGE LEADING(A B) */ A.ORD_LINE_NO, A.ORD_NO, B.COMMENTS, a.prod_id
, (SELECT F_GET_PROD_NM(A.PROD_ID) FROM DUAL) AS FN
FROM ORDERS_DETAIL A, ORDERS B
WHERE A.ORD_NO = B.ORD_NO
AND B.ORD_NO BETWEEN 1006 AND 2005
)
WHERE FN = '999999TEST_NAME';
Predicate Information (identified by operation id):
2 - filter("FN"='999999TEST_NAME')
3 - access("A"."ORD_NO"="B"."ORD_NO")
4 - filter(("A"."ORD_NO"<=2005 AND "A"."ORD_NO">=1006))
6 - access("B"."ORD_NO">=1006 AND "B"."ORD_NO"<=2005)
SQL ID: 0atxy6tz14ppg Plan Hash: 2720159117
--10046 trace file
SELECT PROD_NM
FROM
PROD WHERE PROD_ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1000 0.00 0.00 0 0 0 0
Fetch 1000 0.00 0.00 0 2000 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2001 0.00 0.00 0 2000 0 1000
This time the UDF was executed 1000 times.