nach oben

2018 | Buch

Kapitel lesen Erstes Kapitel lesen

Oracle SQL Revealed

Executing Business Logic in the Database Engine

verfasst von: Alex Reprintsev

Verlag: Apress

Enthalten in: Springer Professional "Wirtschaft+Technik" , Springer Professional "Technik" , Springer Professional "Wirtschaft"

Einloggen, um Zugang zu erhalten

Über dieses Buch

Write queries using little-known, but powerful, SQL features implemented in Oracle's database engine. You will be able to take advantage of Oracle’s power in implementing business logic, thereby maximizing return from your company’s investment in Oracle Database products.
Important features and aspects of SQL covered in this book include the model clause, row pattern matching, analytic and aggregate functions, and recursive subquery factoring, just to name a few. The focus is on implementing business logic in pure SQL, with a comparison of different approaches that can be used to write SELECT statements to return results that drive good decision making and competitive action in the marketplace.
This book covers features that are often not well known, and sometimes not implemented in competing products. Chapters on query transformation and logical execution order provide a grasp of the big picture in which the individual SQL features described in the other chapters are executed. Also included are a discussion on when to use the procedural capabilities from PL/SQL, and a series of examples showing different mixes of SQL features being applied in common types of queries that you are likely to encounter.
What You Will LearnGain competitive advantage from Oracle SQL
Know when to step up to PL/SQL versus staying in SQL
Become familiar with query transformations and join mechanicsApply the model clause and analytic functions to business intelligence queriesMake use of features that are specific to Oracle Database, such as row pattern matching
Understand the pros and cons of different SQL approaches to solving common query tasksTraverse hierarchies using CONNECT BY and recursive subquery factoringWho This Book Is For
Database programmers with some Oracle Database experience. The book is also for SQL developers who are moving to the Oracle Database platform or want to learn unique features of its query engine. Both audiences will learn to apply the full power of Oracle’s own SQL dialect to commonly encountered types of business questions and query challenges.

Inhaltsverzeichnis

Frontmatter

Features and Theory

Frontmatter

Chapter 1. Joins

Abstract

Most real-life queries combine data from multiple tables instead of querying a single table. Logic may be encapsulated in a view to hide complexity from the end user, but the database accesses multiple tables anyway to get the result set. It’s not necessary that data from all tables in a query appear in the result; some tables may be used to filter out data from other tables, for example.

Alex Reprintsev

Chapter 2. Query Transformations

Abstract

The same logic can be implemented using various but semantically equivalent queries that look quite different but have the same plans and performance. This is achieved as a result of query transformations – original queries transform into the same final query.

Alex Reprintsev

Chapter 3. Analytic Functions

Abstract

Basic SQL provides row-level visibility, and aggregate functions allow us to analyze data in groups so that each row corresponds to one specific group according to group by expressions (more details about aggregate functions provided in the next chapter, “Aggregate Functions”).

Alex Reprintsev

Chapter 4. Aggregate Functions

Abstract

Aggregate functions return one row for each group defined in a group by clause. Both column names and expressions can be used to define groups, and one group is a set of rows with the same values for all expressions specified in “group by.” Each row belongs to one and only one group. If “group by” is not specified, then the entire recordset is a single group, and in this case query always returns one row even if the recordset to be grouped is empty.

Alex Reprintsev

Chapter 5. Hierarchical Queries: Connect by

Abstract

The connect by clause is used to query hierarchies if they are stored as parent-child relationships, also known as an adjacency lists model. Simply speaking, this model means that a parent – child pair is stored for each child. In general adjacency lists can represent directed graphs, not only hierarchy trees; in this case the list describes the set of neighbors of a vertex in the graph. So an adjacency list model is much wider and a parent-child model is one of its implementations.

Alex Reprintsev

Chapter 6. Recursive Subquery Factoring

Abstract

A subquery factoring clause (sometimes also referred to as a “with clause” or CTE – common table expression) was introduced in Oracle 9.2. At that time it did not allow us to define recursive subqueries and was mainly used to decompose the logic into named queries – factor out subqueries and reference them by names in the main query. CBO can decide whether to materialize results of factored out subqueries or plug them in as inline views. In the former case it can improve performance if the named query is referenced multiple times in the main query, while in the latter case it may have a negative impact on the performance because the transformation engine doesn’t treat named queries in the same way as inline views. For example, on older versions Oracle could have merged an inline view but not the named query with exactly the same text.

Alex Reprintsev

Chapter 7. Model

Abstract

One may say that the model clause is the most powerful SQL feature, meaning that it can be used to solve numerous tasks that otherwise would not be resolvable using SQL. This can be accomplished not only because the model clause considerably extends possibilities of declarative SQL, but in addition to that it introduces ability of iterative computations in SQL on top of a recordset. On the other hand, the model clause has some issues with scalability and, in general, a class of problems where the model shines is quite limited. In many cases PL/SQL is preferable even though a result can be achieved using a model clause, but first things first.

Alex Reprintsev

Chapter 8. Row Pattern Matching: match_recognize

Abstract

This is required in many business areas, for example, security applications and fraud detection or financial applications and pricing analysis. Native pattern-matching capabilities in SQL help to avoid complex bespoke solutions on the client side or within the middle-tier application server and use easy-to-share SQL queries instead.

Alex Reprintsev

Chapter 9. Logical Execution Order of Query Clauses

Abstract

Oracle allows the combining of various query clauses on the same layer of a single query, from basic features like joins, filtering, and grouping to advanced constructions like model clause or pattern matching. Sometimes it’s not possible to achieve the result using single select … from a query block so you may have to create additional inline views in the query – for example, when you want to filter by the value of an analytic function. However, even if you can implement the entire logic using a single query block – it’s not always necessary, because Oracle can eliminate inline views during the query transformations. Moreover, in some cases additional inline views may help to improve the performance as it will be shown in the end of the chapter.

Alex Reprintsev

Chapter 10. Turing Completeness

Abstract

Turing completeness is a very important notion in computer science because being Turing complete means that your model of computations can execute any algorithm no matter how complex it is, what data structures are used, and how much storage or time would be needed to evaluate it. SQL can be considered as yet another example of a model of computations and even though it's not supposed to be used to implement any algorithm or business logic, it's interesting to analyze whether it's Turing complete or not for the sake of completeness. Moreover, as will be shown in the next chapter "When PL/SQL Is Better Than Vanilla SQL," sometimes pure SQL is not the best way to get the result even if an algorithm can be easily implemented using it.

Alex Reprintsev

PL/SQL and SQL solutions

Frontmatter

Chapter 11. When PL/SQL Is Better Than Vanilla SQL

Abstract

Real-life tasks that can be solved using Oracle SQL are covered in Part II of this book. PL/SQL solutions are also provided to highlight the limitations of SQL or to demonstrate that PL/SQL may be a preferred solution from a performance point of view even if the SQL solution looks concise and easy. Some real cases are presented as better than vanilla SQL.

Alex Reprintsev

Chapter 12. Solving SQL Quizzes

Abstract

In this last chapter I’d like to consider specific real-life tasks and their solutions in SQL to demonstrate the power of Oracle SQL. The complexity of the tasks will vary a lot as well as the depth of analysis for different solutions. For a few tasks there will be both SQL and PL/SQL solutions, but the main accent in this chapter is on SQL capabilities.

Alex Reprintsev

Appendix A. Useful Oracle Links

Abstract

A Look Under The Hood of CBO: THE 10053 Event

Alex Reprintsev

Backmatter

Titel: Oracle SQL Revealed
verfasst von: Alex Reprintsev
Verlag: Apress
Electronic ISBN: 978-1-4842-3372-6
Print ISBN: 978-1-4842-3371-9
DOI: https://doi.org/10.1007/978-1-4842-3372-6