O'Reilly logo

Oracle Database 11gR2 Performance Tuning Cookbook by Ciro Fiorillo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Optimizing joins

One of the most time-consuming operations in a database is the JOIN. We use this when we need to join two or more tables due to the normalized structure of the database. There are many types of joins (equi-join, self-join, outer join, anti-join, and so on).

In this recipe, we will see some join algorithms the database can use to answer our queries, performance related to every type of join, and some tricks to avoid joins (when possible).

How to do it...

The following steps will demonstrate some common types of joins:

  1. Connect to the SH schema:
    CONNECT sh@TESTDB/sh
    
  2. Create a table called MY_CUSTOMERS as a copy of the CUSTOMERS table:
    CREATE TABLE sh.MY_CUSTOMERS AS SELECT * FROM sh.CUSTOMERS; ALTER TABLE sh.MY_CUSTOMERS ADD CONSTRAINT ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required