当前位置:  数据库>oracle

Oracle分区表的层次查询如何才能用到分区？

    来源: 互联网  发布时间：2017-06-14

    本文导语: 最近在调优Oracle分区表的层次查询时，发现用不到分区，做了一个实验，发现还是可以用的到的，只是写法上有些要求。 drop table test; create table test(  id  number primary key,  parent_id number,  name varchar2(20),  code varchar2(4))partition by ...

最近在调优Oracle分区表的层次查询时，发现用不到分区，做了一个实验，发现还是可以用的到的，只是写法上有些要求。

drop table test;

create table test
(
  id  number primary key,
  parent_id number,
  name varchar2(20),
  code varchar2(4)
)
partition by list(code)
(
  partition p1 values('0301'),
  partition p2 values('0302'),
  partition p3 values('0303'),
  partition p4 values('0304'),
  partition p5 values('0305'),
  partition p6 values('0306'),
  partition p7 values('0307'),
  partition p8 values('0308'),
  partition p_default values (default)
);
insert into test values(1,0,'a1','0301');
insert into test values(2,1,'a2','0301');
insert into test values(3,2,'a3','0301');
insert into test values(4,3,'a4','0301');
insert into test values(5,0,'a5','0302');
insert into test values(6,5,'a6','0302');
insert into test values(7,6,'a7','0302');
insert into test values(8,7,'a8','0302');
insert into test values(9,8,'a9','0302');
insert into test values(10,0,'a10','0303');
insert into test values(11,0,'a11','0304');
insert into test values(12,0,'a12','0306');
insert into test values(13,0,'a13','0307');
insert into test values(14,0,'a14','0308');
insert into test values(15,10,'a15','0303');
insert into test values(16,11,'a16','0304');
insert into test values(17,12,'a17','0306');
insert into test values(18,13,'a18','0307');
insert into test values(19,14,'a19','0308');
commit;

exec dbms_stats.gather_table_stats(user,'test',cascade => true);

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


SQL> set autotrace traceonly
SQL> select * from test t
    start with t.id = 12
    connect by prior t.id = t.parent_id;
执行计划
----------------------------------------------------------
Plan hash value: 6144290
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                        |      |    19 |  798 |    16  (7)| 00:00:01 |      |      |
|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|      |      |      |            |          |      |      |
|  2 |  PARTITION LIST ALL                    |      |    19 |  285 |    15  (0)| 00:00:01 |    1 |    9 |
|  3 |    TABLE ACCESS FULL                    | TEST |    19 |  285 |    15  (0)| 00:00:01 |    1 |    9 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  1 - access("T"."PARENT_ID"=PRIOR "T"."ID")
      filter("T"."ID"=12)
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        55  consistent gets
          0  physical reads
          0  redo size
        557  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> select * from test t
    start with t.id = 12
            and t.code = '0306'
    connect by prior t.id = t.parent_id;
执行计划
----------------------------------------------------------
Plan hash value: 6144290
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                        |      |    19 |  798 |    16  (7)| 00:00:01 |      |      |
|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|      |      |      |            |          |      |      |
|  2 |  PARTITION LIST ALL                    |      |    19 |  285 |    15  (0)| 00:00:01 |    1 |    9 |
|  3 |    TABLE ACCESS FULL                    | TEST |    19 |  285 |    15  (0)| 00:00:01 |    1 |    9 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  1 - access("T"."PARENT_ID"=PRIOR "T"."ID")
      filter("T"."ID"=12 AND "T"."CODE"='0306')
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        55  consistent gets
          0  physical reads
          0  redo size
        557  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> select * from test t
    start with (t.id = 12
            and t.code = '0306')
    connect by prior t.id = t.parent_id
          and prior t.code = '0306';
执行计划
----------------------------------------------------------
Plan hash value: 6144290
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                        |      |    19 |  798 |    16  (7)| 00:00:01 |      |      |
|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|      |      |      |            |          |      |      |
|  2 |  PARTITION LIST ALL                    |      |    19 |  285 |    15  (0)| 00:00:01 |    1 |    9 |
|  3 |    TABLE ACCESS FULL                    | TEST |    19 |  285 |    15  (0)| 00:00:01 |    1 |    9 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  1 - access("T"."PARENT_ID"=PRIOR "T"."ID" AND PRIOR "T"."CODE"='0306')
      filter("T"."ID"=12 AND "T"."CODE"='0306')
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        55  consistent gets
          0  physical reads
          0  redo size
        557  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> select * from test t
    start with t.id = 12
    connect by prior t.id = t.parent_id
          and prior t.code = '0306';
执行计划
----------------------------------------------------------
Plan hash value: 6144290
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                        |      |    19 |  798 |    16  (7)| 00:00:01 |      |      |
|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|      |      |      |            |          |      |      |
|  2 |  PARTITION LIST ALL                    |      |    19 |  285 |    15  (0)| 00:00:01 |    1 |    9 |
|  3 |    TABLE ACCESS FULL                    | TEST |    19 |  285 |    15  (0)| 00:00:01 |    1 |    9 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  1 - access("T"."PARENT_ID"=PRIOR "T"."ID" AND PRIOR "T"."CODE"='0306')
      filter("T"."ID"=12)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        55  consistent gets
          0  physical reads
          0  redo size
        557  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          2  rows processed
只有下面的写法才能用到分区，可以看到t.code = '0306'是关键
SQL> select * from test t
    start with (t.id = 12
            and t.code = '0306')
    connect by prior t.id = t.parent_id
            and t.code = '0306';
执行计划
----------------------------------------------------------
Plan hash value: 3571852076
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |              |    2 |    84 |    9  (34)| 00:00:01 |    |          |
|*  1 |  CONNECT BY WITH FILTERING          |              |      |      |            |          |    |          |
|*  2 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TEST        |    1 |    15 |    1  (0)| 00:00:01 |  6 |        6 |
|*  3 |    INDEX UNIQUE SCAN                | SYS_C0010758 |    1 |      |    0  (0)| 00:00:01 |    |          |
|*  4 |  HASH JOIN                        |              |    1 |    28 |    6  (17)| 00:00:01 |    |          |
|  5 |    CONNECT BY PUMP                  |              |      |      |            |          |    |          |
|  6 |    PARTITION LIST SINGLE            |              |    2 |    30 |    4  (0)| 00:00:01 |  KEY |  KEY |
|  7 |    TABLE ACCESS FULL              | TEST        |    2 |    30 |    4  (0)| 00:00:01 |  6 |        6 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  1 - access("T"."PARENT_ID"=PRIOR "T"."ID")
      filter("T"."CODE"='0306')
  2 - filter("T"."CODE"='0306')
  3 - access("T"."ID"=12)
  4 - access("connect$_by$_pump$_002"."prior t.id "="T"."PARENT_ID")
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        16  consistent gets
          0  physical reads
          0  redo size
        557  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> select t.*, prior id, prior parent_id, prior t.name, prior t.code
      from test t
    start with t.id = 12
    connect by prior t.id = t.parent_id
          and  t.code = '0306';
执行计划
----------------------------------------------------------
Plan hash value: 3043676987
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |              |    2 |    84 |    9  (34)| 00:00:01 |    |          |
|*  1 |  CONNECT BY WITH FILTERING          |              |      |      |            |          |    |          |
|  2 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TEST        |    1 |    15 |    1  (0)| 00:00:01 | ROWID | ROWID |
|*  3 |    INDEX UNIQUE SCAN                | SYS_C0010758 |    1 |      |    0  (0)| 00:00:01 |    |          |
|*  4 |  HASH JOIN                        |              |    1 |    28 |    6  (17)| 00:00:01 |    |          |
|  5 |    CONNECT BY PUMP                  |              |      |      |            |          |    |          |
|  6 |    PARTITION LIST SINGLE            |              |    2 |    30 |    4  (0)| 00:00:01 |  KEY |  KEY |
|  7 |    TABLE ACCESS FULL              | TEST        |    2 |    30 |    4  (0)| 00:00:01 |  6 |        6 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  1 - access("T"."PARENT_ID"=PRIOR "T"."ID")
      filter("T"."CODE"='0306')
  3 - access("T"."ID"=12)
  4 - access("connect$_by$_pump$_002"."prior t.id "="T"."PARENT_ID")

：


    
 
 
 
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。












  • 相关文章推荐
  • 入侵Oracle数据库能用到的技术
  • Oracle 12c发布简单介绍及官方下载地址
  • 在linux下安装oracle,如何设置让oracle自动启动！也就是让oracle那个服务自动启动，不是手动的
  • oracle 11g最新版官方下载地址
  • 请问su oracle 和su - oracle有什么不同？
  • Oracle 数据库(oracle Database)Select 多表关联查询方式
  • 虚拟机装Oracle R12与Oracle10g
  • Oracle数据库(Oracle Database)体系结构及基本组成介绍
  • Oracle 数据库开发工具 Oracle SQL Developer
  • 如何设置让Oracle SQL Developer显示的时间包含时分秒
  • Oracle EBS R12 支持 Oracle Database 11g
  • Oracle 10g和Oracle 11g网格技术介绍
  • SCO unix下安装oracle，但没有光盘，请大家推荐一个oracle下载站点（unix版本的）。谢谢！！！！
  • oracle中如何把表中具有相同值列的多行数据合并成一行
  • 请问大家用oracle数据库, 用import oracle.*;下的东西么? 还是用标准库?
  • Oracle 数据库(oracle Database)性能调优技术详解
  • Linux /$ORACLE_HOME $ORACLE_HOME
  • ORACLE日期相关操作
  • Linux系统下Oracle的启动与Oracle监听的启动
  • ORACLE数据库常用字段数据类型介绍
  • 请问在solaris下安装ORACLE，用root用户和用oracle用户安装有什么区别么？
  • Oracle 12c的九大最新技术特性介绍
  • 网间Oracle的连接，远程连接Oracle服务器？？


    • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除！

    ©2012-2021,