SQL & PL/SQL

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: 

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

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.

No ordering is guaranteed unless you use ORDER BY.

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

fyi, records have fields, tables have columns.

Very ineffective. It will scan table 21 times.

SY.

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.

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

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.

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.

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.

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.