Oracle SQL optimization must know-access to tables

Methods of accessing data

There are two types of data in the Access table: 1, direct access to table 2, first access to the index, and then back to the table

1, direct access to the table of two ways:

①, full table scan

Full-table scanning refers to when Oracle accesses the data of the target table, it starts scanning from the first block (block) of the first area occupied by the table, scanning to the high watermark of the table, all the databases in this range must be read, extent Of course, if you specify the filter in the where of the target SQL, then only the data that satisfies the condition is returned, (sometimes the efficiency of the full table scan is very high, but as the table data increases the resource consumption will increase gradually)

②, ROWID scan

ROWID scanning is when Oracle accesses the data in the target table and locates and accesses the data directly through the ROWID of the data. ROWID represents the physical storage address of the data row record in Oracle, which means that rowid is actually corresponding to the row record one by one in the data block in Oracle.

The ROWID scan in Oracle has two levels of meaning:

One is to directly access the corresponding data row records according to the value of the ROWID entered by the user in the SQL statement;

The other is to go to the relevant index first, and then access the corresponding data row records according to the ROWID obtained after accessing the index.

2. How to access the index

Commonly used is the B-tree index, the advantages are as follows:

①: All index leaf blocks are on the same layer, that is, they are the same depth from the index root node, which also means that accessing any index key value of the index leaf block takes almost the same amount of time.

②:oracle will ensure that all B-tree indexes are self-balanced, that is, it is not possible to have different indexes of leaf blocks that are not at the same level.

③: The efficiency of accessing the table row Records through the B-tree index does not decrease significantly with the data volume of the related table, that is, the time to access the data through the index is controllable and basically stable, which is the biggest difference between the index and the full table scan.

some common ways to access a B-Tree index:

①: Index uniqueness Scan (index unique scan):

An index uniqueness scan is a scan for a unique index that only applies to the target SQL in the Where condition that is the equivalent query. Because the scanned object is a unique index, the results of the index uniqueness scan will return at most one record.

②: Index range scan

The index range scan applies to all types of B-tree indexes, and when the scanned object is a uniqueness index, the where condition of the target SQL must be a range query at this point, and note that even for the same SQL under the same conditions, when the number of target indexes is greater than 1 o'clock, An index range scan consumes more logical reads than the logical reads that are consumed by index uniqueness scans.

③: Index Full scan

Index full scan applies to all types of B-tree indexes, including uniqueness indexes and non-uniqueness indexes. Refers to all index rows that are scanned for all leaf blocks of the target index.

By default, Oracle only needs to navigate to the first row of index rows at the leftmost leaf block of the index by accessing the necessary branch blocks when doing an index full scan.

Description: The result of the index full scan is also ordered, and is sorted by the index key Value column of the index, which means that the index full scan can be sorted

effect, while avoiding the real sort operation of the index key value Lieda.

④: Index fast full scan (index fast complete scan)

Index fast full scan and index full scan are very similar, and it is also due to all types of B-tree indexes (including uniqueness indexes and non-uniqueness indexes). As with the index full scan, the index fast full scan also needs to scan all the index rows of all the leaf blocks of the target index;

The difference between index fast full scan and index full scan is as follows:

①: Index fast full scan only for CBO

②: Index Fast full scan can use multiple blocks of read, or can be executed in parallel

③: Index fast full scan execution results are not necessarily sequential.

Example: The following target SQL with hint is a fast full scan of the index pk_emp_test for Oracle to go to the primary key index

(Emp_test is the table name Pk_emp_test is the primary key compound index of the table empno is the field of the query)

Select/*+ index_ffs (emp_test pk_emp_test) */empno from Emp_test;

⑤: Indexed skip Scan (index skip scan)

Index-hopping scanning applies all types of composite B-tree indexes, including unique and non-unique indexes, which specify query criteria for leading columns that do not have a target index in the Where condition

However, SQL that specifies the query criteria for a non-leading column of the index can still use the index.

Attention:

Index hopping scanning in Oracle is only available for selective and very good graphics with fewer distinct values for the leading column of the target index and subsequent non-leading columns, because the execution of an index hop scan

Efficiency must be decremented as the number of distinct values in the leading column of the target index is incremented.

Erase data Dictionary Slow:--production library disabled

Sql> alter system flush Shared_pool;

Empty buffer cache:---Production library disabled

Sql> alter system flush Buffer_cache;

"Table Connection"

1. Table Connection Sequence

Regardless of the number of tables in the target SQL table connection, Oracle can actually execute the SQL only 22 table connection, and then perform such a 22 table connection process, until all the tables in the target SQL are connected;

2, table connection method:

Two table join methods are: Sort merge connection, nested loop connection, hash connection, Cartesian product connection four kinds;

3. Type of table connection:

3.1 Internal connection:

An inner join refers to the connection result of a table connection that contains only those records that fully satisfy the join condition. For target SQL that contains table joins, as long as the Where condition is not written in those definitions in standard SQL or in Oracle for custom representations of outer joins (such as the left OUTER join in standard SQL, right outer join, and full outer Join, or the keyword "+" that is customized in Oracle to represent an outer join, the connection type of the SQL is an inner join.

such as: Select T1.col1,t1.col2,t2.col3 from T1,t2 where t1.col2=t2.col2; This SQL does not have those keywords, this is the inner connection

The INNER join notation in standard SQL is: Join on or join using (natural Join special connection method)

Join on Syntax: Target table 1 join target table 2 on (join condition)

such as: Select T1.col1,t1.col2,t2.col3 from T1 join T2 on (t1.col2=t2.col2);

Join using Syntax: Target table 1 join target table 2 using (Join column collection)

such as: Select T1.col1,col2,t2.col3 from t1 join T2 using (col2);

NOTE: For standard SQL that uses join using, if the connection column also appears in the Query column, the connection column cannot be accompanied by a table name or alias for the table name, or Oracle will error (ORA-25154)

Natural JOIN Syntax: Target table 1 natural join target table 2

such as: Select T1.col1,col2,t2.col3 from T1 natural join T2;

Attention:

In the case of an inner JOIN, an additional constraint other than the table join condition in the SQL text of the target SQL does not affect the actual execution result of the SQL;

3.2. External connection (outer join)

An outer join is an extension of an internal connection that refers to the connection result of a table connection in addition to the records that fully satisfy the join condition, all records in the driver table that do not meet the join condition are included.

The outer joins are divided into: Left join (left OUTER join) right-connected (starboard outer join) and full-join (fully outer join)

Left connection syntax: (the left side of the keyword is the driver table, that is, the target table 1 is the driver table)

The results of the left connection query include all records in the target table 1 and target table 2 that satisfy the join condition, and all records in the driver table that do not satisfy the join condition, and the query columns in the driver table for all records in the driver table that do not satisfy the join condition are populated with null values.

Target table 1 LEFT OUTER join target table 2 on (join condition) or target table 1 LEFT OUTER join target table 2 using (Connection column collection)

Right connection syntax: (the right side of the keyword is the driver table, that is, the target table 2 is the driver table)

The results of a right-connected query include all records in the target table 1 and target table 2 that satisfy the join condition, all records in the driver table that do not meet the join condition, and the query columns in the driver table for all records in the driver table that do not meet the join condition are populated with null values.

Target table 1 right outer join target table 2 on (join condition) or target table 1 right outer join target table 2 using (Connection column collection)

Full-Connection syntax:

The fully connected query results include all records in target table 1 and target table 2 that satisfy the join condition, and all records in target table 1 and target table 2 that do not satisfy the join condition, and the query columns in the driver table for all records that do not satisfy the join condition are populated with null values.

Target table 1 full outer join target table 2 on (join condition) or target table 1 full outer join target table 2 using (Connection column collection)

Attention:

For outer joins, the placement of additional constraints outside the table join condition in the SQL text of the target SQL may indeed affect the actual execution result of the SQL.

Oracle Custom Keyword "(+)" to indicate an outer join:

When the keyword "(+)" appears after the join column of the table, the table name is populated with null values for the query columns that do not meet the join criteria and are located in the table.

At this point should be the keyword "(+)" opposite the table as the external connection of the driver table, the key here is to decide which table is the driver table;

4, the table connection method:

There are four table connection methods in the Oracle database: Sort merge connection, nested loop join, hash join, and Cartesian connection

4.1. Sorting merge connections (sort merge Join)

A sort merge connection is a table join method that uses a sort operation (sort) and a merge operation (merge) to get a concatenated result set when a table is joined;

The pros and cons of a sort merge connection and the scenarios that apply:

①: Typically, the execution of a sort merge connection is much less efficient than a hash connection, but the former is more widely used because hash joins are usually only used for equivalent join conditions.

A sort merge connection can also be used for other join conditions (such as:< <= > >=)

②: In general, sort merge connections are not suitable for OLTP-type systems because sorting is a very expensive operation for OLTP-type systems, and of course, if you can

Avoid sorting operations, you can use sort merge connections even for OLTP-type systems.

③: In strict sense, there is no concept of a driver table for a sort merge connection;

4.2 Nested loops Join (nested loops join)

A nested loop join is a table join method in which two tables rely on two nested loops (outer and inner loops, respectively) for table joins to get a concatenated result set.

Advantages and disadvantages of nested loops and the applicable scenarios:

①: If the driver table has fewer records for the drive result set, there is a unique index on the connection column of the drive table (or there is a selectivity on the connection column of the driver table)

Non-uniqueness index), the execution efficiency of using nested loops joins at this point is very high. However, if the driver table has a large number of records for the drive result set, even if the driver table is connected

There is an index on the column, and the execution efficiency of using nested loops joins is not high at this time.

②: A large table can be used as a driver table for nested loops, and it is critical to see if the predicate condition (if any) specified in the target SQL can reduce the amount of data driving the result set;

③: Nested loops join has one advantage that other connection methods do not have: Nested loops connect for quick response.

4.3. Hash connection (hash join)

A hash join is a table connection method in which two tables rely primarily on hashing to obtain a concatenated result set when making a connection;

Note: Hash connections are only available for CBO

In theory, hash joins are much more efficient than sort merge joins and nested loops, and of course, this is not always the case.

Advantages and disadvantages of hash joins and the applicable scenarios:

①: Hash joins do not necessarily sort, or in most cases do not need to be sorted

②: The connection column of a hash-connected driver table should be optionally as good as possible, as this selectivity affects the number of records in the corresponding hash bucket,

and the number of records in the hash bucket will directly affect the efficiency of finding matching records from the hash bucket.

③: Hash join is only available for CBO, and it can only be used for equivalent join conditions (even if the hash is an inverse connection, Oracle actually converts it to an equivalent equivalent connection)

④: Hash joins are well suited for table joins between small tables and large tables and the number of records connected to the result set, especially if the connection columns of the small table are very selective.

At this point the execution time of the hash connection can be approximated as the time spent by the full table scanning the large table;

⑤: When two tables are hashed, if the predicate condition specified in target SQL is applied (if any), the hash table corresponding to the result set with the lower amount of data

Can be fully accommodated in memory (the PGA's workspace), the hash connection at this time is executed very efficiently;

4.4. Cartesian connection (cross join)

The Cartesian connection, also known as the Cartesian product, is a table join method in which two tables do not have any connection conditions when making a table connection.

Note: The Cartesian connection is actually a special merge connection, where the merge join is similar to the sort merge join, except that the Cartesian connection does not need to be sorted and

There is no connection condition when performing a merge operation.

Anti-Connection: (anti join)

Anti-Join is a special type of connection, unlike internal and external connections, there is no relevant keyword in the Oracle database to specifically express the anti-connection in SQL text;

Note: When you expand as a subquery, Oracle often converts those subqueries whose external where condition is not exists no in or <>all to the corresponding anti-join;

Half Connection: (semi join)

A semi-join is a special type of connection, and as with anti-joins, there are no related keywords in the Oracle database that can be used specifically for semi-joins in SQL text;

Note: When used as a subquery, Oracle often converts those subqueries whose external where condition is exists in or =any to the corresponding semi-join;

4.5, Star joins (star join)

Star joins are typically used for data warehouse type applications, which are a single fact table and a connection between multiple dimension tables;

------------------------------reference to Oracle-based SQL optimization---------------------------------

This article is from the "stupid Child's DBA path" blog, please be sure to keep this source http://fengfeng688.blog.51cto.com/4896812/1953300

Oracle SQL optimization must know-access to tables