思享工具箱

no_merge hint

摘要：
这在10gR2.SQL˃从v$version；横幅----------------------------------------------OracleDatabase10gEnterpriseEditionRelease10.2.0.5.0-64biPL/SQLRelease10.2.0.5.0-ProductionCORE10.2.0。

This is tested in 10gR2.

SQL> select * from v$version;

BANNER
------------------------------------------------
Oracle Database 10g Enterprise Edition Release 1
0.2.0.5.0 - 64bi

PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Solaris: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

　　

First, we create a view.

SQL> create view v_dept as select deptno,dname from dept;

View created.

　　

Then check SQL

SQL> explain plan for select e.ename,d.dname from emp2 e, v_dept d where e.deptno=d.deptno;

Explained.

SQL> set linesize 180
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 827386235

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   308 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   308 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   126 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP2    |    14 |   126 |     3   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("E"."DEPTNO"="DEPTNO")
       filter("E"."DEPTNO"="DEPTNO")

18 rows selected.

　　

use no_merge

SQL>  explain plan for select /*+ no_merge(d) */ e.ename,d.dname from emp2 e, v_dept d where e.deptno=d.deptno;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2242990105

------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |    14 |   434 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN          |        |    14 |   434 |     7  (15)| 00:00:01 |
|   2 |   VIEW              | V_DEPT |     4 |    88 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT   |     4 |    52 |     3   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL | EMP2   |    14 |   126 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E"."DEPTNO"="D"."DEPTNO")

16 rows selected.

SQL>

　　

标签：#select#table | 浏览：109 | 发布日期：

免责声明：文章转载自《no_merge hint》仅用于学习参考。如对内容有疑问，请及时联系本站处理。

上篇转：手把手教你安装MBAM 2.5 SP12D情况下，复数的意义代表旋转下篇

宿迁高防，2C2G15M，22元/月；香港BGP，2C5G5M，25元/月 雨云优惠码：MjYwNzM=

相关文章

postgres 行列转换

行转列 imos=# create table test_split_to_table(id int, name_list varchar); CREATE TABLE imos=# insert into test_split_to_table values(1,'a;b;c'),(2,'d;e;f'); INSERT 0 2 imos=# imos=#...

SQL跨数据库复制表数据

不同服务器数据库之间的数据操作 不同数据库之间复制表的数据的方法： 当表目标表存在时： insert into 目的数据库..表 select * from 源数据库..表 当目标表不存在时： select * into 目的数据库..表 from 源数据库..表 --如果在不同的SQL之间: insert into openrows...

oralce 菜鸟总结

1、Oracle 中默认的日期格式：DD-Mon-RR 2、SELECT  (SYSDATE-to_date('2010-01-01','yyyy-mm-dd'))/7 AS WEEKS from dual; 3、如果只是计算两个日期的月份的话为double行结果： select months_between(sysdate,to_date('2010-0...

SQL Server连接Oracle详细步骤

http://blog.csdn.net/weiwenhp/article/details/8093105 我们知道SQL Server和Oracle其实很多原理都类似.特别是一些常用的SQL语句都是按照标准来.所以它们也可以有一定的互操作性的.这里讲一下,怎么配置让SQL Server连接一个Oracle.然后你在SQL Server中也能查看Oracl...

mysql 成绩排序

编写一个 SQL 查询来实现分数排名。 如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。 +----+-------+| Id | Score |+----+-------+| 1 | 3.50 || 2 | 3.65 || 3 | 4.00 || 4 | 3.85...

索引长度过长 ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

1.发现问题  今天在修改innodb表的某个列的长度时，报如下错误：   [html]view plaincopy  print? alter table test2 modify column id varchar(500);   ERROR 1071 (42000): Specified key was too long; max key ...

最新文章

随机推荐

思享工具箱导航