`
bianxq
  • 浏览: 94198 次
  • 性别: Icon_minigender_1
  • 来自: 福州
社区版块
存档分类
最新评论

/*+ precompute_subquery */子查询中的提示

AccessSQLOracle 
阅读更多

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.

分享到：
| 关于绑定变量的一点心得
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    tif转rawmatlab代码-my_sidewindowfilter:my_sidewindowfilter

    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....

    Customizable Route Planning开源代码（CRP）

    - *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 ...

    ecdsa-wasm:ECDSAsecp256k1 + SHA-256

    ECSSA / secp256k1 + SHA-256 抽象的 这是的wasm版本 用于Node.js 节点test.js 如何使用 const ecdsa = require('ecdsa-wasm') // create secret key const sec = new ecdsa.SecretKey() ...// create precompute

    require-precompute:使创建`require`实现变得更容易

    概述 创建一个（合理的子集）Node.js 风格的require... 此外，该模块可能位于父模块的 node_modules 目录中，如果它们共享一个依赖项。 该模块预先计算了上述两种复杂情况，以使require实现更简单。 您可能已经有了构

    调用sklearn库的K-Means聚类分析实例

    #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=’...

    5.聚类模型1

    - `get_params([deep])`：返回模型的所有参数，如果 `deep=True`，则会包含子对象的参数。 - `set_params(**params)`：用于设置模型的参数，接受关键字参数 `params`，以便调整模型配置。 - `fit(X[, y, sample_...

    K-means python代码

    - `precompute_distances`: 是否预先计算邻近矩阵以提高效率。 ### 应用场景 K-means算法广泛应用于各种领域，如市场细分、图像分割、文本分类等。例如，在市场分析中，可以根据消费者的购买行为将客户分为不同的...

    聚类模型pdf

    - `precompute_distances`：是否预计算距离矩阵，默认为'auto'。当数据量较大时，预计算距离矩阵可能会提高效率。 - `verbose`：日志输出级别，默认为0，不输出任何日志信息。 - `random_state`：用于控制随机数生成...

    EEG_pre_processing：对多个数据集运行EEGLAB的几个预处理步骤

    在“EEG_pre_processing-master”这个项目中，用户可以预期看到MATLAB脚本和函数，它们可能包含了上述预处理步骤的实现。这些脚本可能通过EEGLAB的命令接口（如`.set`文件）来读取原始EEG数据，执行预处理，并保存...

    指纹增强代码matlab-whistler:用于记录和匹配人哨声与音频指纹数据库的桌面应用程序

    "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 ...

    matlab分时代码-audfprint:基于地标的音频指纹

    "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 ...

    指纹增强代码matlab-norepeat:去重

    "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 ...

    node2vec：node2vec算法的实现

    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

    Amplify Shader Editor 1.8.8.000.zip

    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

    计算几何：各种计算几何算法的实现

    并行化框架通过以下方法在DaCAlgorithm接口中形式化了分治算法的结构precompute perform input data precomputationdivide divide given problem into two subproblemsmerge merge solutions for a pair of ...

    react-native-text-size：在布置文本之前准确测量文本并从您的应用中获取字体信息

    React本机文本大小 在布置文本之前准确地测量文本并从您的应用程序（Android和iOS）获取字体信息。 主要有两个功能： ... 在这两个函数中，需要测量的文本都是必需的，但是其余参数是可选的，并且以与React Nati

Magicbox
Global site tag (gtag.js) - Google Analytics