Alex Reprintsev - Oracle SQL Revealed: Executing Business Logic in the Database Engine
Here you can read online Alex Reprintsev - Oracle SQL Revealed: Executing Business Logic in the Database Engine full text of the book (entire story) in english for free. Download pdf and epub, get meaning, cover and reviews about this ebook. year: 2018, publisher: Apress, genre: Computer. Description of the work, (preface) as well as reviews are available. Best literature library LitArk.com created for fans of good reading and offers a wide selection of genres:
Romance novel
Science fiction
Adventure
Detective
Science
History
Home and family
Prose
Art
Politics
Computer
Non-fiction
Religion
Business
Children
Humor
Choose a favorite category and find really read worthwhile books. Enjoy immersion in the world of imagination, feel the emotions of the characters or learn something new for yourself, make an fascinating discovery.
- Book:Oracle SQL Revealed: Executing Business Logic in the Database Engine
- Author:
- Publisher:Apress
- Genre:
- Year:2018
- Rating:5 / 5
- Favourites:Add to favourites
- Your mark:
Oracle SQL Revealed: Executing Business Logic in the Database Engine: summary, description and annotation
We offer to read an annotation, description, summary or preface (depends on what the author of the book "Oracle SQL Revealed: Executing Business Logic in the Database Engine" wrote himself). If you haven't found the necessary information about the book — write in the comments, we will try to find it.
Write queries using little-known, but powerful, SQL features implemented in Oracles database engine. You will be able to take advantage of Oracles power in implementing business logic, thereby maximizing return from your companys 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 Learn
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 Oracles own SQL dialect to commonly encountered types of business questions and query challenges.
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 Learn
- Gain competitive advantage from Oracle SQL
- Know when to step up to PL/SQL versus staying in SQL
- Become familiar with query transformations and join mechanics
- Apply the model clause and analytic functions to business intelligence queries
- Make 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 tasks
- Traverse hierarchies using CONNECT BY and recursive subquery factoring
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 Oracles own SQL dialect to commonly encountered types of business questions and query challenges.
Alex Reprintsev: author's other books
Who wrote Oracle SQL Revealed: Executing Business Logic in the Database Engine? Find out the surname, the name of the author of the book and a list of all author's works by series.
Stackowiak Robert
![Molinaro - SQL cookbook [query solutions and techniques for database developers ; covers SQL server, PostgreSQL, Oracle, MySQL, and DB2]](/uploads/posts/book/221182/thumbs/molinaro-sql-cookbook-query-solutions-and.jpg)
McLaughlin
Panzarino
Onofrio Panzarino
Neagu Adrian
Rick Greenwald
Oracle SQL Revealed: Executing Business Logic in the Database Engine — read online for free the complete book (whole text) full work
Below is the text of the book, divided by pages. System saving the place of the last page read, allows you to conveniently read the book "Oracle SQL Revealed: Executing Business Logic in the Database Engine" online for free, without having to search again every time where you left off. Put a bookmark, and you can go to the page where you finished reading at any time.
Light
Font size:
↓
↑
Reset
Interval:
↓
↑
Bookmark:
Make
Part I
Features and Theory
Features and Theory
Features and Theory
Chapters are organized to follow the list below of Oracle SQL features:
- All joins can be implicitly specified in the query; however sometimes it makes sense to use subqueries, for example, for a more efficient way to implement an ANTI/EQUI join. Correlated scalar subqueries may be more efficient than outer joins because of scalar subquery caching.
- Query transformations make it possible for two queries with quite different text to have the same plan and performance. On the other hand, query transformations are not a universal panacea and sometimes manual query refactoring is required to achieve the best performance.
- Analytic functions are an invaluable feature that helps to implement tricky logic without joins. On the other hand, they almost always require a sort, which may be an issue on big data volumes.
- Aggregate functions allow us to group data and calculate aggregate values as well as implement some complex flattening or pivoting logic.
- Connect by is the best tool to traverse hierarchies or generate lists; however it should not be used to traverse graphs despite built-in capabilities to handle cycles if performance is critical.
- Recursive subquery factoring extends capabilities of traversing hierarchies in a way that you can refer values calculated on a previous level. When recursive subquery factoring is used for iterative transformations of a dataset you should take into account that a new recordset is generated on each iteration, which leads to intensive memory consumption. A functional advantage in comparison to a model clause is that you can calculate multiple measures on each step. In case of a model clause, the first measure is evaluated for all specified rows, then the second one, one and so on.
- Model is the most powerful SQL feature but its shining in quite specific cases. Model may require intensive CPU and memory consumption and does not scale well enough for millions of rows; however performance can be dramatically improved in case of parallel execution of partitioned models.
- Row pattern matching adds noticeable flexibility for analysis of recordsets. This feature is the only way to solve a wide range of tasks in pure SQL in a scalable and efficient manner, and in addition it demonstrates a bit better performance for those tasks that can also be solved using analytic functions.
- One query block may contain various clauses including joins, aggregate and analytic, or even mixes of advanced features like model clause and pattern matching. Its important to understand how this will be executed from a logical point of view and what are the pros and cons of using inline views.
- It was proven that SQL is Turing complete language and for academic purposes it was shown how to implement arbitrary algorithms using an iterative model. SQL is a declarative language though and was designed to manipulate data and not for iterative computations.
Tom Kyte wrote many times over the years, You should do it in a single SQL statement if at all possible. Id like to elaborate on this statement a little bit. Even if we remove from consideration advanced features like recursive subquery factoring, model clause, row pattern matching, and connect by, there are some tasks that can be solved more efficiently using PL/SQL. Various examples will be considered in Chapter to provide more background.
Alex Reprintsev 2018
Alex Reprintsev Oracle SQL Revealed1. Joins
Alex Reprintsev 1
(1)
London, UK
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. Its 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.
Data from two tables may be combined using join (a join keyword is not mandatory as it will be shown later), subquery (may be correlated or not), lateral view (starting with Oracle 12c), or set operators (union/union all/intersect/minus).
Any logic implemented using set operators or subqueries may be rewritten with joins, but this may not be always optimal from a performance point of view. Moreover, semantically equivalent queries may be rewritten into the same query after query transformations are applied (see details in Chapter , Query Transformations) or may have the same execution plan even if they have not been rewritten into the same query. Lateral views can be imitated using a table operator on older versions.
Looking forward, let me mention that some queries that use only one table may be not very easy to understand and may contain quite complex logic, but that is a rare case (a lot of such queries you can find in Part II).
This chapter covers joins (both ANSI and traditional Oracle outer joins syntax) along with some details about subqueries, lateral views, and join methods.
ANSI Joins
The following tables will be used for demonstration.
create table t1(id, name) as
select 1, 'A' from dual union all select 0, 'X' from dual;
create table t2(id, name) as
select 2, 'B' from dual union all select 0, 'X' from dual;
Listing 1-1
Tables for demonstration
- Cross join (also called Cartesian product). It returns all possible combinations of two tables rows.select *from t1cross join t2;ID N ID N---------- - ---------- -1 A 2 B1 A 0 X0 X 2 B0 X 0 XListing 1-2Cross join
- Inner join join type that returns those and only those rows from both joined tables satisfying a join predicate (i.e., predicate evaluates into TRUE).select *from t1join t2on t1.id = t2.id;ID N ID N---------- - ---------- -0 X 0 XListing 1-3Inner join
The table before the join keyword is called a left joined table, and the table after the join keyword is called a right joined table. For an inner join it does not matter which table is left and which one is right, as the result will always be the same for the same tables and join predicate.
A predicate may not always be an equality condition; it can be any expression that evaluates into TRUE, FALSE, or UNKNOWN. UNKNOWN acts almost like FALSE; if a join predicate evaluates into UNKNOWN for given rows from two tables, then they will not be part of the result set. However, if atomic predicates are combined using AND, OR, NOT conditions, then the result may be different if the subexpression evaluates to UNKNOWN and not to FALSE. For example, NOT FALSE evaluates to TRUE, but NOT UNKNOWN evaluates to UNKNOWN.
Speaking about joins, the terms condition, predicate, and criteria are interchangeable.
Its not mandatory that columns from both tables should be used in the predicate. t1.id > 0 is also a valid join condition. All rows from table t2 satisfy this condition and only one row from table t1 does.
select *
from t1
join t2
on t1.id > 0;
ID N ID N
---------- - ---------- -
1 A 2 B
1 A 0 X
Listing 1-4
Light
Font size:
↓
↑
Reset
Interval:
↓
↑
Bookmark:
Make
Similar books «Oracle SQL Revealed: Executing Business Logic in the Database Engine»
Look at similar books to Oracle SQL Revealed: Executing Business Logic in the Database Engine. We have selected literature similar in name and meaning in the hope of providing readers with more options to find new, interesting, not yet read works.
Stackowiak Robert
McLaughlin
Panzarino
Onofrio Panzarino
Neagu Adrian
Rick Greenwald
Reviews about «Oracle SQL Revealed: Executing Business Logic in the Database Engine»
Discussion, reviews of the book Oracle SQL Revealed: Executing Business Logic in the Database Engine and just readers' own opinions. Leave your comments, write what you think about the work, its meaning or the main characters. Specify what exactly you liked and what you didn't like, and why you think so.