My developer wrote a query (trimmed below for easy reading) with in-line views, which turned out to be very slow:
SELECT head.time_series_id time_series_id, head.time_series_name name, obs.observation_id vintage, ...
confidentiality_table.coded_value_en_desc boc_confidentiality_en,
confidentiality_table.coded_value_fr_desc boc_confidentiality_fr, ...
FROM time_series_observation obs,time_series_header head, ...
(SELECT time_series_id, wm_concat(confidentiality_status_en_name) coded_value_en_desc,
wm_concat(confidentiality_status_fr_name) coded_value_fr_desc
FROM time_series_header tsh, data_set_confidentiality ds_conf, confidentiality_status_mv conf
WHERE tsh.data_set_id = ds_conf.data_set_id
AND ds_conf.confidentiality_status_code_id = conf.confidentiality_status_code_id
GROUP BY time_series_id) confidentiality_table
WHERE obs.time_series_id = head.time_series_id AND ...
AND head.time_series_id = confidentiality_table.time_series_id (+)
AND obs.observation_id =?
The time_series_header table is large. My developer never thought about full-table scanned for it in the in-line view confidentiality_table, instead he thought it will be retrieved based on the time_series_id joined from the where clause.
Unfortunately Oracle doesn't work like that smart. It builds the in-line view confidentiality_table by doing full-table scan on the table time_series_header and group by on all the time_series_id. The joining to match the specific time_series_id by matching the specific observation_id. Note there is one-to-one relationship between time_series_id and observation_id
By looking at the execution plan together, we confirmed full-table scan happened, which is absolutely unnecessary as it just needs to wm_concat based on specific time_series_id, not all of them, as the entire query is looking for specific time_series_id, not a whole range of them.
It is not possible to move the confidentiality_status_mv out and join with time_series_header directly, as such it would need to group by on all the columns in the select list. Instead I recommended and built a function as following:
--------------------------------------------
CREATE OR REPLACE FUNCTION GET_CONFIDENTIALITY_DESC(in_time_series_id NUMBER, in_desc VARCHAR2 ) RETURN VARCHAR2 AS
v_dec VARCHAR2(2000);
BEGIN
IF (in_desc='EN') THEN
SELECT wm_concat(confidentiality_status_en_name) into v_dec
FROM time_series_header tsh, data_set_confidentiality ds_conf, confidentiality_status_mv conf
WHERE tsh.data_set_id = ds_conf.data_set_id
AND ds_conf.confidentiality_status_code_id = conf.confidentiality_status_code_id
AND tsh.time_series_id=in_time_series_id;
ELSIF (in_desc='FR') THEN
SELECT wm_concat(confidentiality_status_fr_name) into v_dec
FROM time_series_header tsh, data_set_confidentiality ds_conf, confidentiality_status_mv conf
WHERE tsh.data_set_id = ds_conf.data_set_id
AND ds_conf.confidentiality_status_code_id = conf.confidentiality_status_code_id
AND tsh.time_series_id=in_time_series_id;
END IF;
RETURN v_dec;
EXCEPTION ...
END;
/
--------------------------------------------
By incorporating the function into the query and replacing the in-line view, the new query runs and returns results instantly. Problem is resolved.
SELECT head.time_series_id time_series_id, head.time_series_name name, obs.observation_id vintage, ...
GET_CONFIDENTIALITY_DESC(head.time_series_id,'EN') boc_confidentiality_en,
GET_CONFIDENTIALITY_DESC(head.time_series_id,'EN') boc_confidentiality_fr,...
FROM time_series_observation obs,time_series_header head, ...
WHERE obs.time_series_id = head.time_series_id AND ...
AND obs.observation_id =?
