This book introduces a relatively new approach to mastering one's Oracle SQL skills. This book will teach you how to leverage your existing Oracle SQL knowledge as well as how you can benefit from a variety of SQL tricks and techniques we present thereafter. This is a text book rather than a reference, and it aims to teach you how to become a better SQL specialist. Even though the recommendations found in this book may be applied to a variety of SQL flavors, Oracle SQL is the main subject of this book. Our goal was not to impress you with clever tricks and sophisticated techniques, but rather give you a roadmap to excellence in writing Oracle SQL queries. No doubt, this book presents tricks and classy approaches, which still serve the main goal - to let you master your Oracle SQL skills.

Oracle SQL Tricks and Workarounds

By Zahar Hilkevich

AuthorHouse

Copyright © 2011 Zahar Hilkevich
All right reserved.
ISBN: 978-1-4634-2137-3

Contents

Chapter 1: Terms and Definitions..........................................1Chapter 2: First approach to the first problem............................13Chapter 3: Method Substitution............................................23Chapter 4: Introducing Problem/Technique Matrix (PTM).....................50Chapter 5: Extending the PTM..............................................77Chapter 6: Problem Patterns...............................................98Chapter 7: Benefit with the vendor........................................115Chapter 8: Handicap Challenge.............................................154Chapter 9: Solution patterns..............................................172Chapter 10: Substitution Drill............................................189Chapter 11: Writing DELETE and UPDATE statements..........................210Chapter 12: Mastering your SQL skills.....................................230

Chapter One

Terms and Definitions.

This book introduces a relatively new approach to mastering one's Oracle SQL skills. This book will teach you how to leverage your existing Oracle SQL knowledge as well as how you can benefit from a variety of SQL tricks and techniques we present thereafter. This is a text book rather than a reference, and it aims to teach you how to become a better SQL specialist. Even though the recommendations found in this book may be applied to a variety of SQL flavors, Oracle SQL is the main subject of this book. Our goal was not to impress you with clever tricks and sophisticated techniques, but rather give you a roadmap to excellence in writing Oracle SQL queries. No doubt, this book presents tricks and classy approaches, which still serve the main goal – to let you master your Oracle SQL skills.

Traditionally, when someone explains a trick or technique, a specific category of problems is targeted; it usually demonstrates how the trick or technique can solve the problem and the goal is considered accomplished. When we try solving a set of problems we often operate with the following concepts: a problem, a solution, and a method (employed by solutions). A method can represent a trick or technique, which we will define in a few paragraphs below. Usually, we are satisfied when a problem gets solved one way or another and there is little need to introduce a new concept within the domain we described. It is easy to understand that the value of a trick or technique in any technology can be measured by the number of different problems the method helps to solve. Throughout their career, a software developer acquires a number of such techniques that signifies his or her professional experience. Our idea is to show a different angle in the way we can acquire professional skills. Instead of only focusing on the number of different techniques we know, we emphasize a number of different problems or types of problems we are familiar with, and also a number of different ways in which we can solve all those problems. In other words, we encourage researching different types of problems and finding numerous solutions for each of them.

One of the main goals of this book is to explore and reveal the potential of many well known and lesser known techniques. Any technique is better understood if it can be widely applied. This book will demonstrate numerous applications of traditional SQL features in situations where those features were not expected to be applicable.

During the work on this book we faced a small challenge with respect to how to refer to a set of alternative solutions to a specific problem. This is how we came across the term WORKAROUND which we defined as follows:

We call the family of distinct solutions for a given problem the workarounds for that problem.

Since the terminology we use in this book may differ from what you have used before we will take a moment to define a few more terms.

We define an SQL technique as a general problem solving strategy that can usually be applied to a fairly broad set of problems.

An SQL trick is a method that's more specific than a technique – it may not apply to as many types of problems but it is usually more clever and obscure than a technique, and can sometimes be used to solve more stubborn problems.

Classifying solutions as tricks vs. techniques is a subjective process (the line can be blurry), but for us this distinction has been proven useful.

Roadmap of the book

The first chapter makes is an introduction into the methodology of producing multiple solutions for a given SQL problem. Generally speaking, any set of numerous solutions begins with a first solution. Some useful tactics on finding the very first solution are presented in Chapter 2. When you found your very first solution, in reality, you already "booked" a whole set of workarounds that can be derived with a help of Method Substitution, described in Chapter 3. Each chapter will present more and more SQL nuances that will help you derive more and more workarounds. With the growing number of found solutions, it becomes challenging to keep your knowledge well organized; Chapter 4 introduces a Problem Technique Matrix (PTM) – a neat model to manage your problems, techniques, and actual workarounds. Chapter 5 extends the PTM concept and explains how it can be best used. Simple statistical analysis of the PTM content leads us to a concept of Problem Patterns that provides a great practical payoff; Chapter 6 describes Problem Patterns in details. Every new Oracle release comes with a handful of very useful new features which equates to more new techniques one should try for finding new solutions – see Chapter 7 for more details on that. New features often offer new and simpler approaches to variety of problems. When developers get used to new techniques, they often forget that in previous versions they survived without them. Chapter 8 presents several handicap challenges, including the one we have just pointed to – "How did we do this before"? When developers start working on a new query, they are supposed to know all related business entities, relationships, and other rules. This is not the case when they have to optimize somebody else's request. Chapter 9 demonstrates possible approaches for such circumstances. Nothing can speed up one's professional progress better than extensive practice. Chapter 10 offers a comprehensive drill based on Method Substitution and other techniques reviewed earlier in the book. Data Manipulation commands are not limited to SELECT statement only. Chapter 11 shows how to apply the knowledge accumulated from Chapters 1 through 10 to DELETE and UPDATE commands. The last chapter of the book suggests further ways of improving SQL practical skills.

Why bother looking for workarounds?

SQL is a declarative language (unlike procedural languages, such as C, Java, Visual Basic, etc) hence it allows multiple equivalent ways to write database requests. Sometimes, two logically identical SQL statements look completely different and only SQL experts can recognize a common pattern. We will not be discussing correct and incorrect ways of writing SQL statements, but rather teaching you how to find different ways to write such statements.

Our goal is to enable our readers to become SQL gurus quickly, by teaching specific sample techniques and tricks, and helping them enhance their approach to SQL problems.

For application developers who stop at the first solution to a SQL problem, gaining proficiency is a slow process requiring a huge number of problems to accumulate the requisite experience.

For those willing to try a more direct path, investing additional time upfront on multiple workarounds for each problem will help build your skills more quickly.

One of the benefits of knowing the workarounds is obvious: when you know multiple approaches the likelihood of picking the most suitable or efficient approach is higher than in the situation when you only know one way. Knowledge of all the ins and outs of a specific problem not only gives you a deep understanding of the problem, but also trains you to see the big picture and become more flexible in your decisions.

The good news is that as we will show in this book, it is not that difficult to learn to find workarounds. Over the years of our development and teaching experience, we have built a framework that simplifies the process of finding the workarounds.

Who Should Read This Book

If you, like the author of this book, value every opportunity to extend your collection of challenging problems, tricks, and techniques, this book is for you. Software application developers, managers, database developers and administrators can all find something new and exciting in this book. By any means, this is not a beginner's manual; however, college students taking advanced database classes may also greatly benefit from this book. Casual database programmers will not find this book very handy and we did not target them when writing this book.

Prerequisites. Why Execution Plan?

We assume that our reader have basic knowledge of Oracle SQL as well as the SQL*Plus environment. If you are new to Oracle, we suggest you read any introductory book on Oracle SQL, and then come back to enjoy our journey to Oracle SQL insights. Throughout the book we will use Oracle's famous built-in schema "SCOTT" with tables "EMP" and "DEPT". We took an effort to rephrase numerous problems we collected for this book in terms of those two tables. This approach should make it easy to try all suggested solutions in this book since the "SCOTT" schema is either already available in your Oracle database or it can easily be created since Oracle supplies the script to build it. We also assume that you are familiar with a concept of a Query Execution Plan.

Execution Plans play a central role in our book. Since we refer to workarounds as a set of distinct solutions to a specific problem, we need to develop criteria to consider two or more solutions distinct.

To illustrate the point let's take a look at the following simple problem.

Problem: Find all employees who were hired in 1981.

We expect the following dataset to be returned as a correct solution:

ENAME HIREDATE

ALLEN 20-FEB-81 WARD 22-FEB-81 JONES 02-APR-81 MARTIN 28-SEP-81 BLAKE 01-MAY-81 CLARK 09-JUN-81 KING 17-NOV-81 TURNER 08-SEP-81 JAMES 03-DEC-81 FORD 03-DEC-81

Let's consider the following alternative solution of this problem and execution plans generated by autotrace option (SET autotrace ON EXPLAIN command):

Solution #1.

SELECT ename, hiredate FROM emp WHERE hiredate BETWEEN TO_DATE('01-JAN-1981', 'DD-MON-YYYY') AND TO_DATE('31-DEC-1981', 'DD-MON-YYYY')

Execution Plan

Plan hash value: 3956160932

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 11 | 154 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 11 | 154 | 3 (0)| 00:00:01 |

Predicate Information (identified by operation id):

1 - filter("HIREDATE"<=TO_DATE(' 1981-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "HIREDATE">=TO_DATE(' 1981-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Solution #2.

SELECT ename, hiredate FROM emp WHERE hiredate >= TO_DATE('01-JAN-1981', 'DD-MON-YYYY') AND hiredate <= TO_DATE('31-DEC-1981', 'DD-MON-YYYY')

Execution Plan

Plan hash value: 3956160932

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | 11 | 154 | 3 (0) | 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 11 | 154 | 3 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1 - filter("HIREDATE"<=TO_DATE(' 1981-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "HIREDATE">=TO_DATE(' 1981-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Solution #3.

SELECT ename, hiredate FROM emp WHERE hiredate LIKE '%81'

Comment: one assumption is made here – all employees were hired in 20th century.

Execution Plan

Plan hash value: 3956160932

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 14 | 3 (0)| 00:00:01 |

Predicate Information (identified by operation id):

1 - filter(INTERNAL_FUNCTION("HIREDATE") LIKE '%81')

Solution #4.

SELECT ename, hiredate FROM emp WHERE TO_CHAR(hiredate, 'YYYY')='1981'

Execution Plan

Plan hash value: 3956160932

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 14 | 3 (0)| 00:00:01 |

Predicate Information (identified by operation id):

1 - filter(TO_CHAR(INTERNAL_FUNCTION("HIREDATE"),'YYYY')='1981')

Solution #5.

SELECT ename, hiredate FROM emp

WHERE INSTR(TO_CHAR(hiredate, 'DD-MON-YYYY'),'-1981')>0

Execution Plan

Plan hash value: 3956160932

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 14 | 3 (0)| 00:00:01 |

Predicate Information (identified by operation id):

1 - filter(INSTR(TO_CHAR(INTERNAL_FUNCTION("HIREDATE"),'DD-MON-YYYY') , '-1981')>0)

As you can check, all queries return correct results. It is also obvious that it would be naïve to claim that we have presented five different solutions of the problem, even though they all look different. In our opinion, textual representation of a query cannot be used as proof of a solution's uniqueness. Since Oracle supports in-line views, we can always use any existing solution to derive more by using the following simple technique:

SELECT * FROM () Let's say, to derive Solution #6, we can take Solution #1 and transform it into an inline view: SELECT * FROM (SELECT ename, hiredate FROM emp WHERE hiredate BETWEEN '01-JAN-1981' AND '31-DEC-1981') Apparently, this query does exactly the same as its subquery part, even though it looks somewhat different; hence, we cannot qualify it as a distinct solution. When working on this book, we needed to define an objective criterion for solution distinction, so we decided to employ Oracle's query execution plan. We qualify a workaround as distinct if it causes the database to execute the query differently compared to other solutions. When we come up with a candidate solution, we examine its execution plan against the other solutions found so far. If it is different, we consider what we have found a new, distinct workaround; otherwise, we have only found a workaround equivalent to one of the other workarounds, which we also refer to as a synonymous workaround. Note that based on this definition, the qualification of a workaround is dependent on the database we are using and its configuration. The Oracle database engine may use two different execution plans for the same SQL statement if it is ran on two different servers. This is especially true when you compare execution plans generated by different versions and editions of Oracle server. Using an Execution Plan as an indicator, we have found that among five presented solutions we have no difference in execution plan operation sequence; all execution plans indicate full scan on EMP table. Taking a close look at the execution plan will reveal that all five execution plans have identical plan hash value. This suggests that plan hash value can be used for comparing two or more different workaround candidates. From this point on we will be ultimately using it for workaround classification. SQL*Plus autotrace option has several output formats; one of them is a theoretical execution plan (via explain plan). In older releases of SQL*Plus, autotrace had its own formatting for explain plan, but effective release 10.2, it uses the now-standard DBMS_XPLAN package. (Continues...) Excerpted from Oracle SQL Tricks and Workaroundsby Zahar Hilkevich Copyright © 2011 by Zahar Hilkevich. Excerpted by permission of AuthorHouse. All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.