Oracle OpenWorld'14 Guide!

I think, the biggest challenge for people coming to such huge events as Oracle OpenWorld is to navigate through all of the events that simultaneously happen at various locations. You are always under stress that you may miss something, or that you may hit a bad talk, or that you may not even know that there was a talk directly linked to your key production problems.

Finally there is a real guide to OpenWorld - and I am very pleased have one of my presentations listed there:

http://www.otechmag.com/2014/otech-magazine-fall-2014/

Looking forward to see everybody in San Fran!

My new book

It seems to me that I talked about my most recent book everywhere except for my own blog... So, let me introduce you to the one and only performance tuning book dedicated to PL/SQL:

This book took me much longer to write than I initially anticipated - and to be fair, that's the reason for staying quiet at the blog. I just didn't have time/energy to write everywhere :-) Although, it was fun writing - I finally got a chance to create a digest of my own knowledge and put it into any format I choose to. Of course, some shaping from my esteemed guru and co-author Dr. Paul Dorsey was definitely needed, but still it was as free-form as you get. Publishers trusted us so much that they didn't attach any strings to the contract - we were reshaping the structure of the book up to the very end.

I think, I did my best and reached the goal I was shooting at. I really hope, this book can help a lot of people to build better software. Now it's your turn to tell me whether I was right or wrong.

P.s. Here is a review from Steven Feuerstein - link.

Good sources of Oracle information

Pretty often I am being asked about various online and printed source of Oracle-related information. Finally I decided to create a blog post here where I will start to accumulate all the goodies that I just "happen" to find. Maybe I will eventually create even some kind of a guide... Let's see.

Before we start - a couple of disclaimers:
* It is my personal list of favorite resources, so all of the following statements assumed to be prefixed with IMHO :-)
* The list represents my own interests, which are somewhat between DBA and Developer lands (plus a bit of Architect).
* I highly value printed books, because while working on them authors structure (and check!) the material much better comparing to blogs. So, if I mention a name below, you also should check his/her books - they will be worth your money. Even blogs have the advantage of being more interactive (you may get immediate feedback), the quality of books should not be underestimated.

Ok, let's start:

1. My gurus (overall)
- Tom Kyte asktom.oracle.com - no extra explanations needed, I hope ;-)
- Steven Feuerstein http://www.stevenfeuerstein.com/ - one of the main faces behind PL/SQL for the last 20 years
- Jonathan Lewis http://jonathanlewis.wordpress.com/ - after his explanations I finally started to get the Cost-Based Optimizer
- Daniel Morgan http://www.morganslibrary.org/ - huge collection of useful articles and examples! Often more understandable than original Oracle docs.

2. My teachers in the field of performance tuning
- Tanel Poder http://blog.tanelpoder.com/ - I've seen him reading raw trace files! He can decipher X$-objects too.
- Cary Millsap http://carymillsap.blogspot.com/ - extremely rare case of holistic approach to performance tuning (Method-R!)
- Craig Shallahamer http://shallahamer-orapub.blogspot.com/ - another example of very scientific approach to tuning
- Chris Antognini http://antognini.ch/blog/ - a lot of depth in understanding of how Oracle internals work together.

3. My fellows. They do the same kind of job as I do (only sometimes better!)
- Tim Hall http://www.oracle-base.com/
- Adrian Billington http://www.oracle-developer.net/
- Alex Nuijten http://nuijten.blogspot.com/
- Rob van Wijk http://rwijk.blogspot.com/
- Marco Gralike http://www.liberidu.com/blog/

4. DBAs land
- Arup Nanda http://arup.blogspot.com/.
- Alex Faktulin http://afatkulin.blogspot.com/
- Company blog (a lot of smart people up there!): http://www.pythian.com/blog/ Just to name a few: Alex Gorbachev, Michael Abbey, Christo Kutrovski, Jared Still, etc.

Analytic functions in real life

Yesterday while working on a real production report I found a requirement that forced me to come up with a bit nontrivial usage of analytic functions. Here is the problem (translated to SCOTT-level terms):

- I have a table that contains a DATE column (HIREDATE)
- Input parameter is also a date (IN_DT)
- For each department I need to show all rows that have HIREDATE after IN_DT plus the most recent row from the past (only one!).

Obviously, if you split the last condition into two, the solution is reasonably clear:

SELECT empno, ename, deptno, hiredate
FROM scott.emp
WHERE hiredate>:IN_DT
UNION ALL
SELECT empno, ename, deptno, hiredate
FROM (
     SELECT e.*,
            row_number() 
               over(PARTITION BY deptno
                    ORDER BY hiredate desc, 
                             empno desc
                   ) rank_nr
     FROM scott.emp e
     WHERE hiredate<=:IN_DT
     )
WHERE rank_nr = 1

The only problem of this solution - it needs two passed over EMP table. But in my production case that table is huge. Also, just to make life interesting, the result of the specified query has to be joined to 8 other tables to provide the required output. It is understandable, that my first optimization idea was to somehow do a single-pass query. To my own surprise, Oracle analytic functions are flexible enough to help me out:

SELECT empno, ename, deptno, hiredate, rank_nr
FROM
(
SELECT e.*,
      dense_rank()  
        over(PARTITION BY deptno
             ORDER BY 
               CASE WHEN hiredate > :IN_DT THEN '9999'
                    ELSE to_char(hiredate,'YYYYMMDDHH24MISS')||
                           lpad(empno,10,'0')
               END  desc   
            ) rank_nr,
      max(hiredate) 
        over(
             PARTITION BY deptno) max_dt        
FROM scott.emp e
)
WHERE max_dt > :IN_DT AND rank_nr IN (1,2)
      OR
      max_dt < = :IN_DT AND rank_nr = 1

In this solution I two analytical functions
1. DENSE_RANK is forced to order in the following way:
- bring all future dates to the top by assigning them exactly the same value '9999'

- order all other dates by converting them to 'YYYMMDDHH24MISS' format. In that format DESC would bring the most recent date up anyways.

- to resolve conflicts of multiple entries associated with the same date concatenate past dates with padded EMPNO. This way textual sort would correctly work even if the length of EMPNO may differ.

2. MAX() OVER() brings the highest hire date for each department.

Now the outer SELECT has enough information to filter out correct rows: if the highest date if after the parameter, you need to bring ranks 1 and 2 (all future dates + the last past date). Otherwise you need only rank 1 (last past date).

I understand, that this solution may take a couple of seconds to wrap your head around :-). Still - I was able to do exactly what I needed in a single pass!

Summary: if you are working on reports, analytic function is the MUST. That's the only safe way to write efficient code that would consistently work over large data sets.

Oracle OpenWorld 2014

Less than 3 weeks left before the biggest Oracle event of the year kicks in - and I bet, Oracle OpenWorld'14 will impress anybody (as usual). I am honored to be selected as one of speakers there. Here are my two talks:

- UGF4428 "Expanding SQL Horizons: PL/SQL User-Defined Functions in the Real World"
  Sunday, Sep 28, 1:30 PM - 2:15 PM
  Moscone South - 310

- CON4429  "Calling SQL from PL/SQL the Right Way"
Thursday, Oct 2, 2:30 PM - 3:15 PM
Moscone South - 308

Everybody is welcome! I really think that these presentations can improve you day-to-day PL/SQL.

P.s. I will do my best to provide a daily update from the ground! There should be a lot of interesting things to share.