3

My question is similar to To subselect or not to subselect? but I would like keep using the two existing views (hence I don't want to move the subselect 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 subselect directly:

SELECT h.n
FROM   v_headers h 
WHERE  h.n >= 3000;

A little more details: n is not unique, it has values between 0 and 4000; for each n exists about 50 rows. 11 values from different rows are pivoted into columns along the n.

Is there a solution without extracting the selects behind the views (or modifying the tables)? Optimizer hints maybe?

First is the slow one, the second is the fast one: enter image description here

LE: I've simplified the queries.

The simplified queries work fast thanks to @nop77svk. The more complex query does not (yet):

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 still somewhat simplified):

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;

new query

A little more details about the view v_headers:

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 rows
  • 57 rows with master_dynamic_data = 'M'
  • column_def.col_id: unique 1..100 (with gaps)
  • column_def.col_name: c1, c2, c3,... (20 chars actually instead of 'cx')

qa_data:

  • 450k rows
  • the average number of lines for each qa_data.n is 112 rows. (n is from 1 to 4000)
  • the average length of qa_data.value is 18 chars
Community
  • 1
  • 1
Andrei Damian-Fekete
  • 1,820
  • 21
  • 27

1 Answers1

1

In the 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 >= 3000 predicate is correctly resolved as access predicate, which causes the Exadata storage indexes to kick in.

However, in the full version one must force propagating the predicate to the inner view, which does not seem to happen. A quick check on a test setup shows that propagating such a predicate (for a computed value, not a constant value) to a join view runs OK, yet propagating the predicate to a pivoted join view does not.

The same scenario is true when using a (deterministic) function which precomputes the value of n instead of using an inline view - the predicate does not get pushed down to the pivoted join view (tried on 12.1.0.2).

It would be interesting to see the 10053 event trace file to understand what is going on. (A homework for the reader. ;-))

peter.hrasko.sk
  • 4,043
  • 2
  • 19
  • 34