One Row to Rule Them All in Exadata on the cloud or not.

Way back in 2018 (Seems like a life time ago, doesn’t it?) I did a post about retrieving one row from a table you can see it HERE.   The other day I was looking at my blog and saw this post and wondered, what would this test be like on Exadata?  And thanks to Oracle having the super cool always free option in the cloud I can easily test it.  

 

One thing I found out quickly is that in 21c the statistic numbers have changed. (This may have change in 18 I’m not sure.)  The statistic number for “consistent gets” used to be 139 when I did this in my 12 and earlier databases.  It’s now 209.  There are a bunch of new statistics in the consistent gets group.  I believe that the change was made  to keep them all together they were all moved to the numbers of 209 to 222, they used to be 139 to 146.  Several of the new ones are PMEM for persistent memory.  I’ve not dived into that just yet to see all details about these, but just the name gives a decent clue about what those are about.

 

Back to my test.  In the original post I was showing that to get a single row from a table, it’s best to use an index.  A unique index is even better and the best was a unique covering index. 

 

But is that still true in Exadata?  

 

Yes, it is.  

 

Here is the run output.  As I did in the original post, I ran this several times and the results are consistent.   The test code, which is at the bottom of this post, creates a small table and then selects the 10 rows one at a time 100,000 times.  I time it  in both elapsed time and CPU time.  Also, I capture how many consistent gets (LIOs) were done.

 

 

......................................................

selecting all 10 rows without an index 100000 times

......................................................

In hundredths of a second

**** TIME - 4419

**** CPU  - 4214

**** LIO  - 7001404

 

......................................................

selecting all 10 rows with an index 100000 times

......................................................

In hundredths of a second

**** TIME - 968

**** CPU  - 932

**** LIO  - 2000003

......................................................

selecting all 10 rows with a unique index 100000 times

......................................................

In hundredths of a second

**** TIME - 855

**** CPU  - 831

**** LIO  - 2000004

 

......................................................

selecting all 10 rows with a unique covering index 100000 times

......................................................

In hundredths of a second

**** TIME - 824

**** CPU  - 816

**** LIO  - 1000002

 

 

The output didn’t surprise me, I figured it would be the same on Exadata was it was on a non-Exadata configuration, and it sure is good to have it prove out in a test.   

 

The key take away is that if all you want to get from a table is one row, the fastest way is to use and unique index.  If available, a covering index (one that has all the columns you want) is even better, in an Exadata configuration or non-Exadata.

 

What this doesn’t mean is that if you are doing a join on a primary key and joining a significant number of rows from this table to another, that the index is a good thing in Exadata.  A Smart Scan during a full table scan is very likely to be better.  I talk about this in a post HERE.

 

Here is the code I ran to do the above test.

 

rem testing to get one row out of a table.

rem SEP2018  RVD

rem JUL2021  RVD updated for 21c 

rem

set echo on feedback on serveroutput on

drop table iamsmall purge;

create table iamsmall (id_col number, data_col varchar(20));

begin

 for x in 1..10 loop

   insert into iamsmall values (x, (dbms_random.string('A',20)));

 end loop;

end;

/

 

declare

    x1           varchar2(20);

    l_start_time pls_integer;

    l_start_cpu  pls_integer;

    l_start_cr   pls_integer :=0;

    l_end_cr     pls_integer :=0;

begin

    --the consistent gets statistic is #139 in 12c and #209 in 21c 

    --select value into l_start_cr from v$mystat where STATISTIC# = 139;

    select value into l_start_cr from v$mystat where STATISTIC# = 209;

    l_start_time := DBMS_UTILITY.GET_TIME;

    l_start_cpu  := DBMS_UTILITY.GET_CPU_TIME;

    for ii in 1 .. 100000 loop

     for i in 1 .. 10 loop

        select data_col into x1 from iamsmall where id_col = i;

     end loop;

    end loop;

    DBMS_OUTPUT.put_line ('......................................................');

    DBMS_OUTPUT.put_line ('selecting all 10 rows without an index 100000 times');

    DBMS_OUTPUT.put_line ('......................................................');

    DBMS_OUTPUT.put_line ('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));

    --select value into l_end_cr from v$mystat where STATISTIC# = 139;

    select value into l_end_cr from v$mystat where STATISTIC# = 209;

    DBMS_OUTPUT.put_line ('**** LIO  - '||to_char( l_end_cr - l_start_cr));

end;

/

 

rem create a nonunique index on id_col

create index ias_id on iamsmall(id_col);

declare

    x1           varchar2(20);

    l_start_time pls_integer;

    l_start_cpu  pls_integer;

    l_start_cr   pls_integer :=0;

    l_end_cr     pls_integer :=0;

begin

    select value into l_start_cr from v$mystat where STATISTIC# = 209;

    l_start_time := DBMS_UTILITY.GET_TIME;

    l_start_cpu  := DBMS_UTILITY.GET_CPU_TIME;

    for ii in 1 .. 100000 loop

     for i in 1 .. 10 loop

        select data_col into x1 from iamsmall where id_col = i;

     end loop;

    end loop;

    DBMS_OUTPUT.put_line ('......................................................');

    DBMS_OUTPUT.put_line ('selecting all 10 rows with an index 100000 times');

    DBMS_OUTPUT.put_line ('......................................................');

    DBMS_OUTPUT.put_line ('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));

    select value into l_end_cr from v$mystat where STATISTIC# = 209;

    DBMS_OUTPUT.put_line ('**** LIO  - '||to_char( l_end_cr - l_start_cr));

end;

/

 

rem create a unique index on id_col

drop index ias_id;

create unique index ias_id on iamsmall(id_col);

declare

    x1           varchar2(20);

    l_start_time pls_integer;

    l_start_cpu  pls_integer;

    l_start_cr   pls_integer :=0;

    l_end_cr     pls_integer :=0;

begin

    select value into l_start_cr from v$mystat where STATISTIC# = 209;

    l_start_time := DBMS_UTILITY.GET_TIME;

    l_start_cpu  := DBMS_UTILITY.GET_CPU_TIME;

    for ii in 1 .. 100000 loop

     for i in 1 .. 10 loop

        select data_col into x1 from iamsmall where id_col = i;

     end loop;

    end loop;

    DBMS_OUTPUT.put_line ('......................................................');

    DBMS_OUTPUT.put_line ('selecting all 10 rows with a unique index 100000 times');

    DBMS_OUTPUT.put_line ('......................................................');

    DBMS_OUTPUT.put_line ('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));

    select value into l_end_cr from v$mystat where STATISTIC# = 209;

    DBMS_OUTPUT.put_line ('**** LIO  - '||to_char( l_end_cr - l_start_cr));

end;

/

 

 

rem create a unique covering index on id_col,data_col

 

drop index ias_id;

create unique index ias_id on iamsmall(id_col, data_col);

declare

    x1           varchar2(20);

    l_start_time pls_integer;

    l_start_cpu  pls_integer;

    l_start_cr   pls_integer :=0;

    l_end_cr     pls_integer :=0;

begin

    select value into l_start_cr from v$mystat where STATISTIC# = 209;

    l_start_time := DBMS_UTILITY.GET_TIME;

    l_start_cpu  := DBMS_UTILITY.GET_CPU_TIME;

    for ii in 1 .. 100000 loop

     for i in 1 .. 10 loop

        select data_col into x1 from iamsmall where id_col = i;

     end loop;

    end loop;

    DBMS_OUTPUT.put_line ('......................................................');

    DBMS_OUTPUT.put_line ('selecting all 10 rows with a unique covering index 100000 times');

    DBMS_OUTPUT.put_line ('......................................................');

    DBMS_OUTPUT.put_line ('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));

    select value into l_end_cr from v$mystat where STATISTIC# = 209;

    DBMS_OUTPUT.put_line ('**** LIO  - '||to_char( l_end_cr - l_start_cr));

end;

/

Exadata SQL Optimization in the cloud or not.

“We just moved our Oracle system to Exadata, I don’t need to optimize my SQL any more, right?”

 

Chances are that a lot of your SQL will run amazingly faster in Exadata then it did on a non-Exadata machine.  But to think that optimization is no longer needed is a bit naïve at best.  It is still very possible to have SQL that runs much slower than it should on an Exadata machine. 

 

Here are a two big things that I’ve seen that can cause a query to run in a less than optimal way on an Exadata machine.  

 

1.  Not getting a Smart Scan (offloading).   

 

The number one way that Exadata speeds up a query is the ability to do this thing called Smart Scan.  This is where some (maybe all) of the predicates are “offloaded” to the storage cells to scan the blocks and only return the blocks that satisfy these predicates to the Buffer Cache in the SGA.  

 

Many predicates can be offloaded, but not all.  Generally, the simpler the predicate the more likely it will be offloaded.   You can get a list of what can be offloaded with this query: 

 

SELECT DISTINCT name, datatype, offloadable

FROM v$sqlfn_metadata

WHERE offloadable = 'YES'

ORDER BY name;

 

In a 21c database there are 394 operations that can be offloaded.  That puts the odds in your favor but there could be things you do that aren’t in this list and that will slow things down, a lot.  

 

An example of something that isn’t offloadable is the function calls used to support case insensitive searching when you set the NLS parameters as follows:

 

NLS_SORT = BINARY_CI   (accent-insensitive and case-insensitive sort)

NLS_COMP = LINGUISTIC (sorting and comparison are based on the linguistic rule specified by 

NLS_SORT)

 

Here is a simple query to show what happens (see the code at the end of this if you’d like to run it yourself).  The table is the set of owners form the DBA_OBJECTS view.  The query is run once with the default setting for the two parameters and then run with them set for case insensitive searching as noted above.  Of interest is the predicate information.  I’m using DBMS_XPLAN.DISPLAY_CURSOR to show the plan. 

 

select /*+ gather_plan_statistics */ count(*) from case_test where owner = 'ric';

 

With the default settings this returns zero (0) rows since the names are stored in upper case.  The predicate information in the plan looks like this:

 

storage("OWNER"='ric')

filter("OWNER"='ric')

 

Notice the first one is called STORAGE.  This is the one that can be pushed out to the storage cells for the Smart Scan (offloaded).   Depending on how you get the plan this may just say “Access” and not Storage.  If you see the same predicate with Access and Filter on a same step that is showing one that can be offloaded, and the other is used when it isn’t offloaded.  (More about this is a moment.)  

 

With the NLS parameters set for case insensitive searching this is the predicate information:  

 

filter(NLSSORT("OWNER",'nls_sort=''BINARY_CI''')=HEXTORAW('72696300'))

 

This returned 44 for the count, which is correct in my simple test.  The problem is that this predicate cannot be offloaded. The access step in the plan is the same “TABLE ACCESS STORAGE FULL” but it is being applied after the blocks are returned to the buffer cache in the SGA.  As such, this isn’t taking advantage of the Exadata machine. For this test query it hardly mattered as the table only has about 55,000 rows.  But for tables of significate size, not doing offloading will be a major problem. 

 

How to solve this simple example?  Change the predicate to something like this:

 

select /*+ gather_plan_statistics */ count(*) from case_test where lower(owner) = 'ric';

 

The predicates used are below, and the first one can be offloaded (it likely wasn’t in this case since all the blocks of the table were in the buffer cache).  This returned a count of 44:

 

storage(LOWER("OWNER")='ric')

filter(LOWER("OWNER")='ric')

 

When else might something not get offloaded?  A Smart Scan is a run time decision.  You may have done everything correctly to get a Smart Scan, but it doesn’t happen sometimes.  This is because if the storage cells get over loaded, they will stop doing Smart Scans.   From a code point of view there isn’t really anything you can do about this.  

 

At the DBA level they could spread out the data, mostly by getting more storage cells and redistribute the data.  This can avoid contention at the storage cell level.  Also, it can be a timing issue, don’t try to run everything at the same time.  Even just a few minutes (or even seconds) between jobs might make all the difference.  

 

2.  Using Indexes.

 

This one is a bit tricky.  Indexes are still useful in an Exadata environment, and necessary for things like primary keys.  To compound be this problem, you may be running a mixed work load of transactional based queries (that indexes still will be helpful) and data warehouse queries (that indexes don’t work well for).  Knowing which indexes to keep and which to drop is not exactly clear cut. 

 

You will likely need fewer indexes as compared to non-Exadata.   A really big problem with indexes is that they tend to drive NESTED LOOPS joins (potentially large “stacks” of them) and those can be rather inefficient in Exadata with huge tables.   HASH and SORT MERGE joins tend to be better in Exadata.  Both of these tend to favor full table scans over index scans.  A full table scan can benefit from the Smart scan.  Not so much with index scans.   

 

Other then the INDEX FAST FULL scan (which is works much like a full table scan), offloading doesn’t happen with index scans.  Which as just explained, is the real advantage of using Exadata.  

 

Indexes can still be useful when you are getting a small amount of data from a table. But even here it can be tricky.  Testing is the key.  Here is where you may need to do some SQL tricks to get the optimizer to not use an index, two of my favorites are:

 

COALESCE (NVL doesn’t always work)

In Line Views (ILV), need the NO_MERGE hint

 

Using COALESCE the code would look something like this:

 

COALESCE(my_tab.id,-99999) = other_tab.id

 

The reason that NVL doesn’t always work is that the optimizer knows about things like NOT NULL constraints.  For example, if the column you’re trying not to use an index on is a primary key, the optimizer knows it can’t be null so it will remove the NVL function.  But it wouldn’t remove a COALESCE because it could have multiple options. 

 

To use an ILV it would look something like this:

 

JOIN (select /*+ no_merge */ id, name, status from my_tab) my_tab ON my_tab.id = other_tab.id

 

The NO_MERGE is needed because without it, the optimizer will merge it back into the query and it will be just a normal join, and use the index you’re trying to not use.  With an ILV there are no indexes on any column so it has to do a full scan, which is exactly the point of doing this.  It’s really a good idea to only include the columns you use in the query in the ILV.  You want to keep the ILV small, both in width (columns) and depth (rows) if possible.

 

Of course, other approaches would be to make indexes invisible or use hints to force full table scans.  And there are plenty of other “tricks” to use to make an index not useable for query, like add zero to a number or concatenate a NULL to a string.  Whatever technique you do use, I highly recommend that you put in a comment to explain why you put in something odd in your code.  Use the comment to help yourself know why you did something.  It might be months or years later when you look at this code again, so help yourself and add a comment to anything that is non-standard. 

 

Below is the code I used for the example on the case insensitive searching with the NLS parameters.  If you run it on your system, you’ll like want to change the owner.  I suspect you don’t have a user of RIC that owns any objects in your database. 

 

create table case_test 

as (select owner from dba_objects);

set LINESIZE 200

set PAGESIZE 2000

show parameter NLS_SORT

show parameter NLS_COMP

 

select /*+ gather_plan_statistics */ count(*) from case_test where owner = 'ric'; 

 

COLUMN PREV_SQL_ID NEW_VALUE PSQLID

COLUMN PREV_CHILD_NUMBER NEW_VALUE PCHILDNO

select PREV_SQL_ID,PREV_CHILD_NUMBER  from v$session WHERE audsid = userenv('sessionid')

/

SELECT PLAN_TABLE_OUTPUT 

FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR

('&psqlid','&PCHILDNO','TYPICAL ALLSTATS LAST'))

/

 

alter session set NLS_SORT = BINARY_CI;

alter session set NLS_COMP = LINGUISTIC;

show parameter NLS_SORT

show parameter NLS_COMP

 

 

select /*+ gather_plan_statistics */ count(*) from case_test where owner = 'ric'; 

 

COLUMN PREV_SQL_ID NEW_VALUE PSQLID

COLUMN PREV_CHILD_NUMBER NEW_VALUE PCHILDNO

select PREV_SQL_ID,PREV_CHILD_NUMBER  from v$session WHERE audsid = userenv('sessionid')

/

SELECT PLAN_TABLE_OUTPUT

FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR

('&psqlid','&PCHILDNO','TYPICAL ALLSTATS LAST'))

/

 

alter session reset NLS_SORT;

alter session set NLS_COMP = BINARY;

show parameter NLS_SORT

show parameter NLS_COMP

 

 

select /*+ gather_plan_statistics */ count(*) from case_test where lower(owner) = 'ric'; 

 

COLUMN PREV_SQL_ID NEW_VALUE PSQLID

COLUMN PREV_CHILD_NUMBER NEW_VALUE PCHILDNO

select PREV_SQL_ID,PREV_CHILD_NUMBER  from v$session WHERE audsid = userenv('sessionid')

/

SELECT PLAN_TABLE_OUTPUT 

FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR

('&psqlid','&PCHILDNO','TYPICAL ALLSTATS LAST'))

/

SQL Optimization it’s not always about time in the cloud or not

Most often when presented with a Query that needs to be optimized it’s about how long it takes to run.  Something like, “The query is running for over an hour, can we make it run faster?”   But what about a query that runs quickly each time it runs but runs hundreds (or thousands) of times a minute?   Sure, maybe it runs in a second or less, but the cumulative work caused by the query may add up to something that is a problem.  This is commonly referred to as a scaleability issue. 

These types of queries show the issue more with their LIOs (Logical IOs) rather than time.  Here is an example query from many years ago to illustrate the point.   A query much like this was run at a University and on a per run bases it was running in acceptable time.  However, it was showing up in the “Top SQL Report” because it was run very often.  

When it runs the execution plan with stats looks like this:

This was original run way back in version 8 or 9 as I recall, and this version of the code it running on a 21c Autonomous Database, notice the “STORAGE FULL” option for the full table scan.  Even with this way cool stuff, this isn’t really optimal. 

There isn’t a set of steps that is really screaming out “I’m the problem!” in this plan.  None of the operations appear to be taking a lot of time or recourses (like LIOs). 

When looking at a plan like this, one of the first things to do is to figure out “Who’s who in the Zoo?”  Look at the output and look at the tables in the query, do we need them all? 

The output looks like this:

Looking at the tables we have 

– CLASS: Class information

– STUDENTS: Student information 

– GRADES: Grades the students received for a class

– STUDENTCLASS: Intersection table for the Many-to-Many relationship between students and class 

Seems clear that the first 3 tables are needed for the output, but what about the STUDENTCLASS table?  It would be necessary if this report was reporting about more than one class, but it isn’t.   The report is only on one class (English 101 in this instance).  

Looking into the tables we would find that the GRADES table has both the STUDENT_ID and the CLASS_ID columns so we can stitch the tables together with GRADES and we don’t need STDENTCLASS at all. 

Also, there is the access to the CLASS table, from the plan we see that the optimizer chose a CARTESIAN join.  Why?  It’s not because there is a missing join, it’s because it thinks it’s only going to get one row from the table, and it does.  Which is odd since it’s doing a full table scan to get the one row.   How could it know that it was only going to get one row? 

The statistics on that table.  Notice that the table has 10 rows and there are 10 distinct values for the class description column. 

OK so that means the CARTESIAN join isn’t a “problem” in the sense that it’s causing a lot of rows to be unnecessarily returned.  But isn’t there a better way to get one row from the table?  Sure there is, use the primary key, the CLASS_ID column. 

The class is being picked from a list of values so we can change the code to send in the CLASS_ID rather than the CLASS_DESC to the query.  This will use the already existing index on the CLASS_ID column and since it’s a unique index, this is a much better way to find the one row.  Always try to use things that already exist before creating something new. 

Now the rewritten query would look something like this (I kept in some of the old stuff so you can easily see the changes.) 

The new execution plan with stats looks like this:

The key here is the LIOs, it was cut a little bit more then 50%.  Yes, the time went down as well.  However, it’s not even noticeable, 411 micro-seconds to 360 micro-seconds cannot be perceived by a human. 

What did happen is by cutting the LIOs in half (22 down to 10) over all contention went down and throughput was improved in a noticeable way.  In the actual production environment, the tables were much larger than this test case.  The basic improvement was the same cutting the LIOs by over 50%.  With these changes, this query dropped off the “Top SQL Report” for the University.

Getting WITH it in the cloud or not

The WITH clause in Oracle is used to create CTEs, Common Table Expressions also called Sub-Query Factors.  Oracle as a company hasn’t promoted these nearly enough from my point of view, it’s been available since 9.2.  Which is quite a while ago and still many folks are unaware of it.  This little post is to give CTEs a of bit nudge into the light. 

 

So, what is this “CTE” thing?  It’s easiest to think of it as a temporary view that is useable for the duration of the query.  Once the query is finished, they disappear.  Even the syntax is like a view definition:

 

For a view:  CREATE VIEW my_favorite_view AS (select …

 

For a CTE: WITH my_favorite_cte as (select …

 

Generally, I recommend using the MATERLIZE hint for CTEs:

 

WITH my_favorite_cte AS (select /*+ MATERIALIZE */ … 

This hint forces the optimizer to not merge the CTE back into the main query.  Typically, you just did all this work to get it out of there and you don’t want it going back in.  Of course, testing is a great idea, test with and without the hint to be sure you are getting what you want. 

 

Once the CTE is created, use it like you would any table in your query.  You can select from it or join it into a query just as you would any table.  You can even select from an earlier defined CTE in a later one, but you can’t refer to one that hasn’t been defined yet.  No “forward referencing” here. 

 

You create these because they can be very powerful for query optimization. Amazingly powerful.  Here are some reasons to use them. 

 

The most notable cases are when a sub-query within a query is used many times.  Recently I posted about the use with correlated sub-queries in the select list.  Read the post here.  This can be very dramatic in reducing the time and resources used by a query.  

 

Another common case is a subquery that reused many times but  is “slightly” different each time.  For example, a join of a set of tables and the WHERE clause limits it each time on the same set of columns, just different values.  A CTE that gets the super set of the rows used throughout the query can be made, then select from that each time just getting the rows needed for a particular part of the query.  

 

The savings is that the query getting the super set runs only one time, and that set is stored in memory.  Then as it’s joined into each part, only get from it the rows needed.  This is especially good when the set of rows in the super set is much smaller than the total size of all the rows in the joined set of tables.   For example, there are 5 tables in the subquery, total rows of hundreds of millions, but the super set is only tens of thousands.   There is no set number of rows or ratio for this, test to find what works and what doesn’t. 

 

 

Queries using the UNION operator commonly have subqueries that are the same in each part.  Here is another great use of CTEs.  Take the common query and make it a CTE, now just refer to it in each of the UNION sections.

 

You might even use them just to make the query easier to read.  Breaking up the query in to “steps” with the CTEs can make debugging and modification easier later.   This is where maybe the MATERIALIZE hint isn’t needed.  The code might run just fine after the Optimizer smashes it all back together into one query.   

Some personal recommendation on using them.

 

Please give them intelligent names.  Calling them T1, T2 and so on is very painful for debugging.  To further help in reading the query, name them with _CTE as a suffix.  This makes it easier to see what are tables and what are CTEs in large statements.  

 

These next two are meant to keep memory use down.   Each CTE when MATERIALIZED, which more often is what you want, becomes a table like structure in your PGA.  Some guidelines to consider when creating CTEs: 

·      Keep the number of them down.  If you’re creating more than about 6-8 CTEs make sure they really do the trick.  

 

·      Also try to have CTEs that have a “few” number of rows in them.  About a million or less is great.

 

There isn’t really a hard limit of the number of CTEs and the number of rows in one.   Maybe there is a hard limit on the number of CTEs but it you find out what it is, you likely have other problems already.  The point with keeping the number and size down is to conserve memory.  

 

On the other hand, if you have 16 CTEs and the query goes from hours to seconds, then it’s memory well spent.   Same for size, a 500 million row CTE that cuts the run time in a like manner is worth the memory as well.  Over all you might end up uses less memory then when the query runs for hours when exceeding my guidelines purposed here.  Test, test and test again. 

 

It’s time to get WITH it and use CTEs in your queries.