Lesson 1

In your previous experiences or courses you have learned the concept of joining tables and how to create queries that implement inner, outer, left, and right joins. This module looks into the unique way that Oracle handles table joins and sub-queries.

Let's delve into the nuances of how Oracle handles table joins and subqueries. While the underlying SQL concepts are standard, Oracle does provide both unique optimizations and some syntax variations to keep in mind:

1. Table Joins
   • Optimizer Dominates: The heart of Oracle's join handling is its sophisticated cost-based optimizer. It examines tables being joined, the column data, indexes, and statistics to determine the most efficient join method. Here's where Oracle stands apart:
     • Join Methods Variety: Oracle supports a wide array of join methods:
       • Nested Loop Joins
       • Sort-Merge Joins
       • Hash Joins
     • Adaptive Optimization: The optimizer may adjust its execution plan mid-query if it finds the initial strategy is not optimal.
   • Syntax Hints: While less common in modern implementations, Oracle lets you provide hints to the optimizer to influence its decision.
2. Subqueries
   • Transformation Techniques: Oracle often has the ability to transform subqueries into more efficient joins internally. This might happen for certain correlated subqueries or those with EXISTS operators.
   • Materialization: In some cases, Oracle may materialize the subquery result (store it as a temporary table) to improve performance, particularly if the subquery is referenced multiple times in the outer query.
   • Scalar Subqueries: Oracle handles scalar subqueries (those returning a single value) in a potentially unique way, sometimes allowing them to be used in places where regular subqueries might not be.

Additional Oracle-Specific Considerations

• Parallel Execution: Oracle can leverage parallel processing to speed up join and subquery operations, particularly when dealing with large datasets.
• Partitioning: If you use table partitioning, Oracle's optimizer can intelligently handle join operations utilizing relevant partitions rather than scanning the entire table.

Important Note: The Oracle optimizer is constantly evolving with each release. Its behavior may change with different versions and with the availability of fresh statistics on your tables.

By the end of this module, you will know how to:

1. Identify two proper syntax formats for the IN clause
2. Interpret an outer JOIN using Oracle’s syntax
3. Identify the connection between a query and a sub-query
4. Use EXISTS instead of IN for a sub-query
5. Describe the parsing sequence of a query that use a query in place of a table name

The module looks at joins and sub-queries by focusing on examples of inner and outer joins, subqueries and correlated subqueries.
In the next lesson, you will learn two different ways to code a sub-query that uses the IN clause.

A join combines the output from exactly two row sources, such as tables or views, and returns one row source. The returned row source is the data set. A join is characterized by multiple tables in the

1. WHERE (non-ANSI) or
2. FROM ... JOIN (ANSI) clause

of a SQL statement. Whenever multiple tables exist in the FROM clause, Oracle Database performs a join.
A join condition compares two row sources using an expression. The join condition defines the relationship between the tables. If the statement does not specify a join condition, then the database performs a Cartesian join, matching every row in one table with every row in the other table.

Within the Oracle database, the basic structure is a table. Oracle Database 13c supports many types of tables, including the following:

1. Relational tables: Using the Oracle-supplied datatypes you can create tables to store the rows inserted and manipulated by your applications. Tables have column definitions, and you can add or drop columns as the application requirements change. Tables are created via the create table command.
2. Object-relational tables: To take advantage of features such as type inheritance, you can use Oracle's object-relational capabilities. You can define your own datatypes and then use them as the basis for column definitions, object tables, nested tables, varying arrays, and more.
3. Index-organized tables: You can create a table that stores its data within an index structure, allowing the data to be sorted within the table.
4. External tables: Data stored in flat files may be treated as a table that users can query directly and join to other tables in queries. You can use external tables to access large volumes of data without ever loading them into your database. Note that Oracle also supports BFILE datatypes, a pointer to an external binary file. Before creating a BFILE or an external table, you must create a directory alias within Oracle (via the create directory command) pointing to the physical location of the file.
5. Partitioned tables: You can divide a table into multiple partitions, which allows you to separately manage each part of the table. You can add new partitions to a table, split existing partitions, and administer a partition apart from the other partitions of the table. Partitioning may simplify or improve the performance of maintenance activities and user queries. You can partition tables on ranges of values, on lists of values, on hashes of column values, or on combinations of those options.
6. Materialized views: A materialized view is a replica of data retrieved by a query. User queries may be redirected to the materialized views to avoid large tables during execution, the optimizer will rewrite the queries automatically. You can establish and manage refresh schedules to keep the data in the materialized views fresh enough for the business needs.
7. Temporary tables: You can use the create global temporary table command to create a table in which multiple users can insert rows. Each user sees only his or her rows in the table.
8. Clustered tables: If two tables are commonly queried together, you can physically store them together via a structure called a cluster.
9. Dropped tables: You can quickly recover dropped tables via the flashback table to before drop command. You can flash back multiple tables at once or flash back the entire database to a prior point in time. Oracle supports flashback queries, which return earlier versions of rows from an existing table.