QUERY OPTIMIZATION
INTRODUCTION
A query typically has many possible
execution strategies, and the process of
choosing a suitable one for processing a
query is known as query optimization.
query optimization is a
combination of two word
Query:-Question
Optimization:-selection
optimization
Minimize response time
Minimize resource consumption
Minimize time to first tuple
Maximize throughput
Processing
Query in high level language
Scanning, parsing
Immediate form of query
Query optimizer
Execution plan
Query code generator
Code to execute the query
Query processor
Result of query
Architecture
Rewriter
Rewriting stage (declarative)
Planning Stage (procedural)
Algebraic space
Cost model
Planner
Method-structure
Size-distribution
space
Estimator
TYPE OF QUERY OPTIMIZATION
SEMANTIC QUERY OPTIMIZATION
it is form of optimization mostly related to the rewriter module
integrity constraints defined in the database to rewrite a given
query.
GLOBAL QUERY OPTIMIZATION
when multiple query start for optimize at the same time then
that time only one query optimize. This technique is known
as global query optimization.
PARAMETRIC/DYNAMIC QUERY OPTIMIZATION
query optimize one time and use many time i.e run time.
HOW TO WRITE QUERY
The query work faster if we use actual
column name in select statement instead
of “*”;
general query
select * from student.
query optimization
select student_id,last_name,age,subject
from student.
HOW TO IMPLIMENT JOIN OPERATION
An sql join clause combines record from two or
more table in a database. It also combining field
from two tables by using values common to each.
Type of join operation
SORT MARGE JOIN
HAS JOIN
NESTED LOOP
SORT MARGE JOIN
Sort Marge join also known by Marge join and use in the
implementation of a relational database management system .
A sort merge join is a join optimization method
where two tables are sorted and then joined.
SELECT * FROM emp, dept
WHERE emp.deptno = dept.deptno;
SELECT * FROM dept
INNER JOIN emp ON (emp.deptno = dept.deptno);
SELECT * FROM emp NATURAL JOIN dept;
CREATE TABLE t1 (c1 NUMBER PRIMARY KEY);
CREATE TABLE t2 (
c1 NUMBER PRIMARY KEY,
c2 NUMBER REFERENCES t1(c1),
c3 VARCHAR2(30) );
Definition
OLTP (online transaction processing) is a class of program that
facilitates and manages transaction-oriented applications,
typically for data entry and retrieval transactions in a number of
industries, including banking, airlines, mailorder, supermarkets,
and manufacturers. Probably the most widely installed OLTP
product is IBM's CICS (Customer Information Control System).
Today's online transaction processing increasingly requires
support for transactions that span a network and may include
more than one company. For this reason, new OLTP software
uses client/server processing and brokering software that allows
transactions to run on different computer platforms in a network.
Cost
The query optimizer is the component of a database management system that
attempts to determine the most efficient way to execute a query.
The optimizer considers the possible query plans for a given input query, and
attempts to determine which of those plans will be the most efficient.
Cost-based query optimizers assign an estimated "cost" to each possible query
plan, and choose the plan with the smallest cost.
Costs are used to estimate the runtime cost of evaluating the query, in terms of
the number of I/O operations required, the CPU requirements, and other factors
determined from the data dictionary. The set of query plans examined is formed by
examining the possible access paths (e.g. index scan, sequential scan) and join
algorithms (e.g. sort-merge join, hash join, nested loops). The search space can
become quite large depending on the complexity of the SQL query.
Generally, the query optimizer cannot be accessed directly by users: once queries
are submitted to database server, and parsed by the parser, they are then passed
to the query optimizer where optimization occurs. However, some database
engines allow guiding the query optimizer with hints.
OLTP cannot store historical information
about the organization. It is used for
storing the details of daily transactions
while a datawarehouse is a huge storage
of historical information obtained from
different datamarts for making intelligent
decisions about the organization.
Deference
Data warehouse database OLTP database
Designed for analysis of Designed for real time
business measures by business operations.
categories and attributes Optimized for a common set
Optimized for bulk loads and of transactions, usually
large, complex, unpredictable adding or retrieving a single
queries that access many row at a time per table.
rows per table. Optimized for validation of
Loaded with consistent, valid incoming data during
data; requires no real time transactions; uses validation
validation. data tables.
Supports few concurrent Supports thousands of
users relative to OLTP concurrent users.
