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; 
/