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

╰半橙微兮° 2022-08-06 15:03 150阅读 0赞

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

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