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

  • wisdomone1

    2014-11-17 20:18:56
  • Oracle
  • 原创
前言：
    优化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/
请使用浏览器的分享功能分享到微信等