Advanced SQL Functions In Oracle 10G Richard Earp
I picked up the Oracle 10G advanced SQL material by Richard Earp back when 10g was the current release and people were still figuring out whether analytic functions were worth the mental overhead. The book is basically a reference work that got heavy use on my desk. It covers the stuff that moves you beyond basic SELECT-FROM-WHERE into territory where Oracle 10G actually starts to differentiate itself from other databases. The core of the text sits around analytic functions, which was Oracle 9i's big introducion and then got expanded in 10G. You get full treatment of RANK, DENSE_RANK, ROW_NUMBER, LAG, LEAD, FIRST_VALUE, LAST_VALUE, running totals, moving averages, and the NTILE family. Earp walks through the syntax with enough examples that you stop guessing about the OVER clause partitioning and ordering behavior. Then there is the MODEL clause, which most people avoid entirely because it looks like someone mashed spreadsheet logic into SQL syntax. The book does a reasonable job of showing why you would want it, though honestly I have found myself reaching for it maybe once in five years of production work. The recursive subquery factoring alternative in 11g made MODEL mostly obsolete for tree traversal problems.
Regular expression functions make an appearance. REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR. Oracle 10G supports them as an add-on package called DBMS_REDACT or rather they are built into the database directly, not requiring a separate install. Earp gives you enough pattern matching examples to feel competent, and that is probably all most of you need. XMLType functions get a chapter. XMLELEMENT, XMLAGG, XMLFOREST, EXTRACT, and the XPath support. If you work with XML data at all this is useful. If you do not, skip ahead.
Multi-Table INSERT And Pivot-Like Behavior
Oracle 10G gives you multi-table insert, which is the ability to feed one query result into multiple destination tables in a single statement. Conditional insert lets you branch on predicates. The book explains this clearly. I used this to replace a stored procedure that was doing row-by-row processing against three tables, and it cut the runtime from about twelve minutes down to under a minute on a dataset of roughly two hundred thousand rows. True PIVOT came later in 11g, but 10G has the aggregation tricks that get you most of the way there. Conditional aggregation with CASE expressions inside SUM or COUNT. The book shows this pattern and it is worth knowing even if you end up writing a simple PL/SQL block instead.
Get the Full Details
MERGE And Error Logging
MERGE, also known as UPSERT, gets proper coverage. The error logging clause is the part people miss. You can direct rows that violate constraints into an error table instead of having the entire statement fail. This matters when you are loading data from external sources and you expect some. Without error logging, one bad row kills the batch and you start all over. With it, you process the good rows and handle the failures separately. Working with analytic functions on a production report I needed a running total per customer that reset every time the month changed. The intuitive approach uses ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW with a PARTITION BY customer_id, but that does not give you the month boundary reset. The workaround I ended up using was a nested analytic: first calculate the month identifier with a TO_CHAR conversion on the date, then apply the running sum with PARTITION BY customer_id, month_id. It felt ugly but it worked correctly across leap years and fiscal year boundaries without any procedural code. The book does not cover that exact pattern because it is somewhat specific, but it does give you the building blocks. You just have to connect them yourself.
Common Pitfalls Beginners Miss
The most frequent issue I see is people assuming ROW_NUMBER, RANK, and DENSE_RANK are interchangeable. They are not. ROW_NUMBER always produces unique sequential integers within a partition. RANK leaves gaps after ties. DENSE_RANK leaves no gaps. If you are doing pagination and you use RANK instead of ROW_NUMBER, your page boundaries will jump unpredictably whenever there are ties in the sort column. Another trap is the default frame specification on window functions. If you write SUM(x) OVER (PARTITION BY y), Oracle assumes ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That is usually fine but it is easy to miss when you switch to a different aggregate or when you add an ORDER BY inside the OVER clause without realizing the frame defaults change accordingly. Hierarchical queries with CONNECT BY PRIOR are powerful but they behave oddly when your data has cycles. I once spent half a day debugging a query that returned no rows because a subsidiary relationship in the org chart had a circular reference, and Oracle simply refuses to return any rows from a CONNECT BY when it detects a loop. The NOCYCLE keyword saves you but you have to add it explicitly and then you need to handle the rows that cycle as a separate result set.
Where The Book Falls Short
The material is specific to Oracle 10G, which means some of the examples will not run on 11g or 12c without modification. Oracle changed several behavioral details between releases. The MODEL clause syntax was tweaked. Performance characteristics shifted. If you are on a newer version, check the Oracle documentation for your specific release first and use the book as conceptual reference rather than a copy-paste guide. The book also does not cover everything that matters. Materialized views get relatively thin treatment. Automatic indexing and stats gathering internals are not there. If your problem involves query rewrite or predicate pushing, you will need to look elsewhere. You can find the book through standard book retailers or used copy sites. The ISBN for the Oracle Press edition should help you locate it. It is not free but the used market has it available at reasonable prices if you do not need a pristine copy.
Bottom Line
If you are working with Oracle 10G and need to go beyond basic SQL, this is a solid reference. The analytic function chapters alone are worth the price if you struggle with window specifications and frame behavior. The practical examples are realistic rather than academic. Just keep in mind that database software ages and some of the specific syntax details may need adjustment for later versions. Read it, try the examples in your own environment, and treat the edge cases I mentioned as the things that will trip you up before they trip you up.
