Forum Stats

  • 3,873,336 Users
  • 2,266,535 Discussions
  • 7,911,513 Comments

Discussions

CTE Issue

2»

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,930 Red Diamond
    edited May 22, 2016 8:26AM

    Well, first of all you can't assume optimizer will always materialize and materialize hint (if chosen) is undocumented hint. Also, my understanding is oracle materializes CTE into temp table, not into PGA but in any case table/materialization will be read 21 times vs once using:

    SQL> explain plan for

      2  WITH T AS (SELECT LEVEL R FROM DUAL CONNECT BY LEVEL <= 3 )

      3  SELECT CASE R

      4           WHEN 1 THEN field_x_1

      5           WHEN 2 THEN field_A_1

      6           ELSE field_Y_1

      7         END COL1,

      8         CASE R

      9           WHEN 1 THEN field_x_2

    10           WHEN 2 THEN field_A_2

    11           ELSE field_Y_2

    12         END COL2,

    13         CASE R

    14           WHEN 1 THEN field_x_3

    15           WHEN 2 THEN field_A_3

    16           ELSE field_Y_2

    17         END COL3

    18  FROM many_fields,T

    19  WHERE FieldZ = '1' AND FieldY = '2'

    20  ORDER BY R

    21  /

    Explained.

    SQL> select * from table(dbms_xplan.display)

      2  /

    PLAN_TABLE_OUTPUT

    ---------------------------------------------------------------------------------------------------

    Plan hash value: 1751328397

    ------------------------------------------------------------------------------------------------

    | Id  | Operation                        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

    ------------------------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT                 |             |     1 |   151 |     5  (20)| 00:00:01 |

    |   1 |  SORT ORDER BY                   |             |     1 |   151 |     5  (20)| 00:00:01 |

    |   2 |   MERGE JOIN CARTESIAN           |             |     1 |   151 |     4   (0)| 00:00:01 |

    |*  3 |    TABLE ACCESS FULL             | MANY_FIELDS |     1 |   138 |     2   (0)| 00:00:01 |

    |   4 |    BUFFER SORT                   |             |     1 |    13 |     3  (34)| 00:00:01 |

    |   5 |     VIEW                         |             |     1 |    13 |     2   (0)| 00:00:01 |

    |*  6 |      CONNECT BY WITHOUT FILTERING|             |       |       |            |          |

    |   7 |       FAST DUAL                  |             |     1 |       |     2   (0)| 00:00:01 |

    ------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

       3 - filter("FIELDZ"='1' AND "FIELDY"='2')

       6 - filter(LEVEL<=3)

    Note

    PLAN_TABLE_OUTPUT

    --------------------------------------------------------------------------------------------------------

       - dynamic statistics used: dynamic sampling (level=2)

    24 rows selected.

    SQL>

    SY.

  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown
    edited May 22, 2016 9:06AM

    Agreed, unless in-memory materialize is a new feature I haven't heard about, it's a temp table.

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited May 22, 2016 9:33AM

    The optimizer will cost the materialization of the with clause. If the CTE filters down the table and gets referenced many times then there is a high chance of it materializing. I was sure I had read that it will always materialize if the CTE is used more than once and the query inside it does contain predicates, I can't find this though.

    I always thought it would be loaded in memory rather than temp, but yes it does increment the "physical writes direct temporary tablespace" stat (it then reads the result via scattered reads into SGA rather than direct reads). Thanks for the correction.

    The result set will be small in comparison to the table, the actual overhead of direct writing to temp and then reading it back into SGA is not going to make the query "very ineffective", the table will not be scanned 21 times, the table will be scanned once, the result set will be read 21 times, mostly using cache reads.

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited May 22, 2016 9:44AM

    Found what I had read on when the materialization should happen:

    https://jonathanlewis.wordpress.com/2015/07/24/subquery-factoring-9/

    My tentative conclusion is that the transformation is a heuristic one that follows the rule “two or more appearances of the subquery and some indication of row selection in the subquery rowsource”. (In fact if the rowsource is “select * from pipeline_function” the requirement for subsetting doesn’t seem to apply.)
    

    Obviously this is not from the documentation so is not for certain but I think it's fairly certain the query posted before would use materialize.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,930 Red Diamond
    edited May 22, 2016 9:47AM
    Andrew Sayer wrote:
    
    The result set will be small in comparison to the table
    

    And how do you know that? OP wrote I need a select statement that will return data from a single record that contains three columns and 21 rows. I read it take each row query returns and transform it into 21 rows. So we don't know how many rows condition FieldZ = '1' AND FieldY = '2' returns.

    SY.

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited May 22, 2016 10:28AM
    Solomon Yakobson wrote:
    
    
    Andrew Sayer wrote:
    
    The result set will be small in comparison to the table
    
    
    And how do you know that? OP wrote I need a select statement that will return data from a single record that contains three columns and 21 rows. I read it take each row query returns and transform it into 21 rows. So we don't know how many rows condition FieldZ = '1' AND FieldY = '2' returns.
    
    SY.
    

    And I read that as "There is a single row from a table I want to select, I want to turn that one row into 21 rows." Therefore CTE will contain one row with 65 columns which probably won't be that expensive to store.

This discussion has been closed.