Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

CTE Issue

3234063May 17 2016 — edited May 22 2016

I have a table with 65 fields. I need a select statement that will return data from a single record that contains three columns and 21 rows (the two missing fields are keys). I must be able to control the order of the rows.

output needs to look like this

field_x_1     field_x_2     field_x_3

field_A_1     field_A_2     field_A_3

field_Y_1     field_Y_2     field_Y_3

I have tried the following but it says there is an issue with the WITH.

WITH X AS (

WITH T AS (SELECT * FROM Table_Name WHERE FieldZ = '1' AND FieldY = '2')

SELECT Field_x_1 AS A, Field_x_2 AS B, Field_x_3 AS C, 1 AS R

FROM T

UNION ALL

SELECT Field_A_1, Field_A_2, Field_A_3, 2 AS R

FROM T

-- I would add 19 more select statements here

)

SELECT A, B, C

FROM X

ORDER BY R

I realize I could rewrite it to have my WHERE clause in it 21 times but that seems inefficient. If I only use one CTE I don't know how to guarantee the order without also outputting R.

Thanks,

Scott

This post has been answered by Solomon Yakobson on May 17 2016
Jump to Answer

Comments

Solomon Yakobson
Answer

WITH T AS (SELECT  LEVEL L FROM DUAL CONNECT BY LEVEL <= 21)

SELECT  CASE L

          WHEN 1 THEN field_x_1

          WHEN 2 THEN field_A_1

          WHEN 3 THEN field_Y_1

...

          WHEN 20 THEN ...

          ELSE ...

        END COL1,

        CASE L

          WHEN 1 THEN field_x_2

          WHEN 2 THEN field_A_2

          WHEN 3 THEN field_Y_2

...

          WHEN 20 THEN ...

          ELSE ...

        END COL2,

        CASE L

          WHEN 1 THEN field_x_3

          WHEN 2 THEN field_A_3

          WHEN 3 THEN field_Y_3

...

          WHEN 20 THEN ...

          ELSE ...

        END COL3

  FROM  Table_Name,

        T

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

/

You will need explicit CAST if not all THEN expressions withing each CASE have same/compalible data type.

SY.

Marked as Answer by 3234063 · Sep 27 2020
Frank Kulash

Hi,

Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.

Explain, using specific examples, how you get those results from that data.

Simplify the problem as much as possible.  for example, instead of a problem involving 21 rows, post a problem that involves maybe 3 rows.  (Just explain that you really need 21, so people will give answers that can be tested with 3 rows, and then easily adapted to 21.)

Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

See the forum FAQ: 

3234063

Solomon,

I have tried your solution and it seems to work! Are the order of rows guaranteed to return in the right order? I don't see an "order by" clause that would guarantee that. I added "ORDER BY L" at the end and that seems to work.

Thanks,

Scott

Solomon Yakobson

If you want rows ordered, then use ORDER BY L, assuming WHERE FieldZ = '1' AND FieldY = '2' returns only one row from your table. Otherwise, use ORDER BY TABLE_NAME.ROWID,L


SY.

unknown-7404

No ordering is guaranteed unless you use ORDER BY.

Kalpataru

Check this thread may help you.

Using CTE in Oracle

Please put the T CTE outside X and try.. like..

WITH T AS (SELECT * FROM Table_Name WHERE FieldZ = '1' AND FieldY = '2'),

X AS (

SELECT Field_x_1 AS A, Field_x_2 AS B, Field_x_3 AS C, 1 AS R

FROM T

UNION ALL

SELECT Field_A_1, Field_A_2, Field_A_3, 2 AS R

FROM T

-- I would add 19 more select statements here

)

SELECT A, B, C

FROM X

ORDER BY R

William Robertson

fyi, records have fields, tables have columns.

Solomon Yakobson

Very ineffective. It will scan table 21 times.

SY.

AndrewSayer

Solomon Yakobson wrote:

Very ineffective. It will scan table 21 times.

SY.

Oracle will materialize a CTE if it is referred to more than once:

create table many_fields

  (fieldz varchar2(30), fieldy varchar2(30)

  ,field_x_1 number, field_x_2 number, field_x_3 number

  ,field_a_1 number, field_a_2 number, field_a_3 number

  ,field_y_1 number, field_y_2 number, field_y_3 number

  );

explain plan for

WITH T AS (SELECT * FROM many_fields WHERE FieldZ = '1' AND FieldY = '2'),

X AS (

SELECT Field_x_1 AS A, Field_x_2 AS B, Field_x_3 AS C, 1 AS R

FROM T

UNION ALL

SELECT Field_A_1, Field_A_2, Field_A_3, 2 AS R

FROM T

UNION ALL

SELECT Field_y_1, Field_y_2, Field_y_3, 3 AS R

FROM T

)

SELECT A, B, C

FROM X

ORDER BY R

/

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
Plan hash value: 244091491

--------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |                          |    3 |  126 |    9  (12)| 00:00:01 |
|  1 |  TEMP TABLE TRANSFORMATION |                          |      |      |            |          |
|  2 |  LOAD AS SELECT          | SYS_TEMP_0FD9D6679_62EC16 |      |      |            |          |
|*  3 |    TABLE ACCESS FULL      | MANY_FIELDS              |    1 |  151 |    2  (0)| 00:00:01 |
|  4 |  SORT ORDER BY            |                          |    3 |  126 |    7  (15)| 00:00:01 |
|  5 |    VIEW                    |                          |    3 |  126 |    6  (0)| 00:00:01 |
|  6 |    UNION-ALL              |                          |      |      |            |          |
|  7 |      VIEW                  |                          |    1 |    39 |    2  (0)| 00:00:01 |
|  8 |      TABLE ACCESS FULL    | SYS_TEMP_0FD9D6679_62EC16 |    1 |  151 |    2  (0)| 00:00:01 |
|  9 |      VIEW                  |                          |    1 |    39 |    2  (0)| 00:00:01 |
|  10 |      TABLE ACCESS FULL    | SYS_TEMP_0FD9D6679_62EC16 |    1 |  151 |    2  (0)| 00:00:01 |
|  11 |      VIEW                  |                          |    1 |    39 |    2  (0)| 00:00:01 |
|  12 |      TABLE ACCESS FULL    | SYS_TEMP_0FD9D6679_62EC16 |    1 |  151 |    2  (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

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

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

We are only reading the table once, from then on it is in PGA which is going to be much faster to access - very little impact.

Solomon Yakobson

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

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

AndrewSayer

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

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

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

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.

1 - 16
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 19 2016
Added on May 17 2016
16 comments
2,680 views