- 浏览: 21505182 次
- 性别:
- 来自: 杭州
最新评论
-
ZY199266:
配置文件还需要额外的配置ma
Android 客户端通过内置API(HttpClient) 访问 服务器(用Spring MVC 架构) 返回的json数据全过程 -
ZY199266:
我的一访问为什么是 /mavenwebdemo/WEB-I ...
Android 客户端通过内置API(HttpClient) 访问 服务器(用Spring MVC 架构) 返回的json数据全过程 -
lvgaga:
我又一个问题就是 如果像你的这种形式写。配置文件还需要额外的 ...
Android 客户端通过内置API(HttpClient) 访问 服务器(用Spring MVC 架构) 返回的json数据全过程 -
lvgaga:
我的一访问为什么是 /mavenwebdemo/WEB-I ...
Android 客户端通过内置API(HttpClient) 访问 服务器(用Spring MVC 架构) 返回的json数据全过程 -
y1210251848:
你的那个错误应该是项目所使用的目标框架不支持吧
log4net配置(web中使用log4net,把web.config放在单独的文件中)
Oracle 表连接方法
How the Query Optimizer Executes Join Statements
To choose an execution plan for a join statement, the optimizer must make these interrelated decisions:
-
Access Paths
As for simple statements, the optimizer must choose an access path to retrieve data from each table in the join statement.
-
Join Method
To join each pair of row sources, Oracle Database must perform a join operation. Join methods include nested loop, sort merge, cartesian, and hash joins.
-
Join Order
To execute a statement that joins more than two tables, Oracle Database joins two of the tables and then joins the resulting row source to the next table. This process continues until all tables are joined into the result.
See Also:
"Overview of Optimizer Access Paths"
11.3.2 How the Query Optimizer Chooses Execution Plans for Joins
The query optimizer considers the following when choosing an execution plan:
-
The optimizer first determines whether joining two or more tables definitely results in a row source containing at most one row. The optimizer recognizes such situations based on
UNIQUE
andPRIMARY
KEY
constraints on the tables. If such a situation exists, then the optimizer places these tables first in the join order. The optimizer then optimizes the join of the remaining set of tables. -
For join statements with outer join conditions, the table with the outer join operator must come after the other table in the condition in the join order. The optimizer does not consider join orders that violate this rule. Similarly, when a subquery has been converted into an antijoin or semijoin, the tables from the subquery must come after those tables in the outer query block to which they were connected or correlated. However, hash antijoins and semijoins are able to override this ordering condition in certain circumstances.
With the query optimizer, the optimizer generates a set of execution plans, according to possible join orders, join methods, and available access paths. The optimizer then estimates the cost of each plan and chooses the one with the lowest cost. The optimizer estimates costs in the following ways:
-
The cost of a nested loops operation is based on the cost of reading each selected row of the outer table and each of its matching rows of the inner table into memory. The optimizer estimates these costs using the statistics in the data dictionary.
-
The cost of a sort merge join is based largely on the cost of reading all the sources into memory and sorting them.
-
The cost of a hash join is based largely on the cost of building a hash table on one of the input sides to the join and using the rows from the other of the join to probe it.
The optimizer also considers other factors when determining the cost of each operation. For example:
-
A smaller sort area size is likely to increase the cost for a sort merge join because sorting takes more CPU time and I/O in a smaller sort area. See "PGA Memory Management" to learn how to size SQL work areas.
-
A larger multiblock read count is likely to decrease the cost for a sort merge join in relation to a nested loop join. If the database can read a large number of sequential blocks from disk in a single I/O, then an index on the inner table for the nested loop join is less likely to improve performance over a full table scan. The multiblock read count is specified by the initialization parameter
DB_FILE_MULTIBLOCK_READ_COUNT
.
You can use the ORDERED
hint to override the optimizer's choice of join orders. If the ORDERED
hint specifies a join order that violates the rule for an outer join, then the optimizer ignores the hint and chooses the order. Also, you can override the optimizer's choice of join method with hints.
See Also:
Chapter 19, "Using Optimizer Hints" for more information about optimizer hints11.3.3 Nested Loop Joins
Nested loop joins are useful when the following conditions are true:
-
The database joins small subsets of data.
-
The join condition is an efficient method of accessing the second table.
It is important to ensure that the inner table is driven from (dependent on) the outer table. If the inner table's access path is independent of the outer table, then the same rows are retrieved for every iteration of the outer loop, degrading performance considerably. In such cases, hash joins joining the two independent row sources perform better.
A nested loop join involves the following steps:
-
The optimizer determines the driving table and designates it as the outer table.
-
The other table is designated as the inner table.
-
For every row in the outer table, Oracle Database accesses all the rows in the inner table. The outer loop is for every row in the outer table and the inner loop is for every row in the inner table. The outer loop appears before the inner loop in the execution plan, as follows:
NESTED LOOPS outer_loop inner_loop
11.3.3.1 Original and New Implementation for Nested Loop Joins
Oracle Database 11g introduces a new implementation for nested loop joins. As a result, execution plans that include nested loops might appear different than they did in previous releases of Oracle Database. Both the new implementation and the original implementation for nested loop joins are possible in Oracle Database 11g. So, when analyzing execution plans, it is important to understand that the number of NESTED
LOOPS
join row sources might be different.
11.3.3.1.1 Original Implementation for Nested Loop Joins
Consider the following query:
SELECT e.first_name, e.last_name, e.salary, d.department_name FROM hr.employees e, hr.departments d WHERE d.department_name IN ('Marketing', 'Sales') AND e.department_id = d.department_id;
Before Oracle Database 11g, the execution plan for this query might appear similar to the following execution plan:
------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19 | 722 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 220 | 1 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 19 | 722 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL | DEPARTMENTS | 2 | 32 | 2 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("D"."DEPARTMENT_NAME"='Marketing' OR "D"."DEPARTMENT_NAME"='Sales') 4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
In this example, the outer side of the join consists of a scan of the hr.departments
table that returns the rows that match the condition department_name
IN
('Marketing', 'Sales')
. The inner loop retrieves the employees in the hr.employees
table that are associated with those departments.
11.3.3.1.2 New Implementation for Nested Loop Joins
Oracle Database 11g introduces a new implementation for nested loop joins to reduce overall latency for physical I/O. When an index or a table block is not in the buffer cache and is needed to process the join, a physical I/O is required. Oracle Database 11g can batch multiple physical I/O requests and process them using a vector I/O instead of processing them one at a time.
As part of the new implementation for nested loop joins, two NESTED
LOOPS
join row sources might appear in the execution plan where only one would have appeared in prior releases. In such cases, Oracle Database allocates one NESTED
LOOPS
join row source to join the values from the table on the outer side of the join with the index on the inner side. A second row source is allocated to join the result of the first join, which includes the rowids stored in the index, with the table on the inner side of the join.
Consider the query in "Original Implementation for Nested Loop Joins". In Oracle Database 11g, with the new implementation for nested loop joins, the execution plan for this query might appear similar to the following execution plan:
------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19 | 722 | 3 (0)| 00:00:01 | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 19 | 722 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL | DEPARTMENTS | 2 | 32 | 2 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 220 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("D"."DEPARTMENT_NAME"='Marketing' OR "D"."DEPARTMENT_NAME"='Sales') 4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
In this case, the rows from the hr.departments
table constitute the outer side of the first join. The inner side of the first join is the index emp_department_ix
. The results of the first join constitute the outer side of the second join, which has the hr.employees
table as its inner side.
There are cases where a second join row source is not allocated, and the execution plan looks the same as it did in prior releases. The following list describes such cases:
-
All of the columns needed from the inner side of the join are present in the index, and there is no table access required. In this case, Oracle Database allocates only one join row source.
-
The order of the rows returned might be different than it was in previous releases. Hence, when Oracle Database tries to preserve a specific ordering of the rows, for example to eliminate the need for an
ORDER
BY
sort, Oracle Database might use the original implementation for nested loop joins. -
The
OPTIMIZER_FEATURES_ENABLE
initialization parameter is set to a release before Oracle Database 11g. In this case, Oracle Database uses the original implementation for nested loop joins.
11.3.3.2 When the Optimizer Uses Nested Loop Joins
The optimizer uses nested loop joins when joining small number of rows, with a good driving condition between the two tables. You drive from the outer loop to the inner loop, so the order of tables in the execution plan is important.
The outer loop is the driving row source. It produces a set of rows for driving the join condition. The row source can be a table accessed using an index scan or a full table scan. Also, the rows can be produced from any other operation. For example, the output from a nested loop join can serve as a row source for another nested loop join.
The inner loop is iterated for every row returned from the outer loop, ideally by an index scan. If the access path for the inner loop is not dependent on the outer loop, then you can end up with a Cartesian product; for every iteration of the outer loop, the inner loop produces the same set of rows. Therefore, you should use other join methods when two independent row sources are joined together.
11.3.3.3 Nested Loop Join Hints
If the optimizer chooses to use some other join method, then you can use the USE_NL
(table1 table2
) hint, where table1
and table2
are the aliases of the tables being joined.
For some SQL examples, the data is small enough for the optimizer to prefer full table scans and use hash joins. This is the case for the SQL example shown in Example 11-8, "Hash Joins". However, you can add a USE_NL
to instruct the optimizer to change the join method to nested loop. For more information on the USE_NL
hint, see "Hints for Join Operations".
11.3.3.4 Nesting Nested Loops
The outer loop of a nested loop can be a nested loop itself. You can nest two or more outer loops to join as many tables as needed. Each loop is a data access method, as follows:
SELECT STATEMENT NESTED LOOP 3 NESTED LOOP 2 (OUTER LOOP 3.1) NESTED LOOP 1 (OUTER LOOP 2.1) OUTER LOOP 1.1 - #1 INNER LOOP 1.2 - #2 INNER LOOP 2.2 - #3 INNER LOOP 3.2 - #4
11.3.4 Hash Joins
The database uses hash joins to join large data sets. The optimizer uses the smaller of two tables or data sources to build a hash table on the join key in memory. It then scans the larger table, probing the hash table to find the joined rows.
This method is best when the smaller table fits in available memory. The cost is then limited to a single read pass over the data for the two tables.
11.3.4.1 When the Optimizer Uses Hash Joins
The optimizer uses a hash join to join two tables if they are joined using an equijoin and if either of the following conditions are true:
-
A large amount of data must be joined.
-
A large fraction of a small table must be joined.
In Example 11-8, the database uses the table orders
to build the hash table. The database scans the larger order_items
later.
SELECT o.customer_id, l.unit_price * l.quantity FROM orders o ,order_items l WHERE l.order_id = o.order_id; -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 665 | 13300 | 8 (25)| |* 1 | HASH JOIN | | 665 | 13300 | 8 (25)| | 2 | TABLE ACCESS FULL | ORDERS | 105 | 840 | 4 (25)| | 3 | TABLE ACCESS FULL | ORDER_ITEMS | 665 | 7980 | 4 (25)| -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("L"."ORDER_ID"="O"."ORDER_ID")
11.3.5 Sort Merge Joins
Sort merge joins can join rows from two independent sources. Hash joins generally perform better than sort merge joins. However, sort merge joins can perform better than hash joins if both of the following conditions exist:
-
The row sources are sorted already.
-
A sort operation does not have to be done.
However, if a sort merge join involves choosing a slower access method (an index scan as opposed to a full table scan), then the benefit of using a sort merge might be lost.
Sort merge joins are useful when the join condition between two tables is an inequality condition (but not a nonequality) like <, <=, >, or >=. Sort merge joins perform better than nested loop joins for large data sets. You cannot use hash joins unless there is an equality condition.
In a merge join, there is no concept of a driving table. The join consists of two steps:
-
Sort join operation: Both the inputs are sorted on the join key.
-
Merge join operation: The sorted lists are merged together.
If the input is sorted by the join column, then a sort join operation is not performed for that row source. However, a sort merge join always creates a positionable sort buffer for the right side of the join so that it can seek back to the last match in the case where duplicate join key values come out of the left side of the join.
11.3.5.1 When the Optimizer Uses Sort Merge Joins
The optimizer can choose a sort merge join over a hash join for joining large amounts of data if any of the following conditions are true:
-
The join condition between two tables is not an equijoin.
-
Because of sorts required by other operations, the optimizer finds it is cheaper to use a sort merge than a hash join.
http://www.itpub.net/viewthread.php?tid=207865
SQL> create table t1 as select * from dba_objects;
表已创建。
SQL> create table t2 as select * from dba_objects where rownum<501;
表已创建。
SQL> create index t1_ind on t1(object_id);
索引已创建。
SQL> select t1.* from t1,t2 where t1.object_id=t2.object_id and t2.object_name='t';
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 1634138066
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 286 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 286 | 6 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | T2 | 1 | 79 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T1_IND | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 207 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T2"."OBJECT_NAME"='t')
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
两表的数据量差别很大,并且在内部表上有索引时,CBO选择nested loops
SQL> drop index t1_ind;
索引已删除。
SQL> select t1.* from t1,t2 where t1.object_id=t2.object_id and t2.object_name='t';
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 2959412835
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 286 | 288 (2)| 00:00:04 |
|* 1 | HASH JOIN | | 1 | 286 | 288 (2)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| T2 | 1 | 79 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 73141 | 14M| 283 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T2"."OBJECT_NAME"='t')
Note
-----
- dynamic sampling used for this statement (level=2)
当t1上的索引删除时,CBO选择hash join,以较小表作为外部表(驱动表)
相关推荐
Oracle表连接方式,思路清晰,可以帮助初学者更好的掌握Oracle的表连接。
C#中连接oracle连接方法C#中连接oracle连接方法C#中连接oracle连接方法C#中连接oracle连接方法C#中连接oracle连接方法C#中连接oracle连接方法
这是因为ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询..数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性...
详解Oracle多种表连接方式,对内连接、外联结均有详细描述。
oracle多表连接技巧介绍了常见的多表连接应用实例
您可能感兴趣的文章:常用的Oracle doc命令(收藏)Oracle 多行记录合并/连接/聚合字符串的几种方法Oracle中字符串连接的实现方法php连接oracle数据库及查询数据的方法plsql连接oracle数据库报ora 12154错
Oracle 表的扫描方式及连接方法,全表扫描,索引扫描,索引范围扫描,索引唯一扫描
ORACLE 连接设置方法很多,这里写四种,但主要是远程连接方法。 第一种情况: 若oracle服务器装在本机上,那就不多说了,连接只是用户名和密码的问题了。不过要注意环境变量%ORACLE_HOME%/network/admin/是否...
C#与Oracle的连接方法,Oracle的增删改查
内连接 自然连接 左外连接 右外连接 笛卡尔连接 索引连接 嵌套连接
介绍SQL语句中的多表连接查询。主要讲解了表连接的形式和笛卡尔积,程序也重点讲解了表的左连接、右连接、自然连接等基本概念。
Oracle+表连接方式(内连接-外连接-自连接)+详解
本章主要讲解oracle客户端连接oracle效劳器,主要有以下内容: 1.配置本地net效劳名配置 2.用sqlplus连接; 3.用toad连接; 一、配置本地net效劳名 首先说明我们的效劳器的IP为60.220.246.53,oracle的SID为czdb。 ...
Oracle+表连接方式(内连接-外连接-自连接) 详细介绍的连接的类型及应用实例,一份值得看的数据库资料,强列建义下载
Oracle的三种表连接方式 详细讲述• sort merge join(SMJ) • nest loop(NL) • hash join(HJ)
数据仓库技术是目前已知的比较成熟和被广泛采用的解决方案,用于整和电信运营企业内部所有分散...本文着重分析ORACLE SQL优化中对于系统性能影响极大的表连接方式、特点、适用范围,并对如何使用和优化做了详细的探讨。
通过ORACLE通用连接访问SQLServer数据库的方法
Oracle最大连接数
强制释放Oracle数据连接方案,经常会遇到数据库无法连接,发现是连接数超过限制,如何能够看到默认的数据库连接数量限制呢,如何能够增大数量限制呢? .......
oracle表连接和子查询实例 所有的实例查询都包含在里面,不会担心笔试oracle子查询。