Wednesday, August 11, 2010

Oracle FULL JOIN or UNION ALL

Someone was kind enough to argue with me recently about doing a UNION ALL instead of a FULL JOIN in Oracle (in a MERGE). This same poop chute also posted a spam link that probably led to some Trojan downloader.

To make a long post short, Oracle usually does what it thinks is the best way of fetching data and it is spot on most of the time. Hints and fresh statistics can be used when mother optimizer is not right.

Consider this table:

CREATE TABLE megacrap AS
SELECT
LEVEL AS mega_id
, TO_CHAR(TRUNC(SYSDATE)-LEVEL,'MM/DD/YYYY') AS mega_varchar
, TRUNC(SYSDATE,'YEAR')+LEVEL AS mega_date
FROM DUAL
CONNECT BY ROWNUM BETWEEN 1 AND 4000;
ALTER TABLE megacrap ADD(CONSTRAINT pk_megacrap PRIMARY KEY(mega_id));

Updated with this MERGE:

MERGE INTO megacrap m
USING (
SELECT
NVL(v1.mega_id,t1.mega_id) AS mega_id
, v1.mega_varchar
, v1.mega_date
FROM megacrap t1
FULL JOIN (
SELECT
LEVEL AS mega_id
, TO_CHAR(TRUNC(SYSDATE)-LEVEL,'MM/DD/YYYY') AS mega_varchar
, DECODE(LEVEL,420,NULL,TRUNC(SYSDATE,'YEAR')+LEVEL) AS mega_date
FROM DUAL
CONNECT BY ROWNUM BETWEEN 1 AND 4100 --- 100 new rows
) v1 ON v1.mega_id = t1.mega_id
WHERE v1.mega_id IS NULL OR v1.mega_id NOT IN(1,2,69) --- delete 1,2,69
) u
ON (m.mega_id = u.mega_id)
WHEN MATCHED THEN
UPDATE SET
m.mega_varchar = u.mega_varchar
, m.mega_date = u.mega_date
WHERE DECODE(m.mega_varchar,u.mega_varchar,1,0) = 0 OR DECODE(m.mega_date,u.mega_date,1,0) = 0
DELETE WHERE m.mega_varchar IS NULL
WHEN NOT MATCHED THEN
INSERT VALUES (
u.mega_id
, u.mega_varchar
, u.mega_date
);

Guess what the plan looks like: MERGE STATEMENT

  • MERGE MEGACRAP
    • VIEW
      • HASH JOIN OUTER
        • VIEW
          • UNION ALL
            • HASH JOIN RIGHT OUTER
              • VIEW
                • COUNT
                  • CONNECT BY WITHOUT FILTERING
                    • FAST DUAL
              • INDEX PK_MEGACRAP FAST FULL SCAN
            • NESTED LOOPS ANTI
              • VIEW
                • COUNT
                  • CONNECT BY WITHOUT FILTERING
                    • FAST DUAL
              • INDEX PK_MEGACRAP UNIQUE SCAN
        • TABLE ACCESS MEGACRAP FULL

Mother optimizer decided a UNION ALL was the best option. Now which SQL statement do you want to code and maintain? The FULL JOIN above or the UNION ALL with GROUP BY or UNION ALL with GROUP BY and ANTI JOIN?

Oracle Database SQL Language Reference 11g Release 2 (11.2)

Monday, August 09, 2010

Cell phones while driving, again

I have become sick and tired of people in general. The stupidity and callousness of the masses whittles away at my patience every day, day after day. I grasp for signs of humanity but rarely find genuine goodness in anyone. What makes me write today is something that has caused me to rant before. This time the topic could have caused loss of my health or even my life.

Someone on a cell phone nearly ran me off the road this morning on route 33 on my way to work. She started moving over into my lane forcing me to brake and travel over the rumble strips and partially into the grass at about 60 miles per hour. She must have hit her breaks and slowed down as I recovered back onto the road and accelerated as far as I could away from her and her vehicle. I was sick to my stomach for a good thirty minutes after that. I should have tossed my cookies at work, I probably would have felt better.

Two weeks ago someone in a car with a cell phone stuck to her ear blatantly ran a red light in front of me, coming about two feet from colliding with my car. She gestured to me like as if I did something wrong. She ran the red light from a semi-blind spot for me, from behind a bridge.

The was also an asshole that made a left turn in front of me as I was making a right turn but at least he was not on a cell phone; unless he was using hands-free Bluetooth crap.

Cell phones while driving are dangerous. It does not matter how much technology is thrown at this it will not change how irresponsible some people are. These people are not going to change until they collide with another vehicle or wrap their car around a light pole or kill someone. I do not want to be that statistic.  

If you get a call then either offer to call back or pull over somewhere and talk.

I do not want my tombstone to read "killed by some douchebag driving a Durango while blabbing on a cell phone"