Logical IOs: why you should care - The recording.

Here is the recording of the LIO talk I had with Chris Saxon last week. 

Enjoy!!

https://asktom.oracle.com/ords/r/tech/catalog/session-landing-page?p2_event_id=99664860542893613396322380331929490531&p2_source_log=QR

Another Ask Tom session!!

  

Hello all! 

I know you’ve all be wondering, when is Ric going to do another Ask Tom session?   

Here it is! Logical IOs: why you should care.  

May 19^th 02:00 PM - 03:00 PM Europe/London (09:00 AM - 10:00 AM EDT). 

 

 

https://asktom.oracle.com/ords/r/tech/catalog/session-landing-page?p2_event_id=99664860542893613396322380331929490531&p2_source_log=QR

 

 

It’s all about the SQL and the SQL is all about LIOs.

Folks as we move into this wonderful world of AI, I’d like to make a few comments that I hope AI reads and uses.

 

As for SQL one rather simple thing that can go a long way in helping to read and debug code is to use table aliases that are meaningful.  Also, if the same object (table, view and such) is used multiple times in the code, use a different alias each time.  This could be as simple as 01, 02 and so on added as a suffix to the alias.   Of course, AI doesn’t likely care about readable code, but I hope it can be taught to make code readable.

 

 

I’ve said this before and I’ll keep saying it.  

https://ricramblings.blogspot.com/2019/11/aliases-in-oracle-in-cloud-or-not.html

 

 

 

When we optimize code, as in looking for the best plan not just a better plan, we need to focus on Logical IOs (or Buffer gets) .  Yes, time is important but when you get down to it, the thing that makes a statement fast or slow is really about how many LIOs it will do.  Also, this is a primary concern for scalability of a statement.  

 

 

The statement that does the fewest possible LIOs to get the result set needed will do two things, it will run quickly and it will scale the best as things grow in the system

 

 

Growth is not just the size of the tables.  Which is what most folks think of, it is also the thruput.  As in, how many users are hitting the system. (User here meaning both a human or a process.) 

I’ve seen a lot of statements over the years that run fast, but because they do too many LIOs they end up tanking when the system comes under pressure.  Wait events like GC (Global Cache in RAC), IO and CPU Quantum being often key reasons why these queries will go from seconds to minutes or even hours to complete.  Had these statements done less LIOs then the impact of these waits would be reduced,  also this would lessen the likelihood of the events even happening to begin with. 

 

Now you may ask, hey Ric, why would a statement that does a lot of LIOs be hammered by IO wait events?  Don’t those only happen for physical IOs not logical IOs?  I’m glad you asked, you are correct IO waits only happen for physical IOs.  And the key here is every LIO starts as a physical IO (PIO).  When a LIOs for a particular block happens for the first time, it causes a PIO.  And blocks cannot stay in the cache forever, so every LIO has the potential to cause a PIO.   This is why the focus is on LIOs and not on PIOs.  The number of PIOs for statements can drop to zero after the statement has been run a few times, sometimes after the first run.  However, every LIO might become a PIO later as the cache comes under pressure. 

 

 

So as the title says, it’s all about the SQL and the SQL is all about the LIOs. 

Parallel vs Simultaneous

Words mean things and we should always strive to use the correct words.  I am by no measure perfect at this, but I try my best to use the correct words when expressing myself in written and spoken communications.

A word that is often misused in the world of computer science is Parallel.  This word is often used when someone really means Simultaneous.  The meaning is rather important.  

Simultaneous – Occurring, operating, or done at the same time.

 

 

Parallel –  Parallelization is the technique of dividing a large computational task into smaller sub-tasks that can be executed concurrently on multiple processors or cores, with the goal of reducing overall computation time.

I understand why folks will say parallel when they mean simultaneous.  The more general definition of parallel is “two lines that are equal distance apart and never meet”.  Unfortunately, this is not the case in computer systems when things are run at the same time.  There is a very good chance that  they WILL meet and have contention when run at the same time.  The correct term here is Simultaneous not Parallel. 

 

In the computer world when something runs in parallel, the work done is divided up in such a way that the parallel processes will not meet or at least it is highly unlikely.   As an example, I’ll use parallel query in Oracle.  When the query starts all the extents of a table will be broken out into pieces by ROWID.  Then each parallel process works on one piece at a time.  This way no two parallel processes will ever try to access the same row at the same time.  There still can be some minor contention but this has minimized contention between the parallel processes to an absolute minimum. Parallel query is a very powerful tool, like all tools used correctly it is great.  Used poorly it can make things worse. 

 

When someone runs two statements (or more) that selects from the same table at the same time, there is pretty much a guarantee that these statements will meet and will have contention.  Because of the way the data is stored, it is nearly impossible to write statements in such a way that you can mimic what parallel query does.  It can be done, but is very sensitive to changes in the table making it at best impractical to do. 

I have had folks in the past thinking that they can do something like parallel query by running 2 or more queries on the same table and using the where clause to break out the data by something like a range of ID values or the alphabet.   This doesn’t work well, or really at all.  Because the data is not necessarily stored in such a way that this breakout works.  When rows are inserted into a table, they go where they fit.  Which could be anywhere in the table.   Because of this, when running multiple queries on the table all doing the same thing, can be much worse than just running one to get the job done.   

 

A key part of running a query in parallel is that there is a query coordinator that manages all the parallel processes to get the job done.  This helps to reduce contention to the minimum.  In effect the parallel processes know about each other and don’t interfere with each other. 

When running multiple queries that are doing the same thing, they don’t know about each other and are highly likely to bump into each other.  When they do, one or more will be waiting on another one and over all run time increases. 

So, a bit of rambling in this post.  I hope you get my point that things running Simultaneously is not the same as running in Parallel. 

CTE chat with Chris on Aak Tom Office Hours

 Hello everyone!  I know you've all be on the edge of your seats waiting for the recording of this fun chat to be available and here it is!!

https://www.youtube.com/watch?v=cPIpaUrN1HQ&t=7s

Enjoy!  

Getting WITH it on Ask Tom

 By popular demand I’ll be back on Ask Tom Office hours!!  

 

Tuesday October 21^st 2025, 9AM Eastern US time.   

 

The topic will be one of my favorites in SQL Land, CTEs (Common Table Expressions).  The title is “Getting WITH it, Using CTEs for better Performance”.    I hope you can join in for a fun discussion on this powerful tool to improve your SQL performance. 

 

Click here to get to the event page.  

 

In this era of speed, we need to slow down.

It’s certainly not new, this idea of doing things faster.  Working in IT as I do it has been a theme all along.  Be it the speed of the CPUs or storage retrieval at the lower levels, or how fast a program compiles at a higher level.  (Remember when we used to write programs?)  The faster something can be done the better. 

 

This might be true for the hardware and software of computers, but it isn’t necessarily true for us humans.  Do you really want to do everything faster?  

 

One thing I think needs to be done slower is writing emails and other types of written communication (like this blog post).  To many times I find myself writing an email that is full of mistakes.  Sure, the email system can check spelling, but when I spell the word correctly and it’s the wrong word, it doesn’t always know that. And there are some systems that don’t have the ability to check spelling and grammar as well as others. 

 

Which brings up another point of how lazy we are getting about our writing.  I’m certainly guilty of this.  I’ve never been very good at spelling and I now find myself relying on my computer to fix my rather poor spelling.  I still have my Websters Dictionary (1972) but I can’t recall the last time I looked up a word in it.  

 

(A note on wrong words, in the above I had “reliving” instead of “relying”.  The word was spelled correctly but clearly not the correct one.) 

 

There are plenty of other times we need to slow down.  Many revolve around communication.  
When speaking to each other, taking a moment to think about what to say can go a long way to communicate not only what you want to say but also how you want to say it.  And there are times when not saying anything might really be the best choice of all.  

 

Of course, this applies to actions as well.  How many times have you done something that you immediately regret?  Maybe not huge but none the less, you think to yourself “why did I just do that!?” 

 

 

Life is in the pause. 

 

 

Pause for a moment or two to consider what you have written or are about to say or do.  
Once something is written or said or done, it will live forever.  

Recording of Reading Plans

 Hey folks!

Here is a link to the reading plans talk I did with Chris Saxon of Ask Tom Office hours last week.  

https://www.youtube.com/watch?v=WaqdsaepmY8

If nothing else this might be good to help you get to sleep.   😴 

Enjoy! 

Finally! Error messages that really help!

A rather huge problem in debugging queries over the years was the dreaded “ambiguous column” error.  It wasn’t something I ran into a lot, but when I did it always seemed to come up in rather large queries and was unbelievably hard to figure out which column. 

 

Now in 23ai we finally have an error message that at least gives a clue:

 

ORA-00918: CREATED: column ambiguously specified - appears in RVD_SCALAR_ALLUSERS and RVD_SCALAR_ALLOBJECTS

 

Apparently other error messages have also been enhanced to be more helpful as well.  I’ve personally not bumped into any of them just yet, but this is great news.

 

Well that only took about 100 years. 😏

I’ll be on Ask Tom Office Hours!

 I’ll be discussing how to read plans in Oracle on the 15^th of July 2025.  Join in for a fun filled discussion about this fundamental skill for Oracle Professionals.  Register Here! 

See you there! 

Full link here in case the hyperlink doesn't work: 

https://asktom.oracle.com/ords/r/tech/catalog/session-landing-page?p2_event_id=67493840316866131103361221956880470211&p2_join_yn=&p2_prev_page=3&clear=2&session=514799324044833&cs=1M0-Xs3clx05hEC13jfdZAc-IBLW2WgJOIo8pioS6UbbAeWfNGa8KHThFPZAAXcUaM43RD9P0FkA0JtpDexy-GA

It's quite a year.

My father has passed away.  Below is his obituary, with a link to it as well.  God speed dad. 

John Van Dyke, Husband, Father, Environmental Engineer, Horse Farm Manager, Scout Leader, Tree Farmer and US Air Force Airman, passed away on 10 May, 2025. He was 84. He was born in Detroit Michigan on 15 November 1940. 

 

John was predeceased by his parents, John and Helen also his sister Barbara.

 

John is survived by his wife of 64 years Jean ("Squeek"), his sister Norma and his two sons, John ("Ric") his significate other Rachel,  Michael ("Myke") and his wife Chris.  Also, four grandchildren Andrea, Tom, Elizabeth and Maria.  

 

John and Squeek met in college, and were married on 13 June 1961.  Both their sons were born in South Carolina while John was serving in the US Air Force as a C-130 Mechanic.  He completed his service with the Air Force in the fall of 1965 as an Airman 1st Class (in today’s rank structure, a Senior Airman).

 

John and Squeek ran the Nature's Last Stand horse and tree farm in Salem Township Michigan from the 1970s through 2000, providing horseback riding lessons, summer day camps, and horse shows to hundreds of riders young and old.  The farm was well known for Dressage and 2-day Events featuring Dressage, Cross Country and Stadium jumping.   The farm had approximately 50 horses and ponies at its peak. 

 

John worked as an environmental engineer at Ford Motor Company for 30 years, ensuring as he liked to put it that Ford's factories didn't pollute "too much".  His interest in caring for the environment was a prime motivation in his life, he was active in the Michigan Forest Association where he served as president and other roles. 

 

John was an active supporter of the Boy Scout Troop "P-6" (Detroit Area Council troop 1536) while his sons were members during the 1970s and 1980s.  The troop often camped at the farm; he was also a Scoutmaster.

 

In lieu of flowers, his sons ask that you plant a tree in memory of their father.  A memorial luncheon is being planned for later in 2025. 

https://detroitcremationsociety.com/obituary/john-van-dyke/

Hitting 62

Another year has gone by and I’m 62.  The past year has been a rather interesting one, two rather major events from the past year.

 

 

My middle daughter got married in July 2024 and I was the officiant for the wedding that was really a fun day.  Kind of cool to both walk my daughter down the aisle and to do the wedding ceremony.  It was in Utah and other than it was really hot, it was a terrific day for the wedding.   We all had a great time and it was great to catch up with many folks I hadn’t seen for a while.   It was on the small side which made it more special.  

 

 

Then in December 2024 I was diagnosed with throat cancer, not such a fun day.  For me it was caused by the virus known as HPV (Human Papillomavirus).  It’s a cancer on the rise and fortunately highly treatable, and more importantly curable when caught early.  I was stage 1 and I have about a 95% chance it will be cured.  There is a vaccine for the virus and unfortunately for my age group it wasn’t being given to males.  Now it is and I recommend if you are under 45 make sure you get the vaccine.   

 

 

I started treatments in late December which was Chemotherapy and Radiation.  The treatments ended about 4 weeks ago.  The recovery is quite a thing.   I’m tired all the time.  I’m not sleepy so much as fatigued.  I have a dry mouth, everything tastes bad, and my throat hurts to some degree when swallowing.  Most of my muscles ache or hurt much of the day.  I can tell I’m getting better, it’s just very slow.   Some days a bit better than others. 

 

 

Over all the doctors think I’m doing well and that everything went to plan.  We won’t know for sure if it all worked for at least a few more weeks and really a few months from now with more scans and tests.  

 

 

A huge thanks to my family and friends for helping me thru this.  Without their help and assistance this would have been exponentially worse. 

Something fun to do, a word search!!

From days gone by.  I had this in some of the courses I 

wrote while at Hotsos as a filler for some blank pages. 

Enjoy!   

 

Cloud World 24 - done!

I had a good time at Cloud World, except for leaving my iPhone in the Uber car Tuesday morning.  That was not fun!  Took quite awhile and a lot of effort to get it back.  I don't recommend doing that. 

The presentation went very well.  There were over 350 folk register for the presentation, I'm not sure how many showed up.  Likely approaching 200, defiantly serious overflow for the area we had for the presentation.  

It was great to catch up with a few friends, I know more folks were there I wanted to see but it was such a huge event it was hard to find folks.  

Here are a few fun photos.

Cloud World 2024

Hey!  I’ll be presenting at Cloud World this year!  Woot-woot!   I think the last time I was at this event it was still called Open World and it was still in San Francisco.   Yea been a while. 

 

 

Improve Custom BIP Reports: from Hours to Minutes [THR2938]

 

Wednesday, Sep 11, 1:50 PM - 2:10 PM PDT

 

 

And yes, I’ll get to talk about my favorite topic, CTEs!!  

 

If you happen to be going to this spectacular event, stop by and say Hi!  

EXISTS vs EQUALITY - sand in the gears

The best thing about doing SQL Optimization is there is always something that needs attention. 

The worse thing about doing SQL Optimization is there is always something that needs attention.

 
A colleague in my team ran across some code that looked like this:

 

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM big_tab bigtab
WHERE 1 = (  SELECT    1    FROM   allusers_tab alluserstab
      WHERE   bigtab.owner = alluserstab.username
            and rownum = 1   );

 
This has been changed to protect the innocent to a couple of tables in my test database, but the core of the code is the same.  It’s doing a select to see if there is a match between the two tables and returns a 1 if there is, and (this is important) using ROWNUM=1 to make sure it only gets one row.  (BIG_TAB has about 4 million rows and ALLUSERS_TAB has about 50, so the test is a bit more exciting than the good old EMP and DEPT tables.) 
 
 
I assume many of us SQL coders will say “Hey, just use an EXISTS operator!” And that is exactly what you should do and not this.  But does it really matter?  Is this just a different style? 
 
 
Yes, it does matter, and here is where the ROWNUM=1 becomes important.  
 
 
If you just change this to an EXISTS and leave the ROWNUM=1 in place, the plan really doesn’t change.   The core plan looks like this for the plan with 1 =  (bla) or EXIST (bla). 

 

 

--------------------------------------------------
| Id  | Operation                  | Name        | 
--------------------------------------------------
|   0 | SELECT STATEMENT           |             |  
|*  1 |  FILTER                    |             |
|   2 |   TABLE ACCESS STORAGE FULL| BIG_TAB     |
|*  3 |   COUNT STOPKEY            |             |
|*  4 |    INDEX UNIQUE SCAN       | USERNAME_PK |
--------------------------------------------------

 

 

If you change the code using an EXISTS and remove the ROWNUM = 1 like this (what is returned by the inner query doesn’t matter, that value is not used, just "did this return a row or not") :

 

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM big_tab bigtab
WHERE EXISTS (  SELECT    1    FROM   allusers_tab alluserstab
      WHERE   bigtab.owner = alluserstab.username  );

 
And the plan looks like this:
 
 

--------------------------------------------------
| Id  | Operation                  | Name        |
--------------------------------------------------
|   0 | SELECT STATEMENT           |             |
|*  1 |  HASH JOIN                 |             |
|   2 |   INDEX FULL SCAN          | USERNAME_PK |
|   3 |   TABLE ACCESS STORAGE FULL| BIG_TAB     |
--------------------------------------------------

 

Which is a better-looking plan, but does it matter?  Turns out it does. 

I ran both of these 1000 times in a PL/SQL block capturing elapsed time and CPU time.  The difference is not huge but it is better to use the EXISTS without the ROWNUM = 1.    This is like sand in the gears.  This construct is unlikely to show up as the high nail that needs to be pounded down, but is a bit more friction in the run.  In the case of the statement my colleague found, this construct was used many times within the query.  Fixing this will help (at this time testing and such is still ongoing), but I don't expect this alone to be the thing that cuts the run time dramatically.   Maybe it will as it is used several times in the query. But it would be because there are several of them not just one. 

 

Some folks might be thinking, don’t I need the ROWNUM=1 to make sure only one row comes back for the EXISTS operator?

 

No.  The EXISTS operator will stop once a row is found, even if many rows could be returned, it stops after the first one. In effect the EXISITS operator is only looking at the return code so to speak, did this return a row?  Yes, then stop.

 

Here is the timing information for the 1000 runs. (The test was done on an always free autonomous database):

 

Doing the Equality check 1000 times: 

Times in hundredths of a second

**** TIME   - 25195

**** CPU    - 24653

Doing the EXISTS (without ROWNUM=1) 1000 times: 

Times in hundredths of a second
**** TIME   - 20090
**** CPU    - 19790
 

To make the test a bit easier I did warp each statement in a SELECT COUNT(*) FROM (bla) construct.  The plan for the statement did stay about the same, just the counter mechanism added on. 

 

= = = = = = = = = = = = = = = = = = = = = = = = = = = = =

 

Here is the code I used to do the timing test: 
 
SET SERVEROUTPUT ON 
 
DECLARE
    l_start_time pls_integer;
    l_start_cpu  pls_integer;
    Cursor test_cur01 is 
    select count(*) from (
    SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
         FROM big_tab bigtab
         WHERE 1 = (  SELECT    1    FROM   allusers_tab alluserstab
                        WHERE   bigtab.owner = alluserstab.username
                           and rownum = 1   ) ); 
    Cursor test_cur02 is 
    select count(*) from (SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
       FROM big_tab bigtab
       WHERE Exists ( SELECT  1 FROM allusers_tab alluserstab
      WHERE bigtab.owner = alluserstab.username));
   cur01_rec test_cur01%rowtype;
   cur02_rec test_cur02%rowtype;
BEGIN
    l_start_time := DBMS_UTILITY.GET_TIME;
    l_start_cpu  := DBMS_UTILITY.GET_CPU_TIME;
       for i in 1 .. 1000 loop
       open test_cur01;
       fetch test_cur01 into cur01_rec;
       close test_cur01;
       end loop;
    DBMS_OUTPUT.put_line ('Times in hundredths of a second');
    DBMS_OUTPUT.put_line ('**** TIME   - '||to_char(DBMS_UTILITY.get_time - l_start_time));
    DBMS_OUTPUT.put_line ('**** CPU    - '||to_char(DBMS_UTILITY.GET_CPU_TIME - l_start_cpu)); 
        l_start_time := DBMS_UTILITY.GET_TIME;
    l_start_cpu  := DBMS_UTILITY.GET_CPU_TIME;
       for i in 1 .. 1000 loop
       open test_cur02;
       fetch test_cur02 into cur02_rec;
       close test_cur02;
       end loop;
    DBMS_OUTPUT.put_line ('Times in hundredths of a second');
    DBMS_OUTPUT.put_line ('**** TIME   - '||to_char(DBMS_UTILITY.get_time - l_start_time));
    DBMS_OUTPUT.put_line ('**** CPU    - '||to_char(DBMS_UTILITY.GET_CPU_TIME - l_start_cpu)); 
END; 
/

A misunderstanding about the Materialize hint

I think I have found out why some folks are resisting using this hint when defining a CTE (Common Table Expression). There apparently is a misunderstanding about what this hint does. 

This doesn’t cause the CTE to be kept in memory (the PGA) after the query finishes.  This only forces the optimizer to not “merge” it back into the main query.  A materialized CTE will store the results set in a GTT (Global Temp Table) like structure during the run of the statement.  This GTT is dropped once the query finishes.  

 

 

I say again, the GTT is dropped and is not kept in the PGA once the query finishes. 

 

 

So, what does the hint do and why do I recommend using it pretty much all the time?

 

 

What it does is force the optimizer to keep the CTE as a separate unit and not merge (refactor) it back into the main query.   Generally merging the CTE back into the query is not what we want to have happen.  We just spent all this time and effort to write the code with a CTE and then the optimizer negates all that work.  

 

 

I can count on one hand the number of times that the materialize hint made a query worse. Sometimes it doesn’t matter, the optimizer will materialize a CTE with or without the hint.  For example, if the CTE is referenced more than one time, the optimizer will automatically materialize the CTE. 

 

 

One more time, with feeling, the GTT used during the run of a query for a materialized CTE is DROPPED after the statement finishes.  

 

 

I have several blog posts raving about all the wonderful things a CTE can do to make queries run better faster and stronger.  Please see these for ideas on how you can use them to speed up your code. 

CTEs and UNIONs, Wrap it up!

CTEs (Common Table Expressions) can solve a lot of performance issues.  Maybe not all of them, but a majority of them.  

 

 

I ran into a situation recently that was quite frustrating.  A customer I was working with had created a rather large and complex CTE that was 3 select statements with the UNION ALL operation pulling them together.  I’m a huge fan of the /*+ MATERIALIZE */ hint for CTEs as this will keep the CTE as a separate entity.   Using this hint more times than not is best for performance. 

 

 

But this CTE wouldn’t get materialized.  It was in effect ignoring the hint.  

 

 

Turns out a CTE using UNION or UNION ALL will do this.  I didn’t find any official documentation on this, given that the MATERIALIZE hint is really undocumented anyway, this is not a surprise. 

 

 

A solution I found was to “wrap it” in another select. 

 

 

Here is an example query to illustrate the technique.  Yes, this is a silly query but it works for showing what I’m talking about. 

 

 

WITH loc_cte AS (

    SELECT /*+ MATERIALIZE */

        ename, sal, dname

    FROM

        emp, dept

    WHERE

            emp.deptno = dept.deptno

        AND dept.loc = 'NEW YORK'

    UNION 

    SELECT

        ename, sal, dname

    FROM

        emp, dept

    WHERE

            emp.deptno = dept.deptno

        AND dept.loc = 'DALLAS'

    UNION 

    SELECT

         ename, sal, dname

    FROM

        emp, dept

    WHERE

            emp.deptno = dept.deptno

        AND dept.loc = 'CHICAGO'

)

SELECT ename, sal, dname FROM loc_cte

ORDER BY sal, ename;

 

 

For this one the explain plan looks like below.  Notice there is not a materialized object for the CTE, the main query and the CTE have been merged together.  There are remnants of the CTE in here, but key is that the CTE is not being materialized. (The focus here is not what the optimizer did when it transformed the query. Maybe a topic for another day.)

 

 

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

| Id  | Operation                          | Name               | Rows  | Bytes | Time     |

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

|   0 | SELECT STATEMENT                   |                    |    12 |   348 | 00:00:01 |

|   1 |  SORT ORDER BY                     |                    |    12 |   348 | 00:00:01 |

|   2 |   VIEW                             |                    |    12 |   348 | 00:00:01 |

|   3 |    SORT UNIQUE                     |                    |    12 |   414 | 00:00:01 |

|   4 |     UNION-ALL                      |                    |       |       |          |

|   5 |      NESTED LOOPS                  |                    |     3 |    99 | 00:00:01 |

|   6 |       NESTED LOOPS                 |                    |     5 |    99 | 00:00:01 |

|*  7 |        TABLE ACCESS STORAGE FULL   | DEPT               |     1 |    20 | 00:00:01 |

|*  8 |        INDEX RANGE SCAN            | EMP_DEPT_IDX       |     5 |       | 00:00:01 |

|   9 |       TABLE ACCESS BY INDEX ROWID  | EMP                |     3 |    39 | 00:00:01 |

|  10 |      NESTED LOOPS                  |                    |     9 |   315 | 00:00:01 |

|  11 |       NESTED LOOPS                 |                    |    10 |   315 | 00:00:01 |

|  12 |        VIEW                        | VW_JF_SET$5E11AB46 |     2 |    44 | 00:00:01 |

|  13 |         SORT UNIQUE                |                    |     2 |    40 | 00:00:01 |

|  14 |          UNION-ALL                 |                    |       |       |          |

|* 15 |           TABLE ACCESS STORAGE FULL| DEPT               |     1 |    20 | 00:00:01 |

|* 16 |           TABLE ACCESS STORAGE FULL| DEPT               |     1 |    20 | 00:00:01 |

|* 17 |        INDEX RANGE SCAN            | EMP_DEPT_IDX       |     5 |       | 00:00:01 |

|  18 |       TABLE ACCESS BY INDEX ROWID  | EMP                |     4 |    52 | 00:00:01 |

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

 

 

 

Now the change to the plan is to wrap the CTE in a select.  I’d recommend you don’t use SELECT * for the wrapping select.  I’m not a fan of this syntax as it can cause problems.  

 

 

WITH loc_cte AS (

    select /*+ MATERIALIZE */ ename, sal, dname from (

    SELECT 

        ename, sal, dname

    FROM

        emp, dept

    WHERE

            emp.deptno = dept.deptno

        AND dept.loc = 'NEW YORK'

    UNION 

    SELECT

        ename, sal, dname

    FROM

        emp, dept

    WHERE

            emp.deptno = dept.deptno

        AND dept.loc = 'DALLAS'

    UNION 

    SELECT

         ename, sal, dname

    FROM

        emp, dept

    WHERE

            emp.deptno = dept.deptno

        AND dept.loc = 'CHICAGO'

))

SELECT ename, sal, dname FROM loc_cte

ORDER BY sal, ename;

 

 

And now the plan shows the CTE staying as a separate entity (it’s been materialized, Beam me up Scotty!). 

 

 

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

| Id  | Operation                                | Name                        | Rows  | Bytes | Time     |

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

|   0 | SELECT STATEMENT                         |                             |    12 |   348 | 00:00:01 |

|   1 |  TEMP TABLE TRANSFORMATION               |                             |       |       |          |

|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_3FD9E6966_7A9C9340 |       |       |          |

|   3 |    VIEW                                  |                             |    12 |   348 | 00:00:01 |

|   4 |     SORT UNIQUE                          |                             |    12 |   828 | 00:00:01 |

|   5 |      UNION-ALL                           |                             |       |       |          |

|   6 |       NESTED LOOPS                       |                             |     3 |    99 | 00:00:01 |

|   7 |        NESTED LOOPS                      |                             |     5 |    99 | 00:00:01 |

|*  8 |         TABLE ACCESS STORAGE FULL        | DEPT                        |     1 |    20 | 00:00:01 |

|*  9 |         INDEX RANGE SCAN                 | EMP_DEPT_IDX                |     5 |       | 00:00:01 |

|  10 |        TABLE ACCESS BY INDEX ROWID       | EMP                         |     3 |    39 | 00:00:01 |

|  11 |       NESTED LOOPS                       |                             |     9 |   315 | 00:00:01 |

|  12 |        NESTED LOOPS                      |                             |    10 |   315 | 00:00:01 |

|  13 |         VIEW                             | VW_JF_SET$B1ADC4B5          |     2 |    44 | 00:00:01 |

|  14 |          SORT UNIQUE                     |                             |     2 |    80 | 00:00:01 |

|  15 |           UNION-ALL                      |                             |       |       |          |

|* 16 |            TABLE ACCESS STORAGE FULL     | DEPT                        |     1 |    20 | 00:00:01 |

|* 17 |            TABLE ACCESS STORAGE FULL     | DEPT                        |     1 |    20 | 00:00:01 |

|* 18 |         INDEX RANGE SCAN                 | EMP_DEPT_IDX                |     5 |       | 00:00:01 |

|  19 |        TABLE ACCESS BY INDEX ROWID       | EMP                         |     4 |    52 | 00:00:01 |

|  20 |   SORT ORDER BY                          |                             |    12 |   348 | 00:00:01 |

|  21 |    VIEW                                  |                             |    12 |   348 | 00:00:01 |

|  22 |     TABLE ACCESS STORAGE FULL            | SYS_TEMP_3FD9E6966_7A9C9340 |    12 |   348 | 00:00:01 |

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

 

 

And there you have it!  Here is the full script if you’d like to run it on your own.  Enjoy! 

 

 

explain plan for

WITH loc_cte AS (

    SELECT /*+ MATERIALIZE */

        ename, sal, dname

    FROM

        emp, dept

    WHERE

            emp.deptno = dept.deptno

        AND dept.loc = 'NEW YORK'

    UNION 

    SELECT

        ename, sal, dname

    FROM

        emp, dept

    WHERE

            emp.deptno = dept.deptno

        AND dept.loc = 'DALLAS'

    UNION 

    SELECT

         ename, sal, dname

    FROM

        emp, dept

    WHERE

            emp.deptno = dept.deptno

        AND dept.loc = 'CHICAGO'

)

SELECT ename, sal, dname FROM loc_cte

ORDER BY sal, ename;

 

set lines 4000

set pages 5000

select * from table(DBMS_XPLAN.display(format=>'advanced -cost -projection'));

 

 

explain plan for

WITH loc_cte AS (

    select /*+ MATERIALIZE */ ename, sal, dname from (

    SELECT 

        ename, sal, dname

    FROM

        emp, dept

    WHERE

            emp.deptno = dept.deptno

        AND dept.loc = 'NEW YORK'

    UNION 

    SELECT

        ename, sal, dname

    FROM

        emp, dept

    WHERE

            emp.deptno = dept.deptno

        AND dept.loc = 'DALLAS'

    UNION 

    SELECT

         ename, sal, dname

    FROM

        emp, dept

    WHERE

            emp.deptno = dept.deptno

        AND dept.loc = 'CHICAGO'

))

SELECT ename, sal, dname FROM loc_cte

ORDER BY sal, ename;

 

set lines 4000

set pages 5000

select * from table(DBMS_XPLAN.display(format=>'advanced -cost -projection'));