Pluralsight Optimizing SQL Queries in Oracle – DVD

Optimizing SQL Queries in Oracle

Introduction

Course Overview

Course Contents

Module Contents

Who Will Benefit?

Database Version

Data Used in Course

A Comment about Tablespaces

Oracle Query Optimizer

Oracle and Pluralsight Resources

Your Database Administrator

Summary

Basic SQL Optimization Part I

Introduction

Module Contents

Primary/Foreign Keys

Normal Forms – Part I

Normal Forms – Part II

Normal Forms – Part III

Primary/Foreign Keys REDUX

Infrequent Columns

NULLs and Space

Summary

Basic SQL Optimization Part II

Introduction

Module Contents

Neaten Up SQL Code

Restrict Columns/Rows

INTERSECT vs. INNER JOIN

MINUS vs. JOIN

Correlated Subqueries

IN vs. EXISTS

Multi-Column IN vs. ANDs/ORs

The WITH Clause

The APPEND Hint

ON Clause vs. WHERE Clause

Order of Tables on FROM Clause

Cartesian Products

Summary

Overview of Indexes

Introduction

Module Contents

What is an Index?

B-Tree Indexes

Bitmap Indexes

Function-Based Indexes

Index-Organized Tables

Bitmap Join Indexes

Gathering Statistics

Index/Partition Interaction

Example

Summary

B-Tree Indexes

Introduction

Module Contents

What is a B-Tree Index?

When do you use a B-Tree Index?

Creating a B-Tree Index

Listing the Indexes on a Table

Dropping a B-Tree Index

Gathering Statistics

Is my Index Being Used?

Summary

Bitmap Indexes

Introduction

Module Contents

What is a Bitmap Index?

When do you use a Bitmap Index?

Creating a Bitmap Index

Dropping a Bitmap Index

Is my Index Being Used?

Summary

Additional Index Types

Introduction

Module Contents

Function-Based Indexes

Bitmap Join Indexes

Index-Organized Tables

Summary

Overview of Partitions

Introduction

Module Contents

What is a Partition?

Varieties of Partitions

Partition-Wise Joins

Partition/Index Interaction

Examples

Summary

Simple Partitions

Introduction

Module Contents

What is a List Partition?

Creating a List Partition

Examples of List Partitions

Adding to a List Partition

Dropping a List Partition

What is a Range Partition?

Creating a Range Partition

Examples of Range Partitions

Adding to a Range Partition

Dropping a Range Partition

Summary

Composite Partitions

Introduction

Module Contents

What is a Composite Partition?

Varieties of Composite Partitions

Example #1 – List/List

Example #2 – Range/List

Example #3 – AUTOTRACE

Summary

Partition/Index Interaction

Introduction

Module Contents

Partition/Index Interaction

Global Non-Partitioned Indexes

Local Partitioned Indexes

Changes in Partitions Affect Indexes

Summary