• 分区裁剪


    explain plan for select /*+leading (T6) use_nl( T1 T6)*/ * from T_PM_ACCT_DTL_AF T1,(SELECT 
                    *
                     FROM S_PM_MGR_DEPT_RELA A
                    WHERE DEPT1_CODE <> '999999999') T6
          where T1.MGR_CODE = T6.MGR_CODE
          and t1.data_date>20130101;
    select * from table(dbms_xplan.display());
    
    Plan hash value: 3887218478
     
    ---------------------------------------------------------------------------------------------------------------
    | Id  | Operation                | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ---------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT         |                    |  1173M|   358G|    52G  (2)|999:59:59 |       |       |
    |   1 |  NESTED LOOPS            |                    |  1173M|   358G|    52G  (2)|999:59:59 |       |       |
    |*  2 |   TABLE ACCESS FULL      | S_PM_MGR_DEPT_RELA |  6303 |   683K|    45   (0)| 00:00:01 |       |       |
    |   3 |   PARTITION LIST ITERATOR|                    |   186K|    38M|  8354K  (2)| 27:50:58 |   KEY |   KEY |
    |*  4 |    TABLE ACCESS FULL     | T_PM_ACCT_DTL_AF   |   186K|    38M|  8354K  (2)| 27:50:58 |   KEY |   KEY |
    ---------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - filter("DEPT1_CODE"<>'999999999')
       4 - filter("T1"."MGR_CODE"="A"."MGR_CODE")
    
    key key  Oracle不知道要扫描那些分区,动态分区裁剪
    
    explain plan for select * from T_PM_ACCT_DTL_AF t1 
    where t1.data_date='20130101';
    select * from table(dbms_xplan.display());
    
    Plan hash value: 1825735905
     
    ----------------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ----------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |                  |  1312K|   267M|  9232   (2)| 00:01:51 |       |       |
    |   1 |  PARTITION LIST SINGLE|                  |  1312K|   267M|  9232   (2)| 00:01:51 |   KEY |   KEY |
    |   2 |   TABLE ACCESS FULL   | T_PM_ACCT_DTL_AF |  1312K|   267M|  9232   (2)| 00:01:51 |   368 |   368 |
    ----------------------------------------------------------------------------------------------------------

  • 相关阅读:
    实战SpringCloud响应式微服务系列教程(第八章)构建响应式RESTful服务
    说说hashCode() 和 equals() 之间的关系?
    说说Object类下面有几种方法呢?
    Redis中是如何实现分布式锁的?
    从实践角度重新理解BIO和NIO
    数据的异构实战(一) 基于canal进行日志的订阅和转换
    The base command for the Docker CLI.
    Installing Jenkins to Centos Docker
    Docker Community Edition for CentOS
    Kafka自我学习-报错篇
  • 原文地址:https://www.cnblogs.com/hzcya1995/p/13352351.html
Copyright © 2020-2023  润新知