• Oracle 分区表的统计信息实例


      ORACLE的统计信息在执行SQL的过程中扮演着非常重要的作用,而且ORACLE在表的各个层次都会有不同的统计信息,通过这些统计信息来描述表的,列的各种各样的统计信息。下面通过一个复合分区表来说明一些常见的和不常见的统计信息。

    搭建测试用例

    --创建表。
    SQL>
    create table test
    partition by range(object_id)
    subpartition by hash(object_type) subpartitions 4
    (partition p1 values less than(10000),
    partition p2 values less than(20000),
    partition p3 values less than(30000),
    partition p4 values less than(maxvalue))
    as
    select * from dba_objects;
    
    --收集统计信息
    sql>
    BEGIN
    dbms_stats.gather_table_stats(  ownname          => 'NC60',
                                    tabname          => 'TEST',
                                    estimate_percent => 100,                       --百分之百采样
                                    block_sample     => FALSE,
                                    method_opt       => 'FOR ALL COLUMNS SIZE 10', --收集直方图
                                    granularity      => 'ALL',                     --所有分区
                                    cascade          => TRUE                       --收集索引
                                 );
    END;

    1,表级的统计信息

    SQL> select table_name,num_rows,blocks,empty_blocks,avg_space 
      2  from user_tables 
      3  where table_name = 'TEST';
    
    TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE
    ------------------------------ ---------- ---------- ------------ ----------
    TEST                                87556       1328            0          0

    2,表上列的统计信息

    SQL> select table_name,column_name,num_distinct,density 
      2  from user_tab_columns 
      3  where table_name = 'TEST';
    
    TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT    DENSITY
    ------------------------------ ------------------------------ ------------ ----------
    TEST                           OWNER                                    34 .023504483
    TEST                           OBJECT_NAME                           57495 .000020431
    TEST                           SUBOBJECT_NAME                          163 .007094837
    TEST                           OBJECT_ID                             87556 .000011421
    TEST                           DATA_OBJECT_ID                        22685 .000044786
    TEST                           OBJECT_TYPE                              45 .086650298
    TEST                           CREATED                                2057 .000847961
    TEST                           LAST_DDL_TIME                          1898 .000919514
    TEST                           TIMESTAMP                              2182 .000838526
    TEST                           STATUS                                    1 5.7106E-06
    TEST                           TEMPORARY                                 2 5.7106E-06
    
    TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT    DENSITY
    ------------------------------ ------------------------------ ------------ ----------
    TEST                           GENERATED                                 2 5.7106E-06
    TEST                           SECONDARY                                 2 5.7106E-06
    TEST                           NAMESPACE                                21 .116972867
    TEST                           EDITION_NAME                              0          0

    3,表上列的直方图信息(OBJECT_ID列)

    SQL> col TABLE_NAME format a20
    SQL> col COLUMN_NAME format a40
    SQL> select table_name,column_name,endpoint_number,endpoint_value
      2  from user_tab_histograms
      3  where table_name = 'TEST'
      4  and column_name = 'OBJECT_ID';
    
    TABLE_NAME           COLUMN_NAME                              ENDPOINT_NUMBER ENDPOINT_VALUE
    -------------------- ---------------------------------------- --------------- --------------
    TEST                 OBJECT_ID                                              0              2
    TEST                 OBJECT_ID                                              1           8921
    TEST                 OBJECT_ID                                              2          17998
    TEST                 OBJECT_ID                                              3          26754
    TEST                 OBJECT_ID                                              4          35510
    TEST                 OBJECT_ID                                              5          44266
    TEST                 OBJECT_ID                                              6          53025
    TEST                 OBJECT_ID                                              7          62172
    TEST                 OBJECT_ID                                              8          71290
    TEST                 OBJECT_ID                                              9          82232
    TEST                 OBJECT_ID                                             10          91577

    4,分区的统计信息

    SQL> select partition_name,num_rows,blocks,empty_blocks,avg_space
      2  from user_tab_partitions
      3  where table_name = 'TEST';
    
    PARTITION_NAME                   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE
    ------------------------------ ---------- ---------- ------------ ----------
    P1                                   9708        147            0          0
    P2                                   9805        162            0          0
    P3                                  10000        156            0          0
    P4                                  58043        863            0          0

    5,分区上列的统计信息

    SQL> select column_name,num_distinct,density,num_nulls
      2  from user_part_col_statistics
      3  where table_name = 'TEST'
      4  and partition_name = 'P1';
    
    COLUMN_NAME                              NUM_DISTINCT    DENSITY  NUM_NULLS
    ---------------------------------------- ------------ ---------- ----------
    OWNER                                               5 .000051504          0
    OBJECT_NAME                                      7878 .000142267          0
    SUBOBJECT_NAME                                     25  .00877193       9594
    OBJECT_ID                                        9708 .000103008          0
    DATA_OBJECT_ID                                   2074 .000560776       7597
    OBJECT_TYPE                                        20 .080003882          0
    CREATED                                           141 .011559584          0
    LAST_DDL_TIME                                     213 .010730067          0
    TIMESTAMP                                         182 .011371733          0
    STATUS                                              1 .000051504          0
    TEMPORARY                                           2 .000051504          0
    
    COLUMN_NAME                              NUM_DISTINCT    DENSITY  NUM_NULLS
    ---------------------------------------- ------------ ---------- ----------
    GENERATED                                           2 .000051504          0
    SECONDARY                                           1 .000051504          0
    NAMESPACE                                           8 .000051504          0
    EDITION_NAME                                        0          0       9708

    6,分区上列的直方图信息(OBJECT_ID列)

    SQL> select column_name,bucket_number,endpoint_value
      2  from user_part_histograms
      3  where table_name = 'TEST'
      4  and partition_name = 'P1'
      5  and column_name = 'OBJECT_ID';
    
    COLUMN_NAME                              BUCKET_NUMBER ENDPOINT_VALUE
    ---------------------------------------- ------------- --------------
    OBJECT_ID                                            0              2
    OBJECT_ID                                            1           1030
    OBJECT_ID                                            2           2010
    OBJECT_ID                                            3           2981
    OBJECT_ID                                            4           3952
    OBJECT_ID                                            5           4923
    OBJECT_ID                                            6           5928
    OBJECT_ID                                            7           6953
    OBJECT_ID                                            8           7933
    OBJECT_ID                                            9           8903
    OBJECT_ID                                           10           9999
    
    11 rows selected.

    7,子分区的统计信息

    SQL> select subpartition_name,num_rows,blocks,empty_blocks
      2  from user_tab_subpartitions
      3  where table_name = 'TEST'
      4  and partition_name = 'P1';
    
    SUBPARTITION_NAME                NUM_ROWS     BLOCKS EMPTY_BLOCKS
    ------------------------------ ---------- ---------- ------------
    SYS_SUBP21                           3314         48            0
    SYS_SUBP22                           3824         57            0
    SYS_SUBP23                            528         11            0
    SYS_SUBP24                           2042         31            0
    
    SQL>

    8,子分区上的列的统计信息

    SQL> select column_name,num_distinct,density
      2  from user_subpart_col_statistics
      3  where table_name = 'TEST'
      4  and subpartition_name = 'SYS_SUBP21';
    
    COLUMN_NAME                              NUM_DISTINCT    DENSITY
    ---------------------------------------- ------------ ----------
    OWNER                                               3 .000150875
    OBJECT_NAME                                      3314  .00030175
    SUBOBJECT_NAME                                      1 .009615385
    OBJECT_ID                                        3314  .00030175
    DATA_OBJECT_ID                                    178 .005617978
    OBJECT_TYPE                                         7 .000150875
    CREATED                                           126 .017527186
    LAST_DDL_TIME                                     148  .01653325
    TIMESTAMP                                         134 .017483116
    STATUS                                              1 .000150875
    TEMPORARY                                           2 .000150875
    
    COLUMN_NAME                              NUM_DISTINCT    DENSITY
    ---------------------------------------- ------------ ----------
    GENERATED                                           2 .000150875
    SECONDARY                                           1 .000150875
    NAMESPACE                                           5 .000150875
    EDITION_NAME                                        0          0

    9,子分区上的列的直方图信息

    SQL> select column_name,bucket_number,endpoint_value
      2  from user_subpart_histograms
      3  where table_name = 'TEST'
      4  and subpartition_name = 'SYS_SUBP21'
      5  and column_name = 'OBJECT_ID';
    
    COLUMN_NAME                              BUCKET_NUMBER ENDPOINT_VALUE
    ---------------------------------------- ------------- --------------
    OBJECT_ID                                            0            100
    OBJECT_ID                                            1           1764
    OBJECT_ID                                            2           2429
    OBJECT_ID                                            3           3088
    OBJECT_ID                                            4           3752
    OBJECT_ID                                            5           4309
    OBJECT_ID                                            6           4640
    OBJECT_ID                                            7           5828
    OBJECT_ID                                            8           7278
    OBJECT_ID                                            9           8912
    OBJECT_ID                                           10           9998

    我们对这个复合分区分析之后产生了上面这九种不同层次的统计信息。CBO想要得要一个高效的执行计划需要如此多的统计信息

  • 相关阅读:
    将Python脚本变为命令行--click模块使用
    MongoDB大批量读写数据优化记录
    [转]MongoDB更新操作replaceOne()实例讲解
    pip 18.1: pipenv graph results in ImportError: cannot import name 'get_installed_distributions'
    mitmdump 屏蔽443错误
    python3 操作appium
    appium-Could not obtain screenshot: [object Object]
    scrapy主动触发关闭爬虫
    匿名函数
    装饰器1、无参数的装饰器 2、有参数的装饰器 3、装饰器本身带参数的以及如果函数带return结果的情况
  • 原文地址:https://www.cnblogs.com/polestar/p/4435347.html
Copyright © 2020-2023  润新知