oracle 10g sql execution plan之分区表pstart及pstop含义小测

最新推荐文章于 2024-04-22 21:49:33 发布
最新推荐文章于 2024-04-22 21:49:33 发布
阅读量786 收藏
点赞数
文章标签： 数据库 后端
前言：
    优化sql时，查看执行计划即可快速定位其性能问题，理解与执行计划相关一些概念及知识点则
至关重要。近期优化某客户数据库中1条sql,发现如下一点问题，于是有此文以为备记，供诸君参考
insert
into
  CEP_ORDER_FINISH
  (
    EXT_CUST_ORDER_ID,
    SYSTEM_ID
  )
select distinct
  a.EXT_CUST_ORDER_ID,
  :"SYS_B_0"
from
  JK.SOO_ORDER_MAP a,
  CRMDB.L_ORDER_ITEM B
where
  a.CUST_ORDER_ID = B.CUST_ORDER_ID
and exists
  (
    select
      :"SYS_B_1"
    from
      JK.L_SOO_SERVICES_XML C
    where
      TO_CHAR(a.TRANSACTIONID) = C.TRANSACTIONID
    and C.PARTITION_TIME       = TO_CHAR(sysdate, :"SYS_B_2")
  )
and not exists
  (
    select
      :"SYS_B_3"
    from
      CEP_ORDER_FINISH AA
    where
      AA.EXT_CUST_ORDER_ID = a.EXT_CUST_ORDER_ID
    and AA.SYSTEM_ID       = :"SYS_B_4"
  )

测试思路:
sql执行计划
Id Operation Name Rows Bytes Cost (%CPU) Time Pstart Pstop 0 INSERT STATEMENT       3905 (100)       1    VIEW VW_DIS_1 1 65 3905 (1) 00:00:47     2      SORT UNIQUE   1 127 3905 (1) 00:00:47     3        NESTED LOOPS ANTI   1 127 3904 (1) 00:00:47     4          NESTED LOOPS   1 99 3901 (1) 00:00:47     5            HASH JOIN   1 88 3803 (1) 00:00:46     6              SORT UNIQUE   1 47 2 (0) 00:00:01     7                PARTITION LIST SINGLE   1 47 2 (0) 00:00:01 KEY KEY 8                  TABLE ACCESS FULL L_SOO_SERVICES_XML 1 47 2 (0) 00:00:01 KEY KEY 9              TABLE ACCESS FULL SOO_ORDER_MAP 692K 27M 3796 (1) 00:00:46     10            PARTITION LIST ALL   3 33 98 (0) 00:00:02 1 49 11              INDEX RANGE SCAN IDX_L_O_ITEM_CUST_ORDER_ID 3 33 98 (0) 00:00:02 1 49 12          TABLE ACCESS BY INDEX ROWID CEP_ORDER_FINISH 27524 752K 3 (0) 00:00:01     13            INDEX RANGE SCAN IDX_CEP_ORDER_FINISH 1   2 (0) 00:00:01    

l_soo_services_xml为分区表，2954980条记录
Owner Table Name Tablespace Table Type Status Rows Blocks Avg Row Len Chain Count Degree Cache Analyzed JK CEP_ORDER_FINISH CRMDATA04 Normal Table VALID 247727 2512 69 0 1 N 2014-09-17 22:28:38 JK L_SOO_SERVICES_XML Partition Table VALID 2954980 1371042 282 0 1 N 2014-09-17 22:38:32

l_soo_services_xml表列partition_time不同值为4,注意列类型为number
Table Name
Column Name
Data Type
is Null
Distinct Num
Buckets Num
Histogram Type
Analyzed
L_SOO_SERVICES_XML
PARTITION_TIME
NUMBER
N
4
4
FREQUENCY
2014-09-17 22:38:32


执行计划结果源于plan_table表，其pstart及pstop列的官方含义

PARTITION_START

VARCHAR2(255)

Start partition of a range of accessed partitions. It can take one of the following values:

n indicates that the start partition has been identified by the SQL compiler, and its partition number is given by n.

KEY indicates that the start partition is identified at run time from partitioning key values.

ROW REMOVE_LOCATION indicates that the database computes the start partition (same as the stop partition) at run time from the location of each retrieved record. The record location is obtained by a user or from a global index.

INVALID indicates that the range of accessed partitions is empty.

PARTITION_STOP

VARCHAR2(255)

Stop partition of a range of accessed partitions. It can take one of the following values:

n indicates that the stop partition has been identified by the SQL compiler, and its partition number is given by n.

KEY indicates that the stop partition is identified at run time from partitioning key values.

ROW REMOVE_LOCATION indicates that the database computes the stop partition (same as the start partition) at run time from the location of each retrieved record. The record location is obtained by a user or from a global index.

INVALID indicates that the range of accessed partitions is empty.


SQL> create table t_par_1(list_id varchar2(8),list_name int)
  2  partition by list(list_id)
  3  (partition p1 values('20141111','20141112'),
  4   partition p2 values('20141113','20141114'),
       partition p4 values('20141117','20141118'),
       partition p3 values('20141115','20141116'),
  6   partition p4 values('20141117','20141118')
  8  );

Table created.

构建每个分区记录均匀分布的分区表
insert into t_par_1
select  (case when level <250000 then decode(sign(mod(level,2)),1,'20141111','20141112')
                     when level between 250000 and 500000 then  decode(sign(mod(level,2)),1,'20141113','20141114')
                     when level between 500001 and 800000 then  decode(sign(mod(level,2)),1,'20141115','20141116')
                     when level between 800001 and 1000000 then decode(sign(mod(level,2)),1,'20141117','20141118')
            end),
           level   
from dual connect by level<=1000000;

SQL> commit;

Commit complete.

SQL>
SQL> exec dbms_stats.delete_table_stats(user,'t_par_1');

PL/SQL procedure successfully completed.

SQL> select table_name,partition_name,partition_position,high_value,num_rows from user_tab_partitions where lower(table_name)='t_par_1' order by 3;

TABLE_NAME      PARTITION_ PARTITION_POSITION HIGH_VALUE                                            NUM_ROWS
--------------- ---------- ------------------ -------------------------------------------------------------------------------- ----------
T_PAR_1         P1                          1 '20141111', '20141112'                                                               248653
T_PAR_1         P2                          2 '20141113', '20141114'                                                               247997
T_PAR_1         P3                          3 '20141115', '20141116'                                                               299252
T_PAR_1         P4                          4 '20141117', '20141118'                                                               199864

SQL>



构建每个分区记录不均匀分布的分区表
create table t_par_2(list_id varchar2(8),list_name int)
partition by list(list_id)
(partition p1 values('20141111','20141112'),
 partition p2 values('20141113','20141114'),
 partition p4 values('20141115','20141116'),
 partition p3 values('20141117','20141118')
);


SQL> insert into t_par_2
select  (case when level <=10000 then decode(sign(mod(level,2)),1,'20141111','20141112')
  3                       when level between 500001 and 900000 then  decode(sign(mod(level,2)),1,'20141115','20141116')
                     when level between   10001 and 500000 then  decode(sign(mod(level,2)),1,'20141113','20141114')
  4                       when level between 500001 and 900000 then  decode(sign(mod(level,2)),1,'20141115','20141116')
                     when level between 900001 and 1000000 then decode(sign(mod(level,2)),1,'20141117','20141118')
            end),
  7             level   
  8  from dual connect by level<=1000000;

1000000 rows created.

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

PL/SQL procedure successfully completed.

SQL> select table_name,partition_name,partition_position,high_value,num_rows from user_tab_partitions where lower(table_name)='t_par_2' order by 3;

TABLE_NAME      PARTITION_ PARTITION_POSITION HIGH_VALUE                                                                         NUM_ROWS
--------------- ---------- ------------------ -------------------------------------------------------------------------------- ----------
T_PAR_2         P1                          1 '20141111', '20141112'                                                                10000
T_PAR_2         P2                          2 '20141113', '20141114'                                                               489971
T_PAR_2         P4                          3 '20141115', '20141116'                                                               401323
T_PAR_2         P3                          4 '20141117', '20141118'                                                               100000

各分区记录均匀：

    1，列值为具体值
SQL> select list_id,list_name from t_par_1 where list_id='20141111';

125000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1228640134

-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |   121K|  2257K|   189  (21)| 00:00:03 |       |       |
|   1 |  PARTITION LIST SINGLE|         |   121K|  2257K|   189  (21)| 00:00:03 |     1 |     1 |
|*  2 |   TABLE ACCESS FULL   | T_PAR_1 |   121K|  2257K|   189  (21)| 00:00:03 |     1 |     1 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LIST_ID"='20141111')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       8974  consistent gets
          0  physical reads
          0  redo size
    2770526  bytes sent via SQL*Net to client
      92155  bytes received via SQL*Net from client
       8335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     125000  rows processed

SQL>

2，列值为函数
SQL> select list_id,list_name from t_par_1 where list_id=to_char(sysdate-4,'yyyymmdd');

125000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3451832993

-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |   121K|  2257K|   652  (77)| 00:00:08 |       |       |
|   1 |  PARTITION LIST SINGLE|         |   121K|  2257K|   652  (77)| 00:00:08 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL   | T_PAR_1 |   121K|  2257K|   652  (77)| 00:00:08 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LIST_ID"=TO_CHAR(SYSDATE@!-4,'yyyymmdd'))

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       8974  consistent gets
          0  physical reads
          0  redo size
    2770526  bytes sent via SQL*Net to client
      92155  bytes received via SQL*Net from client
       8335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     125000  rows processed

SQL>

3，列值不隶属于任何一个分区（列值为具体值）
SQL> select list_id,list_name from t_par_1 where list_id='201411119';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3085917610

------------------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    19 |   151   (1)| 00:00:02 |       |       |
|   1 |  PARTITION LIST EMPTY|         |     1 |    19 |   151   (1)| 00:00:02 |INVALID|INVALID|
|*  2 |   TABLE ACCESS FULL  | T_PAR_1 |     1 |    19 |   151   (1)| 00:00:02 |INVALID|INVALID|
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LIST_ID"='201411119')

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        391  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>

4,列值不隶属于任何一个分区（列值为函数）
SQL> select list_id,list_name from t_par_1 where list_id=to_char(sysdate+20,'yyyymmdd');

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3451832993

-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |    31 |   589 |   662  (13)| 00:00:08 |       |       |
|   1 |  PARTITION LIST SINGLE|         |    31 |   589 |   662  (13)| 00:00:08 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL   | T_PAR_1 |    31 |   589 |   662  (13)| 00:00:08 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LIST_ID"=TO_CHAR(SYSDATE@!+20,'yyyymmdd'))

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        391  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>


5，列值为绑定变量
SQL> var v1 varchar2(8)
SQL> exec :v1:='20141111'

PL/SQL procedure successfully completed.
SQL> select list_id,list_name from t_par_1 where list_id=:v1;

125000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3451832993

-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         | 10305 |   191K|   608   (5)| 00:00:08 |       |       |
|   1 |  PARTITION LIST SINGLE|         | 10305 |   191K|   608   (5)| 00:00:08 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL   | T_PAR_1 | 10305 |   191K|   608   (5)| 00:00:08 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LIST_ID"=:V1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       8974  consistent gets
          0  physical reads
          0  redo size
    2770526  bytes sent via SQL*Net to client
      92155  bytes received via SQL*Net from client
       8335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     125000  rows processed

SQL> exec :v1:='20141030'

PL/SQL procedure successfully completed.

SQL> select list_id,list_name from t_par_1 where list_id=:v1;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3451832993

-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         | 10305 |   191K|   608   (5)| 00:00:08 |       |       |
|   1 |  PARTITION LIST SINGLE|         | 10305 |   191K|   608   (5)| 00:00:08 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL   | T_PAR_1 | 10305 |   191K|   608   (5)| 00:00:08 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LIST_ID"=:V1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        391  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>


各分区记录不均匀：
SQL> select list_id,list_name from t_par_2 where list_id='20141111';

5000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3518364490

-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |  5000 | 60000 |     8   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE|         |  5000 | 60000 |     8   (0)| 00:00:01 |     1 |     1 |
|*  2 |   TABLE ACCESS FULL   | T_PAR_2 |  5000 | 60000 |     8   (0)| 00:00:01 |     1 |     1 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LIST_ID"='20141111')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        363  consistent gets
          0  physical reads
          0  redo size
     106526  bytes sent via SQL*Net to client
       4155  bytes received via SQL*Net from client
        335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       5000  rows processed

SQL> select list_id,list_name from t_par_2 where list_id=to_char(sysdate-4,'yyyymmdd');

5000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 656430012

-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |  5000 | 60000 |     9  (12)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE|         |  5000 | 60000 |     9  (12)| 00:00:01 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL   | T_PAR_2 |  5000 | 60000 |     9  (12)| 00:00:01 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LIST_ID"=TO_CHAR(SYSDATE@!-4,'yyyymmdd'))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        363  consistent gets
          0  physical reads
          0  redo size
     106526  bytes sent via SQL*Net to client
       4155  bytes received via SQL*Net from client
        335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       5000  rows processed

SQL> select to_char(sysdate+40,'yyyymmdd') from dual;

TO_CHAR(
--------
20141225

SQL> select list_id,list_name from t_par_2 where list_id='20141225';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1128354708

------------------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |   124K|  1577K|     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST EMPTY|         |   124K|  1577K|     2   (0)| 00:00:01 |INVALID|INVALID|
|*  2 |   TABLE ACCESS FULL  | T_PAR_2 |   124K|  1577K|     2   (0)| 00:00:01 |INVALID|INVALID|
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LIST_ID"='20141001')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        391  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed



SQL> select list_id,list_name from t_par_2 where list_id=to_char(sysdate+40,'yyyymmdd');

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 656430012

-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |   124K|  1577K|   165  (13)| 00:00:02 |       |       |
|   1 |  PARTITION LIST SINGLE|         |   124K|  1577K|   165  (13)| 00:00:02 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL   | T_PAR_2 |   124K|  1577K|   165  (13)| 00:00:02 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LIST_ID"=TO_CHAR(SYSDATE@!+40,'yyyymmdd'))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        391  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


SQL> select list_id,list_name from t_par_2 where list_id=:v1;

5000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 656430012

-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |   124K|  1577K|   152   (5)| 00:00:02 |       |       |
|   1 |  PARTITION LIST SINGLE|         |   124K|  1577K|   152   (5)| 00:00:02 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL   | T_PAR_2 |   124K|  1577K|   152   (5)| 00:00:02 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LIST_ID"=:V1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        363  consistent gets
          0  physical reads
          0  redo size
     106526  bytes sent via SQL*Net to client
       4155  bytes received via SQL*Net from client
        335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       5000  rows processed

SQL>

SQL> exec :v1:='20141030'
SQL> select list_id,list_name from t_par_2 where list_id=:v1;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 656430012

-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |   124K|  1577K|   152   (5)| 00:00:02 |       |       |
|   1 |  PARTITION LIST SINGLE|         |   124K|  1577K|   152   (5)| 00:00:02 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL   | T_PAR_2 |   124K|  1577K|   152   (5)| 00:00:02 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LIST_ID"=:V1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        391  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

注意开文所述表L_SOO_SERVICES_XML的列partition_time为number,但sql where条件列为如下内容：
select
      :"SYS_B_1"
    from
      JK.L_SOO_SERVICES_XML C
    where
      TO_CHAR(a.TRANSACTIONID) = C.TRANSACTIONID
    and C.PARTITION_TIME       = TO_CHAR(sysdate, :"SYS_B_2")
可见有个隐式转换

继续测试隐式转换的SQL查询情况
SQL> set autot off
SQL> drop table t_par_1 purge;

Table dropped.

SQL> drop table t_par_2 purge;

Table dropped.

create table t_par_1(list_id number,list_name int)
partition by list(list_id)
(partition p1 values(20141111,20141112),
 partition p2 values(20141113,20141114),
  5   partition p4 values(20141115,20141116),
 partition p3 values(20141117,20141118)
  7  );

Table created.

SQL> create table t_par_2(list_id number,list_name int)
  2  partition by list(list_id)
  3  (partition p1 values(20141111,20141112),
 partition p2 values(20141113,20141114),
  5   partition p4 values(20141115,20141116),
  6   partition p3 values(20141117,20141118)
  7  );

Table created.


SQL> insert into t_par_1
select  (case when level <250000 then decode(sign(mod(level,2)),1,'20141111','20141112')
  3                       when level between 250000 and 500000 then  decode(sign(mod(level,2)),1,'20141113','20141114')
  4                       when level between 500001 and 800000 then  decode(sign(mod(level,2)),1,'20141115','20141116')
                     when level between 800001 and 1000000 then decode(sign(mod(level,2)),1,'20141117','20141118')
  6              end),
  7             level  
  8  from dual connect by level<=1000000;

1000000 rows created.

SQL> commit;

Commit complete.




SQL> insert into t_par_2
select  (case when level <=10000 then decode(sign(mod(level,2)),1,20141111,20141112)
  3                       when level between   10001 and 500000 then  decode(sign(mod(level,2)),1,20141113,20141114)
  4                       when level between 500001 and 900000 then  decode(sign(mod(level,2)),1,20141115,20141116)
                     when level between 900001 and 1000000 then decode(sign(mod(level,2)),1,20141117,20141118)
            end),
  7             level   
  8  from dual connect by level<=1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'t_par_1');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'t_par_2');

PL/SQL procedure successfully completed.


SQL> select list_id,list_name from t_par_1 where list_id=to_char(sysdate-4,'yyyymmdd');

125000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3451832993

-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |   125K|  1225K|   145  (16)| 00:00:02 |       |       |
|   1 |  PARTITION LIST SINGLE|         |   125K|  1225K|   145  (16)| 00:00:02 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL   | T_PAR_1 |   125K|  1225K|   145  (16)| 00:00:02 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LIST_ID"=TO_NUMBER(TO_CHAR(SYSDATE@!-4,'yyyymmdd')))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       8853  consistent gets
          0  physical reads
          0  redo size
    2770520  bytes sent via SQL*Net to client
      92155  bytes received via SQL*Net from client
       8335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     125000  rows processed


SQL> select list_id,list_name from t_par_1 where list_id=to_char(sysdate+30,'yyyymmdd');

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3451832993

-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |    10 |   145  (16)| 00:00:02 |       |       |
|   1 |  PARTITION LIST SINGLE|         |     1 |    10 |   145  (16)| 00:00:02 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL   | T_PAR_1 |     1 |    10 |   145  (16)| 00:00:02 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LIST_ID"=TO_NUMBER(TO_CHAR(SYSDATE@!+30,'yyyymmdd')))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        391  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


SQL> var v1 varchar2(8)
SQL> exec :v1:='yyyymmdd'

PL/SQL procedure successfully completed.

SQL> select list_id,list_name from t_par_1 where list_id=to_char(sysdate+30,:v1);

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3451832993

-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |   124K|  1218K|   153  (20)| 00:00:02 |       |       |
|   1 |  PARTITION LIST SINGLE|         |   124K|  1218K|   153  (20)| 00:00:02 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL   | T_PAR_1 |   124K|  1218K|   153  (20)| 00:00:02 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LIST_ID"=TO_NUMBER(TO_CHAR(SYSDATE@!+30,:V1)))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        391  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> select list_id,list_name from t_par_1 where list_id=to_char(sysdate-4,:v1);

125000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3451832993

-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |   124K|  1218K|   153  (20)| 00:00:02 |       |       |
|   1 |  PARTITION LIST SINGLE|         |   124K|  1218K|   153  (20)| 00:00:02 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL   | T_PAR_1 |   124K|  1218K|   153  (20)| 00:00:02 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LIST_ID"=TO_NUMBER(TO_CHAR(SYSDATE@!-4,:V1)))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       8853  consistent gets
          0  physical reads
          0  redo size
    2770520  bytes sent via SQL*Net to client
      92155  bytes received via SQL*Net from client
       8335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     125000  rows processed


SQL> select list_id,list_name from t_par_2 where list_id=to_char(sysdate+30,:v1);

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 656430012

-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |   125K|  1222K|   153  (20)| 00:00:02 |       |       |
|   1 |  PARTITION LIST SINGLE|         |   125K|  1222K|   153  (20)| 00:00:02 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL   | T_PAR_2 |   125K|  1222K|   153  (20)| 00:00:02 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LIST_ID"=TO_NUMBER(TO_CHAR(SYSDATE@!+30,:V1)))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        391  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> select list_id,list_name from t_par_2 where list_id=to_char(sysdate-4,:v1);

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 656430012

-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |   125K|  1222K|   153  (20)| 00:00:02 |       |       |
|   1 |  PARTITION LIST SINGLE|         |   125K|  1222K|   153  (20)| 00:00:02 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL   | T_PAR_2 |   125K|  1222K|   153  (20)| 00:00:02 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LIST_ID"=TO_NUMBER(TO_CHAR(SYSDATE@!+30,:V1)))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          359  consistent gets
          0  physical reads
          0  redo size
        391  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

小结：
       1,下述结论皆基于oracle 10.2.0.5下
       1,无论是各分区记录均匀或是不均匀，where条件列值为实际值抑或对应的函数值，
          sql执行计划逻辑及物理读相同
       2,pstart及pstop若是基于函数值，则显示为key,否则显示具体的分区编号
       3,若where条件值不隶属于分表区任何记录，则pstart及stop显示为invalid
          operation值为partition list empty,且物理及逻辑读值为0
       4,使用绑定变量是否，SQL性能结果相同，仅operation操作结果显示不同，值为partition list single
       5,pstart及pstop为n,表明扫描具体的分区
                              为key,表明在SQL执行确认具体的分区
                              为invalid,表明扫描不到任何的分区
          operation为partition list empty,表明扫描不到任何分区
       6,where条件列值存在隐式转换情况下，sql查询性能同于非隐式转换

个人简介

8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。
   
   服务过的客户：
          中国电信
          中国移动
          中国联通
          中国电通
          国家电网
          四川达州商业银行
          湖南老百姓大药房
          山西省公安厅
          中国邮政
          北京302医院     
          河北廊坊新奥集团公司
  
 项目经验:
           中国电信3G项目AAA系统数据库部署及优化
           中国联通CRM数据库性能优化
           中国移动10086电商平台数据库部署及优化
           湖南老百姓大药房ERR数据库sql优化项目
           四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化
           四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化
           北京高铁信号监控系统RAC数据库部署及优化
           河南宇通客车数据库性能优化
           中国电信电商平台核心采购模块表模型设计及优化
           中国邮政储蓄系统数据库性能优化及sql优化
           北京302医院数据库迁移实施
           河北廊坊新奥data guard部署及优化
           山西公安厅身份证审计数据库系统故障评估
           国家电网上海灾备项目4 node rac+adg 
 联系方式：
          手机：18201115468
          qq   :   305076427
          qq微博: wisdomone1
          新浪微博：wisdomone9
          qq群：275813900    
          itpub博客名称：wisdomone1    http://blog.itpub.net/9240380/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1336677/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-1336677/

clg10051
关注
oracle 建分区索引_Oracle分区表创建本地索引和局部索引的性能
weixin_35895994的博客
12-29 233
1、演示环境：SQL>select*fromv$version;BANNER----------------------------------------------------------------OracleDatabase10gEnterpriseEditionRelease10.2.0.4.0-64biPL/SQLRelease10.2.0.4.0-...
ORACLE:分区表range,hash,list
孔老先生的博客
07-29 2611
对表数据进行水平分割的一种手段! 降低I/O 分散I/O 基本概念：把一个大表分成几个部分 分区的方式： range 如上面的清单表，按照时间范围进行分区 好处： 可以分散IO，不同的分区可以放在不同的表空间上。 如有的表是按照手机号码的后两位分区，这个表就有100个分区，可以把IO分散到100个不同的segment上。 可靠性 一个分区故障，不会影响其它的
Pstart and Pstop in sql plan with interval partition
cq7936的博客
10-13 193
select * from v$version; BANNER --------------------------------------------------------------------------------...
数据库开发小知识普及二十--左右SQL执行计划妙招
keep12moving的博客
03-02 324
执行计划SQL写法差异改变之三_rowid的影响 环境准备： create table t as select * from dba_objects; 语句1： update t set object_id=rownum; create index idx_object_id on t(object_id); select rowid from t where object_id=8; ROWID
oracle 执行计划详解
最新发布
Ruishine的博客
04-22 4362
执行计划是指示 Oracle 如何获取和过滤数据、产生最终的结果集，是影响SQL 语句执行性能的关键因素。在深入了解执行计划之前，首先需要知道执行计划是在什么时候产生的，以及如何让 SQL 引擎为语句生成执行划。先了解 SQL 语句的处理执行过程。当一条语句提交到 Oracle 后，SQL 引擎会分为三个步骤对其处理和执行：解析（Parse）、执行（Execute）和获取（Fetch），分别由 SQL 引擎的不同组件完成。
oracle分区表执行计划分区合并,oracle分区表执行计划
weixin_30949673的博客
04-03 100
分区表有很多好处，以大化小，一小化了，加上并行的使用，在loap中能往往能提高几十倍甚至几百倍的效果。当然表设计得不好也会适得其反，效果比普通表跟糟糕。为了更好的使用分区表，这里看一下分区表的执行计划。PARTITION RANGE ALL：扫描所有分区PARTITION RANGE ITERATOR：扫描多个分区，小于所有个分区数量PARTITION RANGE SINGLE：扫描单一的分区KE...
Oracle查看执行计划
热门推荐
m0_71406734的博客
05-18 1万+
Oracle必知执行计划
oracle 亿级数据 索引,Oracle 如何在亿级记录表中创建索引
weixin_29133151的博客
04-07 1510
Oracle 如何在亿级记录表中创建索引1. 查看表的具体情况查看是不是分区表，有多少个分区、分区字段：SQL> col table_name for a20SQL> col column_name for a20SQL> select a.table_name,a.partitioned,b.partition_count,c.column_name2 from user_ta...
Oracle的分区修剪介绍：Partition Pruning
cuccntzd50572的博客
04-30 1298
Oracle的分区修剪介绍：Partition Pruning 该文译自官方guide Partition Pruning 在数据仓库中分区修剪是一种非常有效的性能特性。分析修剪分析SQL中的WHERE 和FROM字句，从...
Oracle 9i & 10g编程艺术-深入数据库体系结构——第13章：分区
knowhow的专栏
02-03 4033
第13章                      分区分区（partitioning）最早在Oracle 8.0中引入，这个过程是将一个表或索引物理地分解为多个更小、更可管理的部分。就访问数据库的应用而言，逻辑上讲只有一个表或一个索引，但在物理上这个表或索引可能由数十个物理分区组成。每个分区都是一个独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。注意     
oracle执行计划是什么意思,求教oracle执行计划中的意思 - Oracle / 非技术区
weixin_42174098的博客
04-03 178
求教oracle执行计划中的意思 - Oracle / 非技术区oracle执行计划中一些数字是什么意思，谁有这方面的资料给我学习学习比如PSTART,PSTOP等的数字是什么意思啊----------------------------------------------------------------------------------------------------| Id | Op...
分区扫描执行计划分析简介（引用）
u010719917的专栏
07-23 515
分区扫描执行计划分析简介 Posted on 2014 年 2 月 19 日 by xiaoyu     SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 10g En
oracle分区表的分区执行计划的几种扫描类型
viviliving的专栏
11-16 481
partition range inlist: 分区键中用了in 例如： where n1 in(X1,X2) and n2=X3。比range少了partition range or和partition range multi-column。partition range or: 分区键中用了or 例如 where n1=X1 or n2=X2。---1、partition list inlist 不连续分区扫描。---2、partition list inlist 不连续分区扫描。
Oracle索引
dianzu1827的博客
06-13 136
在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的 SQL 语句执行得更快。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。对于数据库来说，索引是一个必选项，但对于现在的各种大型数据库来说，索引可以大大提高数据库的性能，以至于它变成了数据库不可缺少的一部分。 索引分类: 逻辑分类 single column or concatenated：...
oracle分区表执行计划分区合并,分区表的执行计划 - Oracle数据库管理 - ITPUB论坛－中国专业的IT技术社区...
weixin_39931101的博客
04-03 99
在看一篇文章SQL> create table t2partition by list(owner)3(4 partition t_list_sys values ('SYS'),5 partition t_list_scott values ('SCOTT'),6 partition t_list_hr values ('HR'),7 partiti...
Oracle分区知识介绍-下集
m0_38036502的博客
03-18 460
六、分区裁剪（Partition pruning） 概念： 1、分区裁剪（Partition pruning），又叫分区修剪、分区消除，是指在对Oracle分区表进行查询时， 优化器会检查谓词条件中是否存在对分区段的过滤，如果存在，则可以仅访问符合条件的分区，即裁剪掉无用的分区。 2、分区裁剪（Partition pruning）是根据查询语句中的谓词条件来进行分区消除的，分区裁剪既可以在分区级别消除， 也可以在分区上的索引级别消除。 3、范围和列表分区上可以在谓词上使用等于，范围操作（><等.
oracle表类型数据索引,oracle_分区表的索引类型以及是否带分区键索引的区别
weixin_35834687的博客
04-10 212
create table parttest(owner varchar2(20) not null ,object_id number not null ,object_name varchar2(32) ,created date) partition by list(owner)(partition part1 values ('SYS') ,partition part2 values ...
Oracle 分区(partition)技术
cspec46064的专栏
04-25 261
Oracle 分区(partition)表分区表的好处:一：提高数据的可用性，分区表逻辑上是一个表，实际各分区的数据是独立存放的，一个分区可以离线的 同时，其他分区可以正常操作.二：减轻管理负担，对一个20g的对象...
分区扫描执行计划分析简介
塔山上的山
03-03 1206
分区扫描执行计划分析简介 转自：http://www.dbaxiaoyu.com/archives/1780   SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 1
Oracle性能优化：深度解析SQL执行计划Execution Plan
"Oracle性能优化讲座-真正读懂Oracle-SQL执行计划Execution-Plan" 在Oracle数据库管理系统中，SQL执行计划是数据库解析器为了执行一个SQL语句而制定的详细步骤，它直接影响到查询性能。理解并分析执行计划是进行...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值