Query optimization techniques in SQL

Name: Query optimization techniques in SQL
Rating: 3.3 (287 reviews)

This is an advanced course which embodies an aggregation of all the neccessery techniques to write optimized queries.

Created byNikita Vasilev, Arsen Petrenko, Veronika Vasileva

Last updated 2/2022

English

English [Auto],

What you'll learn

To learn how to read and use the query plan in SQL.
To study different join algorithms and best conditions of it application.
To master the optimal usage of indexing depending on the task
To master superior features and functionality of SQL by using partitioning, hints, caching, e.t.c

Course content

2 sections • 10 lectures • 57m total length

Introduction0:55
Master SQL at middle and senior levels, study query optimization with Oracle as an example, and sharpen skills in aggregate and analytical functions, and writing your own functions for interviews.
Query Plan3:22
Analyze the query plan to compare explain plans with actual execution, guided by the database optimizer that evaluates join algorithms, indexes, sorting, and aggregation to optimize SQL queries.
How to read the query plan4:46
Learn how to read a query plan by following the tree of operations, understanding parent–child relationships, and evaluating index usage, range scans, and nested loops with optimizer statistics.

Join algorithms5:43
The optimizer selects among nested loop, hash join, and sort-merge join to optimize a join, noting nested loop simplicity, hash join efficiency on large data, and sort-merge requires sorting.
Indexes8:20
Indexes speed up searches by scanning only a subset of rows. Types include B-tree, bitmap, reverse, inverted, spatial, and functional indexes, each with trade-offs for inserts and updates.
Hints4:56
Explore how hints influence the SQL query optimizer, pruning branches and guiding plan selection, while noting that statistics, complex joins, and feature limits affect performance.
Statistics2:56
Oracle's optimizer uses statistics to compare the costs of alternative plans and select efficient execution paths, considering join orders, data distribution, and storage characteristics of tables and indexes.
Partitioning10:13
Explore partitioning as a design approach to split tables and indexes, covering horizontal and vertical partitions, range, hash, interval, reference, and composite methods, plus local and global indexes.
Caching7:39
Learn how the Oracle result cache stores query and function results in memory to speed up frequent calls, with declarative caching, automatic invalidation, and initialization parameter settings.
Temporary, staging tables, and materialized views8:15
Discover how temporary and staging tables store intermediate data to streamline complex queries, reduce redo logs, and how materialized views speed up frequent queries with controlled refresh and indexing.

Requirements

Basic SQL knowledge including the ability to write basic SQL queries, to use aggregate and analytical functions and to write your own functions.

Description

This course is designed for people who want to master SQL at the middle and senior levels. We will discuss the Oracle database as an example, but all the working and research methods can be applied to other relational databases.

In our course, we will talk about such an important aspect as query optimization and will deeper analyze the theoretical questions that may be useful not only for successful work, but also for the interviews. The focus will be on the technical implementation of the acquired knowledge, paying the most attention to the "under the hood" operation.

What you should already be able to do

- write basic SQL queries

⁃ use aggregate functions

⁃ use analytical functions

⁃ write your own functions (in PLSQL or, speaking of Oracle, using the with construction: a new feature introduced by Oracle, and according to the presentation, it should work 4 times faster than PLSQL variant).

In this course we will go through:

- query plan and how to read it

- join algorithms

- hints and statistics

- indexing

- caching

- partitioning

- use of temporary, intermediate tables and materialized views

Mastering the topics mentioned above will drastically improve an overall perfomance of your SQL queries and will allow you to properly time manage your applications with the most efficient use of available resources.

Who this course is for:

Developers/analysts who are working with databases and want to do it more efficiently.

Query optimization techniques in SQL

What you'll learn

Explore related topics

Course content

Introduction3 lectures • 9min

Main part7 lectures • 48min

Requirements

Description

Who this course is for: