Oracle SQL

TwitterGoogleLinkedInRSS

Materialization in subquery factoring without hint “materialize” can be considered only when exists at least one predicate

Posted on by Posted in oracle, query optimizing, undocumented 6 Comments

I found just now that materialization can not be considered by optimizer if there are no predicates in subquery factoring clause. Of course, i mean cases without forcing materialization through hint “materialize”.
Simple example:

Spoiler:: Hidden text SelectShow

Update: I did some additional tests and found:

  1. with “table()” but without “xmltable” materialization occurs always regardless of existence of predicates or another tables in subquery factoring clause
  2. with “xmltable” behavior is very strange – decision about materialization depends from ‘columns …’ clause: when it exists – materialization occurs, if not – not occurs.
  3. with “selects a subset of table columns” as David Aldridge said – decision still depends from predicates existence

Spoiler:: Tests with table() were like this: SelectShow

Spoiler:: Tests with xmltable() were like this: SelectShow

Spoiler:: Test with 2 from 1000 columns SelectShow

  • http://twitter.com/jeffreymjacobs Jeff Jacobs

    While this may be true, you really don’t want the CBO to materialize a subquery without a WHERE clause. The 2nd query will have worse performance due to the temp table creation. In effect, you will be getting 3 FTS when it’s materialized vs 2 FTs for the original query. The materialization in the 1st query is unnecessary and unwanted. In the 2nd query, materialization is driven by the need to match the WHERE to potentially reduce the size of the temp table.

    • rednetx

      Jeff, first of all, it was just simple example and query not from real life. Now about materialization without “where” clause: for example it can be needed when you want use twice result of pipelined function or xmltable/xmlsequence in one query.

    • http://www.facebook.com/david.aldridge.921 David Aldridge

      I would think that the benefits of materialisation would be based on cost estimates with and without it, and that the presence of a predicate would have only a limited impact on that. It might be better to leave the CTE unmaterialised despite the presence of a predicate, or if the CTE’s selects a subset of table columns and hence has a much lower average row length than the table the materialisation may be beneficial without a predicate.

      I don’t think that the test is very conclusive.

      • Sayan Malakshinov

        David, the test above is very simple: note the cost in examples. With materialization it more the without it. So a cost has no effect on decision to materialize. If we look in 10053 we wiil not see making this decision. Also, in my humble opinion, such dummy predicates like ’1=1′ can affect only on presence a filters like ‘null is not null’, and depending on this cost will calculated as usually or will become zero.

      • Sayan Malakshinov

        But maybe i’m wrong, because examples with pipelined function giving a doubtful results

      • Sayan Malakshinov

        Thanks, for example with columns. I did some addition tests and updated post