首页 > 代码库 > oracle 10g下范围分区扫描的几种方式

oracle 10g下范围分区扫描的几种方式

2024-07-03 17:38:51   228人阅读

   oracle 10g下有几种扫描方式，注意最后一种扫描方式，当对分区的列进行计算时，会不走分区，这跟对索引列进行计算会导致无法用索引一样。

   --扫描单个分区  PARTITION RANGE SINGLE
   --连续扫描多个分区 PARTITION RANGE ITERATOR
   --不连续扫描多个分区  PARTITION RANGE INLIST
   --扫描全分区 PARTITION RANGE ALL

SQL> drop table t_range purge;

SQL> create table t_range (id number not null PRIMARY KEY, test_date date) partition by range (test_date)
    (
    partition p_1 values less than (to_date(‘2013-12-01‘, ‘yyyy-mm-dd‘)),
    partition p_2 values less than (to_date(‘2014-02-01‘, ‘yyyy-mm-dd‘)),
    partition p_3 values less than (to_date(‘2014-03-01‘, ‘yyyy-mm-dd‘)),
    partition p_4 values less than (to_date(‘2014-04-01‘, ‘yyyy-mm-dd‘)),
    partition p_5 values less than (to_date(‘2014-05-01‘, ‘yyyy-mm-dd‘)),
    partition p_6 values less than (to_date(‘2014-06-01‘, ‘yyyy-mm-dd‘)),
    partition p_max values less than (MAXVALUE)
   ) nologging;
SQL> insert /*+append */ into t_range  select rownum,
           to_date(to_char(sysdate - 140, ‘J‘) +
                   trunc(dbms_random.value(0, 80)),
                   ‘J‘)
      from dual
    connect by rownum <= 100000;
SQL> commit;
SQL> exec dbms_stats.gather_table_stats(user,‘T_RANGE‘);
SQL> select to_char(t.test_date,‘yyyy-MM‘),count(1) from t_range t
     group by to_char(t.test_date,‘yyyy-MM‘);
TO_CHAR   COUNT(1)
------- ----------
2014-01      38803
2014-03      11242
2013-12      15107
2014-02      34848

SQL> set autotrace traceonly
--扫描单个分区
SQL> select * from t_range  where test_date = to_date(‘2014-04-28‘, ‘yyyy-mm-dd‘);
执行计划
----------------------------------------------------------
Plan hash value: 3010141842
--------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |     1 |    22 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|         |     1 |    22 |     2   (0)| 00:00:01 |     5 |     5 |
|*  2 |   TABLE ACCESS FULL    | T_RANGE |     1 |    22 |     2   (0)| 00:00:01 |     5 |     5 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("TEST_DATE"=TO_DATE(‘2014-04-28 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘))
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        327  bytes sent via SQL*Net to client
        374  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 *  from t_range
     where test_date <= to_date(‘2014-04-28‘, ‘yyyy-mm-dd‘)
       and test_date >= to_date(‘2014-02-28‘, ‘yyyy-mm-dd‘);
执行计划
----------------------------------------------------------
Plan hash value: 1921532398
----------------------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |         | 12556 |   147K|    28   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|         | 12556 |   147K|    28   (0)| 00:00:01 |     3 |     5 |
|*  2 |   TABLE ACCESS FULL      | T_RANGE | 12556 |   147K|    28   (0)| 00:00:01 |     3 |     5 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("TEST_DATE">=TO_DATE(‘2014-02-28 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) AND
              "TEST_DATE"<=TO_DATE(‘2014-04-28 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘))
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        956  consistent gets
          0  physical reads
          0  redo size
     309138  bytes sent via SQL*Net to client
       9515  bytes received via SQL*Net from client
        832  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      12453  rows processed

--不连续扫描多个分区
SQL> select *  from t_range
     where test_date = to_date(‘2014-04-28‘, ‘yyyy-mm-dd‘)
       or test_date = to_date(‘2014-02-28‘, ‘yyyy-mm-dd‘);
执行计划
----------------------------------------------------------
Plan hash value: 2021067984
--------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |  1678 | 20136 |    21   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE INLIST|         |  1678 | 20136 |    21   (0)| 00:00:01 |KEY(I) |KEY(I) |
|*  2 |   TABLE ACCESS FULL    | T_RANGE |  1678 | 20136 |    21   (0)| 00:00:01 |KEY(I) |KEY(I) |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("TEST_DATE"=TO_DATE(‘2014-02-28 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) OR
              "TEST_DATE"=TO_DATE(‘2014-04-28 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘))
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        175  consistent gets
          0  physical reads
          0  redo size
      22646  bytes sent via SQL*Net to client
       1265  bytes received via SQL*Net from client
         82  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1211  rows processed


--扫描全分区
SQL> select * from t_range where to_char(test_date,‘yyyy-MM-dd‘)=‘2014-04-01‘;
执行计划
----------------------------------------------------------
Plan hash value: 2128486036
-----------------------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |   994 | 11928 |    59   (4)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|         |   994 | 11928 |    59   (4)| 00:00:01 |     1 |     7 |
|*  2 |   TABLE ACCESS FULL | T_RANGE |   994 | 11928 |    59   (4)| 00:00:01 |     1 |     7 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_CHAR(INTERNAL_FUNCTION("TEST_DATE"),‘yyyy-MM-dd‘)=‘2014-04-01‘)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        272  consistent gets
          0  physical reads
          0  redo size
        327  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


联系
我们