Structured Query Language
Advanced SQL Query Optimization Techniques
Copyright 2021 - All rights reserved.
The content contained within this book may not be reproduced, duplicated or transmitted without direct written permission from the author or the publisher.
Under no circumstances will any blame or legal responsibility be held against the publisher, or author, for any damages, reparation, or monetary loss due to the information contained within this book, either directly or indirectly.
Legal Notice:
This book is copyright protected. It is only for personal use. You cannot amend, distribute, sell, use, quote or paraphrase any part, or the content within this book, without the consent of the author or publisher.
Disclaimer Notice:
Please note the information contained within this document is for educational and entertainment purposes only. All effort has been executed to present accurate, up to date, reliable, complete information. No warranties of any kind are declared or implied. Readers acknowledge that the author is not engaging in the rendering of legal, financial, medical or professional advice. The content within this book has been derived from various sources. Please consult a licensed professional before attempting any techniques outlined in this book.
By reading this document, the reader agrees that under no circumstances is the author responsible for any losses, direct or indirect, that are incurred as a result of the use of information contained within this document, including, but not limited to, errors, omissions, or inaccuracies.
Table of Contents
Introduction
Understanding Databases Today
Structure of this Book
Database Queries
Chapter 1: Structured Query Language in Oracle
The History of the SQL
Variations in the SQL Today
Procedural Language for SQL by Oracle
The Syntax of PL/SQL
Queries in the SQL & their Syntax
Chapter 2: Query Processing & Query Optimization
The Phases of Query Processing
Fundamentals of Query Optimization
Chapter 3: Query Optimization Techniques
What is Optimization?
What a Query Does
The Tools
What the Query Optimizer Does
Common Query Optimization Themes
Joins and WHERE Clauses Wrapped in Functions
Implicit Conversions
Chapter 4: Query Optimization Tips and Tricks
Tips and Tricks
Chapter 5: Using the Rule-Based Optimizer
Understanding the RBO Access Paths
Using RBO to Choose an Execution Plan for a Join
Using the RBO to Transform and Optimize Statements
Alternative SQL Syntax
Chapter 6: Using the Cost-Based Optimizer and Database Statistics
DBMS_STATS
Chapter 7: Reading an Execution Plan
What Is An Execution Plan?
Chapter 8: SQL Server Query Optimization Techniques
Understanding the Application
Conclusion
References
Introduction
At their inception, databases were nothing more than a simple system that could save a handsome number of records for an organization. This included databases for employees, customers, transactions, and the budget. Initially, these different types had to be saved separately in different databases. However, with time, these systems became more and more complex and allowed more flexibility to the database administrators and the organizations managing them. We saw the introduction of a variety of features like relations, joins, indexes, and queries. As of now, databases have proven themselves to be one of the most powerful tools in human history. From large corporate giants like Facebook and Google to small settlements like a local pharmacy or supermart, the database has evolved from a facilitative role to one of necessity. Any business that wants to guarantee its success must have a database (for the starters). Without that, its survival cannot be guaranteed at all. Moreover, organizations compete with each other with regard to data, and better and more optimized usage of the data provided to them in those databases serves as a key to their success.
Understanding Databases Today
Long gone are the days where databases were used as a means just to store necessary data. In fact, the very name database came into existence only after its being built upon Codd's model. While data storage is still an important component of database systems, it is not the only primary goal for having databases, except for home use - some individuals may have simple databases on their personal computers. Today's targeted (or wider) purpose of databases is to make sense of data by extracting the required information and putting the conclusions derived from that information to good use. This fundamental use of databases makes use of a very important concept (i.e., querying) and can be broken down into two fundamental dimensions. They are discussed below.
Database Handling for Monetization Purposes
All big organizations use databases today to further expand their businesses and generate even more revenue for themselves. Of course, this task is not as easy as it sounds and may require a lot of time and patience to benefit from. First, organizations make use of databases to store important user data. Then, that data is analyzed through time to consider the ongoing business trends that the company can use to predict their most profitable moves for the future. This is a basic technique used by all existing businesses, from Facebook to large departmental stores.
For example, Facebook keeps track of each user's activity stored in a database to determine what kind of content and advertisements should be shown to the user. This aims to increase Facebook usage time for regular users significantly, and the more someone uses this application, the more it benefits Facebook. This probably explains why you spend so much time every day needlessly scrolling your Facebook feed.
The departmental stores, likewise, do the same thing while using a starkly different approach. They may use the databases to determine what kinds of products are usually brought by the customer together. They may put them in the same place, thereby increasing the chances of both of the things being bought. The same technique is used by eCommerce sites like Amazon, where you see a list of recommended products with a product you are buying or considering buying.
You may have noticed (at least partly) that there are more things in play here, along with database handling. Those things mainly include a deep understanding of human psychology, without which the efficient use of databases cannot be ensured. Remember this point as it will be important for us in our discussion on query optimization.
Database Handling for Research Purposes
Research today is extremely dependent upon database systems. The point is to store enough data that can be processed in the future to derive important scientific conclusions. Suppose our research is in the domains of Physics or Computer Science. In that case, we may require data on a lot of calculations in a particular domain, which could be used to understand the computers' universe better, subsequently. On the other hand, if the research is in the domain of psychology or biology, we may need a record of human behavior to certain stimuli or different contexts to better facilitate the research.
These pieces of information are analyzed with different environments and circumstances at hand, and through that, researchers determine if there is any valid correspondence. Without a proper database, it would be hard to track all the contexts or determine the reason for any consequence of that research.
