- 浏览: 91635 次
- 性别:
- 来自: 福州
-
最新评论
-
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
QUOTE:
--------------------------------------------------------------------------------
select * from tab1 where tab1.object_name in(select /*+ precompute_subquery */ object_name from tab2 where owner='SYS');
/*+ precompute_subquery */子查询中的提示
就是把子查询中in的多个值改写成多个OR条件
--------------------------------------------------------------------------------
SCOTT@ncdb>select count(*) from dba_objects;
COUNT(*)
----------
62493
Elapsed: 00:00:00.84
SCOTT@ncdb>create table dba_objects_20090210 as select * from dba_objects;
Table created.
Elapsed: 00:00:01.56
SCOTT@ncdb>create table dba_objects2 as select * from dba_objects;
Table created.
Elapsed: 00:00:01.48
SCOTT@ncdb>select owner, count(*) from dba_objects2 group by owner;
OWNER COUNT(*)
---------- ----------
PUBLIC 20084
SYSTEM 454
XDB 680
OLAPSYS 720
IUFOV31 366
YONGYOU 1573
SYS 23257
TSMSYS 3
MDSYS 936
SHUIBO 1562
JV 50
NCTOCC 7
SYSMAN 1346
HAIFENG 1561
EXFSYS 282
SI_INFORMT 8
N_SCHEMA
BI_DATA 18
NCV31 4798
WMSYS 315
ORDSYS 1721
SCOTT 17
NCTOPSI 220
NFD 191
ORACLE_OCM 8
BI_REP 111
CTXSYS 313
ORDPLUGINS 10
OUTLN 9
DBSNMP 46
DMSYS 189
OULONG 1503
RMAN 128
OA 8
33 rows selected.
Elapsed: 00:00:00.82
SCOTT@ncdb>create index idx_dba_objects_name on dba_objects_20090210(object_name);
Index created.
Elapsed: 00:00:00.43
SCOTT@ncdb>explain plan for
2 select * from dba_objects_20090210 where object_name in
3 (select object_name from dba_objects2 where owner='NCTOCC');
Explained.
Elapsed: 00:00:00.06
SCOTT@ncdb>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Plan hash value: 2716592996
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 94 | 24440 | 114 (6)| 00:00:01 |
| 1 | NESTED LOOPS | | 94 | 24440 | 114 (6)| 00:00:01 |
| 2 | SORT UNIQUE | | 10 | 830 | 103 (5)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | DBA_OBJECTS2 | 10 | 830 | 103 (5)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DBA_OBJECTS_20090210 | 10 | 1770 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_DBA_OBJECTS_NAME | 10 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("OWNER"='NCTOCC')
5 - access("OBJECT_NAME"="OBJECT_NAME")
Note
-----
- dynamic sampling used for this statement
22 rows selected.
Elapsed: 00:00:00.06
SCOTT@ncdb>explain plan for
2 select * from dba_objects_20090210 where object_name in
3 (select object_name from dba_objects2 where owner='SYS');
Explained.
Elapsed: 00:00:00.06
SCOTT@ncdb>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Plan hash value: 3391626064
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68173 | 16M| | 560 (5)| 00:00:04 |
|* 1 | HASH JOIN RIGHT SEMI| | 68173 | 16M| 2352K| 560 (5)| 00:00:04 |
|* 2 | TABLE ACCESS FULL | DBA_OBJECTS2 | 25340 | 2053K| | 104 (6)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DBA_OBJECTS_20090210 | 77278 | 13M| | 108 (10)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"="OBJECT_NAME")
2 - filter("OWNER"='SYS')
Note
-----
- dynamic sampling used for this statement
20 rows selected.
Elapsed: 00:00:00.04
SCOTT@ncdb>explain plan for
2 select * from dba_objects_20090210 where object_name in
3 (select /*+ precompute_subquery */object_name from dba_objects2 where owner='SYS');
Explained.
Elapsed: 00:00:00.34
SCOTT@ncdb>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Plan hash value: 3391626064
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68173 | 16M| | 560 (5)| 00:00:04 |
|* 1 | HASH JOIN RIGHT SEMI| | 68173 | 16M| 2352K| 560 (5)| 00:00:04 |
|* 2 | TABLE ACCESS FULL | DBA_OBJECTS2 | 25340 | 2053K| | 104 (6)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DBA_OBJECTS_20090210 | 77278 | 13M| | 108 (10)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"="OBJECT_NAME")
2 - filter("OWNER"='SYS')
Note
-----
- dynamic sampling used for this statement
20 rows selected.
Elapsed: 00:00:00.04
SCOTT@ncdb>explain plan for
2 select /*+ precompute_subquery */ * from dba_objects_20090210 where object_name in
3 (select object_name from dba_objects2 where owner='SYS');
Explained.
Elapsed: 00:00:00.04
SCOTT@ncdb>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Plan hash value: 3391626064
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68173 | 16M| | 560 (5)| 00:00:04 |
|* 1 | HASH JOIN RIGHT SEMI| | 68173 | 16M| 2352K| 560 (5)| 00:00:04 |
|* 2 | TABLE ACCESS FULL | DBA_OBJECTS2 | 25340 | 2053K| | 104 (6)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DBA_OBJECTS_20090210 | 77278 | 13M| | 108 (10)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"="OBJECT_NAME")
2 - filter("OWNER"='SYS')
Note
-----
- dynamic sampling used for this statement
20 rows selected.
Elapsed: 00:00:00.04
SCOTT@ncdb>
=======================================================================================================
SQL> create table test_1 as select * from dba_objects;
表已创建。
SQL> create table test2 as select rownum id from dual connect by rownum<10;
表已创建。
SQL> create index i_test_1 on test_1(object_id);
索引已创建。
SQL> set autot trace exp
SQL> select * from test_1 where object_id in (select /*+ precompute_subquery */ id from test2);
执行计划
----------------------------------------------------------
Plan hash value: 3907423376
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 1416 | 12 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST_1 | 8 | 1416 | 12 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_TEST_1 | 207 | | 9 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"=1 OR "OBJECT_ID"=2 OR "OBJECT_ID"=3 OR "OBJECT_ID"=4
OR "OBJECT_ID"=5 OR "OBJECT_ID"=6 OR "OBJECT_ID"=7 OR "OBJECT_ID"=
8 OR
"OBJECT_ID"=9)
Note
-----
- dynamic sampling used for this statement
==================================================================================================
SCOTT@ncdb>explain plan for
2 select * from dba_objects_20090210 where object_name in
3 (select /*+ precompute_subquery */ object_name from dba_objects2 where owner='NCTOCC');
Explained.
Elapsed: 00:00:00.18
SCOTT@ncdb>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2232840152
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 69 | 12213 | 59 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DBA_OBJECTS_20090210 | 69 | 12213 | 59 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_DBA_OBJECTS_NAME | 309 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_NAME"='V_COINFO' OR "OBJECT_NAME"='V_CUSTINFO' OR
"OBJECT_NAME"='V_RETURNBILL_STATUS' OR "OBJECT_NAME"='V_SALEORDER_STATUS' OR
"OBJECT_NAME"='V_SALEOUT_STATUS' OR "OBJECT_NAME"='V_SALERET_STATUS' OR
"OBJECT_NAME"='V_USERINFO')
Note
-----
- dynamic sampling used for this statement
22 rows selected.
发表评论
-
[译] 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条件 ... -
关于绑定变量的一点心得
2009-09-08 10:03 1217我们一直在告诉开发人员一定要使用绑定变量，而你是否真正了解 ... -
push_subq提示
2009-09-06 12:17 1258PUSH_SUBQ 可以用来控制子查询的执行 这个是PUSH_ ... -
查询--驱动表
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*/ 含义 是什么 ...
相关推荐
precompute_size.py --min 512 python precompute_size.py --min 1024 python precompute_size.py --random python resize_image.py --file_name 512 python resize_image.py --file_name 1024 python resize_image....
- *customization*: Used to precompute the metric weights for the overlay graph. Call it with `./deploy/customization path_to_graph path_to_overlay_graph metric_output_directory metric_type`. We ...
ECSSA / secp256k1 + SHA-256 抽象的 这是的wasm版本 用于Node.js 节点test.js 如何使用 const ecdsa = require('ecdsa-wasm') // create secret key const sec = new ecdsa.SecretKey() ...// create precompute
概述 创建一个（合理的子集）Node.js 风格的require... 此外，该模块可能位于父模块的 node_modules 目录中，如果它们共享一个依赖项。 该模块预先计算了上述两种复杂情况，以使require实现更简单。 您可能已经有了构
#class sklearn.cluster.KMeans(n_clusters=8, init=’k-means++’, n_init=10, max_iter=300, tol=0.0001, precompute_distances=’auto’, verbose=0, random_state=None, copy_x=True, n_jobs=1, algorithm=’...
"precompute" writes a *.fpt file under precompdir with precomputed fingerprint for each input wav file. "merge" combines previously-created databases into an existing database; "newmerge" combines ...
"precompute" writes a *.fpt file under precompdir with precomputed fingerprint for each input wav file. "merge" combines previously-created databases into an existing database; "newmerge" combines ...
"precompute" writes a *.fpt file under precompdir with precomputed fingerprint for each input wav file. "merge" combines previously-created databases into an existing database; "newmerge" combines ...
Built-in Renderer, HD, URP, and Lightweight SRP Support IMPORTANT! Break To Components ... Bicubic Precompute Sample NEW! Procedural Sample NEW! Plane Clip NEW! HDRP Vector Displacement Sample NEW! Tes
Node2Vec node2vec算法Aditya Grover，Jure Leskovec和Vid Kocijan的Python3实现。... fast_gnp_random_graph ( n = 100 , p = 0.5 )# Precompute probabilities and generate walks - **ON WINDOWS ONLY WORKS
并行化框架通过以下方法在DaCAlgorithm接口中形式化了分治算法的结构precompute perform input data precomputationdivide divide given problem into two subproblemsmerge merge solutions for a pair of ...
React本机文本大小 在布置文本之前准确地测量文本并从您的应用程序（Android和iOS）获取字体信息。 主要有两个功能： ... 在这两个函数中，需要测量的文本都是必需的，但是其余参数是可选的，并且以与React Nati