- 浏览: 91633 次
- 性别:
- 来自: 福州
最新评论
-
JonHans:
...
ORALCE /*+NO_EXPAND*/ 含义 -
sangli:
Alter table table parallel 4;
...
oracle parallel execution example -
Ivan.t:
不会这么巧吧?你是银钦?http://ivanstudy.bl ...
Oracle Raw,number,varchar2...转换 -
Christ:
那么,如何使用Hibernate存取RAW?显然 我无法使用u ...
Oracle Raw,number,varchar2...转换 -
bianxq:
执行计划没有变化,说明你的并行提示被忽略掉了。检查你的书写和系 ...
oracle parallel execution example
PUSH_SUBQ 可以用来控制子查询的执行 这个是PUSH_SUBQ 的本意
我那个例子的意思是说:
PUSH_SUBQ 本质上是个CBO的hints(当然RBO也提不上hints)
由于PUSH_SUBQ 的引入就是为了来解决unnesting的某些不足
所以在不同的版本上,这个hints发挥的作用也有所不同了.
8i上这个提示的作用更接近本原:
$ sqlplus "/ as sysdba"
SQL*Plus: Release 8.1.5.0.0 - Production on Sun Sep 12 20:51:21 2004
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
SQL> connect scott/tiger
Connected.
SQL> create table dept1 as select * from dept;
Table created.
SQL> set linesize 120
SQL> select a.*
2 from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 FILTER ---------------'最初push_subq的使命是为了消除/提高这个filter的效率的'
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'EMP'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
5 4 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
6 1 TABLE ACCESS (FULL) OF 'DEPT1'
Statistics
----------------------------------------------------------
0 recursive calls
52 db block gets
21 consistent gets
0 physical reads
0 redo size
2715 bytes sent via SQL*Net to client
751 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
用于push_subq是个CBO hints,这里我们可以看到COST的出现:
SQL> select /*+ push_subq */ a.*
2 from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=1 Bytes=122)
1 0 NESTED LOOPS (Cost=3 Card=1 Bytes=122)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=2 Bytes=44)
3 2 TABLE ACCESS (FULL) OF 'DEPT1' (Cost=1 Card=1 Bytes=9)--------'这里消除了之前的filter'
4 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=21 Bytes=2100)
Statistics
----------------------------------------------------------
204 recursive calls
85 db block gets
38 consistent gets
0 physical reads
0 redo size
2706 bytes sent via SQL*Net to client
768 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
14 rows processed
再看CBO下:
正常情况下,没什么好说的:
SQL> set autotrace traceonly
SQL> exec dbms_stats.gather_schema_stats('SCOTT')
PL/SQL procedure successfully completed.
SQL> select a.*
2 from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=5 Bytes=275)
1 0 FILTER
2 1 NESTED LOOPS (Cost=2 Card=5 Bytes=275)
3 2 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=1 Bytes=18)
4 2 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=518)
5 1 TABLE ACCESS (FULL) OF 'DEPT1' (Cost=1 Card=1 Bytes=9)
Statistics
----------------------------------------------------------
39 recursive calls
68 db block gets
21 consistent gets
0 physical reads
0 redo size
2708 bytes sent via SQL*Net to client
751 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
在CBO下push_subq发挥了同样的作用:
SQL> select /*+ push_subq */ a.*
2 from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=5 Bytes=275)
1 0 NESTED LOOPS (Cost=2 Card=5 Bytes=275)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=1 Bytes=18)
3 2 TABLE ACCESS (FULL) OF 'DEPT1' (Cost=1 Card=1 Bytes=9)---'注意这里'
4 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=518)
Statistics
----------------------------------------------------------
0 recursive calls
84 db block gets
11 consistent gets
0 physical reads
0 redo size
2709 bytes sent via SQL*Net to client
768 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
而在Oracle9i之中:
[oracle@jumper oracle]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.3.0 - Production on Sun Sep 12 21:42:57 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
SQL> connect scott/tiger
Connected.
SQL> set linesize 120
SQL> set autotrace traceonly
SQL> select a.*
2 from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);
11 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 FILTER
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'EMP'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
5 4 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
6 1 TABLE ACCESS (FULL) OF 'DEPT1'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
26 consistent gets
0 physical reads
0 redo size
1164 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
SQL> select /*+ push_subq */ a.*
2 from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);
11 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=8 Card=82 Bytes=9676)
1 0 HASH JOIN (SEMI) (Cost=8 Card=82 Bytes=9676)
2 1 HASH JOIN (Cost=5 Card=82 Bytes=8938)
3 2 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=7134)
4 2 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=1804)
5 1 TABLE ACCESS (FULL) OF 'DEPT1' (Cost=2 Card=82 Bytes=738)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1195 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
SQL> exec dbms_stats.gather_schema_stats('scott')
PL/SQL procedure successfully completed.
SQL> select a.*
2 from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);
11 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=8 Card=11 Bytes=682)
1 0 HASH JOIN (SEMI) (Cost=8 Card=11 Bytes=682)
2 1 HASH JOIN (Cost=5 Card=11 Bytes=572)
3 2 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=52)
4 2 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=11 Bytes=429)
5 1 TABLE ACCESS (FULL) OF 'DEPT1' (Cost=2 Card=4 Bytes=40)
Cou
发表评论
-
[译] PL/SQL 格式化指南 (PL/SQL Formatting Guide)
2009-11-30 17:03 1613最近一直在修改以前同事写的Oracle存储过程,由于编码不规范 ... -
Oracle Raw,number,varchar2...转换
2009-09-17 11:24 7701Oracle Raw,number,varchar2... ... -
Oracle's Query Transformer
2009-09-09 17:56 1050Oracle's Query Transformer O ... -
观察analyze table compute statistics 都对什么对象统计了信息
2009-09-08 12:52 10332观察analyze table compute statis ... -
sql 语句中or条件之种种情况
2009-09-08 10:55 1698sql 语句中or条件之种种情况 1、相同字段or条件 ... -
/*+ precompute_subquery */子查询中的提示
2009-09-08 10:51 1422QUOTE:------------------------- ... -
关于绑定变量的一点心得
2009-09-08 10:03 1217我们一直在告诉开发人员一定要使用绑定变量,而你是否真正了解 ... -
查询--驱动表
2009-08-24 10:58 1186查询中何为驱动表阿? ... -
SQL连接驱动表帖子
2009-08-24 10:08 2400http://www.itpub.net/v ... -
Oracle中巧用CTAS快速建立表格
2009-08-21 15:55 1323CTAS是通过查询,然后根 ... -
优化SQL语句的一些规则
2009-08-20 21:01 1397大家都在讨论关于数据 ... -
oracle中关于in和exists,not in 和 not exists、关联子查询、非关联子查询
2009-08-20 15:01 5215oracle中关于in和e ... -
Oracle的大表,小表与全表扫描
2009-08-20 11:11 1294通常对于小表,Oracle建议通过全表扫描进行数据访问,对于大 ... -
reverse函数与like % 的使用
2009-08-19 18:03 1177oracle 提供一个reverse函数,可以实现将一个对象反 ... -
Oracle语句优化规则汇总(二)
2009-08-19 15:14 6831. 用UNION替换OR (适用于 ... -
Oracle语句优化规则汇总(一)
2009-08-19 15:07 830Oracle sql 性能优化调整 1. 选用适合的OR ... -
几种索引扫描方式
2009-08-19 14:47 14731)索引唯一扫描 如果查询时是通过unique或primary ... -
oracle不使用索引原因定位
2009-08-19 11:36 1621较典型的问题有:有时,表明明建有索引,但查询过程显然没有 ... -
列定义是否为空对COUNT(*)操作索引选择的影响
2009-08-19 11:28 1185SQL> desc test; Name ... -
ORALCE /*+NO_EXPAND*/ 含义
2009-08-18 11:02 2929求教 ORALCE /*+NO_EXPAND*/ 含义 是什么 ...
相关推荐
opnet 点对点例子描述了源节点无限发送包到目的节点
#Subq3Text-基于日晒和苏打的SublimeText 2配置 在Subq3Text存储库中是我的SublimeText配置。 它为SublimeText使用了和。 ## Installation以下内容会将此存储库的文件克隆到~/Library/Application Support/Sublime...
数据来源:中国电力统计NJ-2021版
数据来源:中国电力统计NJ-2021版
词根单词 2.2.4 修改版.apk
毕业论文-基于JSP的个人通讯录管理系统设计与实现.docx
数据来源:中国电力统计NJ-2021版
Node.js,简称Node,是一个开源且跨平台的JavaScript运行时环境,它允许在浏览器外运行JavaScript代码。Node.js于2009年由Ryan Dahl创立,旨在创建高性能的Web服务器和网络应用程序。它基于Google Chrome的V8 JavaScript引擎,可以在Windows、Linux、Unix、Mac OS X等操作系统上运行。 Node.js的特点之一是事件驱动和非阻塞I/O模型,这使得它非常适合处理大量并发连接,从而在构建实时应用程序如在线游戏、聊天应用以及实时通讯服务时表现卓越。此外,Node.js使用了模块化的架构,通过npm(Node package manager,Node包管理器),社区成员可以共享和复用代码,极大地促进了Node.js生态系统的发展和扩张。 Node.js不仅用于服务器端开发。随着技术的发展,它也被用于构建工具链、开发桌面应用程序、物联网设备等。Node.js能够处理文件系统、操作数据库、处理网络请求等,因此,开发者可以用JavaScript编写全栈应用程序,这一点大大提高了开发效率和便捷性。 在实践中,许多大型企业和组织已经采用Node.js作为其Web应用程序的开发平台,如Netflix、PayPal和Walmart等。它们利用Node.js提高了应用性能,简化了开发流程,并且能更快地响应市场需求。
数据来源:中国电力统计NJ-2021版
Node.js,简称Node,是一个开源且跨平台的JavaScript运行时环境,它允许在浏览器外运行JavaScript代码。Node.js于2009年由Ryan Dahl创立,旨在创建高性能的Web服务器和网络应用程序。它基于Google Chrome的V8 JavaScript引擎,可以在Windows、Linux、Unix、Mac OS X等操作系统上运行。 Node.js的特点之一是事件驱动和非阻塞I/O模型,这使得它非常适合处理大量并发连接,从而在构建实时应用程序如在线游戏、聊天应用以及实时通讯服务时表现卓越。此外,Node.js使用了模块化的架构,通过npm(Node package manager,Node包管理器),社区成员可以共享和复用代码,极大地促进了Node.js生态系统的发展和扩张。 Node.js不仅用于服务器端开发。随着技术的发展,它也被用于构建工具链、开发桌面应用程序、物联网设备等。Node.js能够处理文件系统、操作数据库、处理网络请求等,因此,开发者可以用JavaScript编写全栈应用程序,这一点大大提高了开发效率和便捷性。 在实践中,许多大型企业和组织已经采用Node.js作为其Web应用程序的开发平台,如Netflix、PayPal和Walmart等。它们利用Node.js提高了应用性能,简化了开发流程,并且能更快地响应市场需求。
数据来源:中国人口与就业统计NJ-2023版
图书借阅管理系统设计与实现及论大学生写作能力.docx
Smart继电器编程器操作手册
毕业设计论文-基于C的人事管理系统设计与实现.docx
数据来源:中国劳动统计NJ-2023版
OPCEV096IPHT手册
数据来源:中国电力统计NJ-2021版
数据来源:中国人口与就业统计NJ-2023版
数据来源:中国人口与就业统计NJ-2023版
基于matlab实现应用遗传算法针对物流配送车辆路径规划问题进行求解.rar