2_Optimizer Operation

■Access path: 

・The optimizer must choose an access path to retrieve data from each table in the join statement such as use indexes, full table scans, or materialized views.

・In general, index access paths should be used for statements that retrieve a small subset of table rows, while full scans are more efficient when accessing a large portion of the table.

Choosing an Access Path：

①Available access paths for the statement

②Estimated cost of executing the statement, using each access path or combination of paths
Influenced by ：Optimizer hints > Old statistics

•

1.Full Table Scans

　 ①lack of index 

  ②large amount of data( will access most of the blocks in the table)     ③small table (If a table contains blocks fewer than the value of DB_FILE_MULTIBLOCK_READ_COUNT under the high-water mark, )

2.Row ID Scans

is the fastest way to retrieve a single row.

3.Index Scans

①Index Unique Scan

returns at most a single row ID.

if a statement contains a UNIQUE or a PRIMARY KEY constraint that guarantees that only a single row is accessed.  

②Index Range Scan

An index range scan is a common operation for accessing selective data. 

in conditions, such as col1 = :b1, col1 < :b1, col1 > :b1

Wildcard searches (col1 like '%ASD') should not be in a leading position, as this does not result in a range scan

A NOT NULL constraint can influence the optimizer to use an available index on the column to to satisfy an ORDER_BY clause, thus avoiding further sorting

Hint：/*+INDEX(table_alias index_name)*/

③Index Range Scan Descending

An index range scan descending is identical to an index range scan, except that the data is returned in descending order. 

Hint：/*+INDEX_DESC(table_alias index_name)*/

④Index Skip Scan

Index skip scans improve index scans by skipping blocks that could never contain keys matching the filter column values.

Scanning index blocks is often faster than scanning table data blocks. Skip scanning can happen when the initial column of the composite index is not specified in a query. 

Hint：/*+INDEX_SS*/ 

⑤Index Full Scans

A full scan is available if a predicate references one of the columns in the index. 

A full scan is also available when there is no predicate if both the following conditions are met:

・All of the columns in the table referenced in the query are included in the index. 

・At least one of the index columns is not null. 

A full scan can be used to eliminate a sort operation, because the data is ordered by the index key. 

⑥Fast-Full Index Scans

Fast-full index scans are an alternative to full table scans when the index contains all the columns that are needed for the query and at least one column in the index key has the NOT NULL constraint. 

You can specify it with the initialization parameter OPTIMIZER_FEATURES_ENABLE or 

Hint：/*+INDEX_FFS*/ 

Fast-full index scans cannot be performed against bitmap indexes.  

A fast-full scan is faster than a normal full index scan because it can use multiblock I/O and can be parallelized just like a table scan

⑦Index Joins

An index join is a hash join of several indexes that together contain all the table columns that are referenced in the query. 

You can specify an index join with the initialization parameter OPTIMIZER_FEATURES_ENABLE or

Hint：/*+INDEX_JOIN*/

⑧Bitmap Joins

A bitmap join uses a bitmap for key values and a mapping function that converts each bit position to a row ID. 

■Joining Multiple Tables

For each join statement, the optimizer determines the following (in sequence):

1.Order in which to join the tables

2.Best join operation to apply for each join

3.Access path for each row source

Join Terminology：

・A join predicate： is a predicate in the WHERE clause that combines the columns of two of the tables in the join.

・A nonjoin predicate： is a predicate in the WHERE clause that references only one table.

・A single-row predicate： is an equality predicate on a column with a unique or primary key constraint, or a column with a unique index without a corresponding constraint. 

・A natural join： is a join statement in which the join predicate automatically joins between all the matched columns in both tables using the equality (=) operator. 

・A cross join: is a join statement in which there is no join predicate

Note: Cartesian joins and natural joins are best avoided by specifying the join columns, because they can use up resources and get poor results.

Join Order Rules：

・A single-row predicate forces its row source to be placed first in the join order.

・For outer join statements, the outer-joined table must come after (in the join order) the other table in the join predicate.

■Join Methods

・Nested loop join: Useful when small subsets of data are being joined and if the join condition is an efficient way of accessing the second table

・Hash join: Used for joining large data sets. 

・Sort-merge join: Can be used to join rows from two independent sources. 

Hash joins generally perform better than sort-merge joins. 

On the other hand, sort-merge joins can perform better than hash joins if both of the following conditions are met:

①The row sources are already sorted. 

②A sort operation does not have to be done.

1.Nested loop join

The outer (driving) table is usually accessed with a full table scan.

hint：USE_NL(table1 table2)

If you have a non-equijoin, a hash join is not possible

2.Hash Joins：

1.The optimizer uses the smaller of two tables or data sources to build a hash table on the join key in memory.

2. The optimizer then scans the larger table, probing the hash table to find the joined rows.

hint：USE_HASH 

3.Sort-merge join

4.Star Joins

■Subqueries and Joins

Subqueries (like joins) are statements that reference multiple tables.

• Subquery types:

– Noncorrelated subquery

– Correlated subquery

– NOT IN subquery (antijoin)

– EXISTS subquery (semijoin)

1.Noncorrelated subquery

A noncorrelated subquery does not contain references to the outer (main) query and can be executed independently

eg:

SELECT c.*

FROM   customers c

WHERE  c. country_id IN 

          (SELECT co.country_id

           FROM   countries co

           WHERE  co.country_subregion = 'Asia');

2.Correlated Subqueries

eg:

  SELECT p1.*

       FROM   promotions p1

       WHERE  p1.promo_cost =

              (SELECT MAX(p2.promo_cost)

               FROM   promotions p2

              WHERE p1.promo_category = p2.promo_category)

3.Antijoins

An antijoin is a select statement with a subquery with a NOT IN predicate

eg:

      SELECT c.*

       FROM   customers c

       WHERE  c.cust_income_level = 'F: 110,000 - 129,999'

       AND    c.country_id NOT IN 

                  (SELECT co.country_id

                   FROM countries co

                   WHERE co.country_subregion = 'Europe');

4.Semijoins

A semijoin returns rows that match an EXISTS subquery without duplicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery. 

eg:

SELECT e.employee_id, e.first_name, e.last_name, e.salary    

     FROM hr.employees e  

     WHERE e.department_id = 80

           AND e.job_id = 'SA_REP'

           AND EXISTS (SELECT 1

                          FROM oe.orders o

                          WHERE e.employee_id = o.sales_rep_id);  

■Sort Operations

Sort operations result when users specify an operation that requires a sort. 

・SORT UNIQUE： occurs if a user specifies a DISTINCT clause or if an operation requires unique values for the next step.

・SORT AGGREGATE： does not actually involve a sort. It is used when aggregates are being computed across the whole set of rows.

・SORT GROUP BY： is used when aggregates are being computed for different groups in the data. The sort is required to separate the rows into different groups.

・SORT JOIN： happens during a sort-merge join if the rows need to be sorted by the join key.

・SORT ORDER BY： is required when the statement specifies an ORDER BY that cannot be satisfied by one of the indexes.

■Tuning Sort Performance

1.Note that several SQL language components cause implicit sorts, such as DISTINCT, GROUP BY, UNION, MINUS, and INTERSECT. 

Make sure that you get only the sorts that are needed for your result.

2.Sorts can be avoided by creating indexes. 

Concatenated indexes, with the appropriate DESC or ASC attributes, can especially help avoid sorts. 

3.An index fast-full scan (using multiblock I/O) does not guarantee that the rows are returned in the correct order

■Top-N SQL

SELECT *

FROM (SELECT prod_id

, prod_name

, prod_list_price

, prod_min_price

FROM products

ORDER BY prod_list_price DESC)

WHERE ROWNUM <= 5;