Subselect (instead of a numeric constant) degrades optimization
My question is similar to To sub-query or not to sub-query? but I would like to use two existing views (hence I don't want to move the subheading directly into the view logic).
Slow (1.5 seconds)
SELECT h.n
FROM v_headers h
WHERE h.n >= (select 3000 from dual);
The query is fast when using the value from the subquery directly:
SELECT h.n
FROM v_headers h
WHERE h.n >= 3000;
A little more detail: n is not unique, it ranges from 0 to 4000; there are about 50 lines for each n. 11 values from different rows are rotated into columns along n.
Is there a solution without highlighting the selected behind the views (or changing the tables)? Possibly optimizer hints?
Slow first, fast second:
LE: I've simplified queries.
Simplified queries are fast thanks to @ nop77svk. More complex query (for now):
Fast
with xyz$ as (select 3986 as n from dual)
SELECT h.hw_number
FROM xyz$ X
JOIN v_headers h
ON h.hw_number >= x.n
Slow (and a little more simplistic):
with xyz$ as ((SELECT nvl(MAX(n), 0) AS n
FROM (SELECT h.hw_number AS n,
Rank() OVER(ORDER BY h.hw_number DESC) rnk
FROM x_data h
GROUP BY h.hw_number)
WHERE rnk = 50))
SELECT h.hw_number
FROM xyz$ X
JOIN v_headers h
ON h.hw_number >= x.n;
A little more about the v_headers view:
WITH s AS
(
SELECT a.n,
b.col_name,
a.value,
b.col_id
FROM qa_data a
JOIN column_def b
ON b.col_id = a.col_id
WHERE b.master_dynamic_data = 'M' )
SELECT "n","c1","c2","c3","c4","c4","c5","c6","c7","c8","c9","c10"
FROM s PIVOT( Max(value)
keep(dense_rank first ORDER BY col_id) FOR col_name IN (
'c1' c1,
'c2' c2,
'c3' c3,
'c4' c4,
'c5' c5,
'c6' c6,
'c7' c7,
'c8' c8,
'c9' c9,
'c10' c10,
'c11' c11) )
column_def
- 72 lines
- 57 lines with master_dynamic_data = 'M'
- column_def.col_id: unique 1..100 (with spaces)
- column_def.col_name: c1, c2, c3, ... (instead of 20 characters instead of cx).
qa_data:
- 450k lines
- the average number of rows for each qa_data.n is 112 rows. (n from 1 to 4000).
- the average length of qa_data.value is 18 characters
source to share
In a simplified version ...
SELECT --+ leading(X) use_hash(H)
H.n
FROM (select 3000 as n from dual) X
JOIN v_headers H
ON H.n >= X.n
;
... the predicate is >= 3000
correctly resolved as an access predicate that causes Exadata storage indexes to insert.
However, in the full version, the predicate needs to be forced to the inner view, which doesn't seem to be happening. A quick test of the test setup shows that propagation of such a predicate (for a computed value, not a constant value) for the join view works fine, but that the predicate propagates for the pivot join view does not.
The same scenario is true when using a (deterministic) function that pre-calculates the value n
instead of using the inline view — the predicate does not hit the point with the hinge (example 12.1.0.2).
It would be interesting to see the 10053 event trace file to understand what is going on. (Homework for the reader. -))
source to share