h1.히스토그램

• 오라클은 히스토그램을 사용하여 데이터 분포가 고르지 않은 환경에서 Selectivity와 Cardinality 계산을 향상시킴
• 데이터 분포가 고르지 않을 때 히스토그램을 생성하지 않으면 NDV( Number of Distinct Value) 값을 활용하여 Cardinality를 계산하지만 히스토그램을 생성하면 정확하게 Cardinality 값을 계산할 수 있다.

h3.(1) 히스토그램 유형

• 높이균형 (Height-Balanced) 히스토그램
• 도수분포(Frequency) 히스토그램

-- sample
create sequence seq;
create table member( mem_id number, age number( 2 ) );
exec dbms_random.seed(0);
insert into member
select seq.nextval, dbms_random.value( 1,19 ) from dual connect by level <= 50;
insert into member
select seq.nextval, dbms_random.value( 20,29 ) from dual connect by level <= 270;
insert into member
select seq.nextval, dbms_random.value( 30,39 ) from dual connect by level <= 330;
insert into member
select seq.nextval, dbms_random.value( 30,39 ) from dual connect by level <= 330;
insert into member
select seq.nextval, 40  from dual connect by level <= 1000; --> popular value
insert into member
select seq.nextval, dbms_random.value( 41,49 ) from dual connect by level <= 200;
insert into member
select seq.nextval, dbms_random.value( 50,59 ) from dual connect by level <= 100;
insert into member
select seq.nextval, dbms_random.value( 60,99 ) from dual connect by level <= 50;

SU   COUNT(*)
-- ----------
10         50
20        270
30        660
40       1200
50        100
60         50

h3.(2) 도수분포 히스토그램

• 값별로 빈도수{frequency number)를 저장하는 히스토그램
  

select count(*) , count(distinct age) from member;

  COUNT(*)                      COUNT(DISTINCTAGE)
---------- ---------------------------------------
      2330                                      83

begin
  dbms_stats.gather_table_stats( user, 'MEMBER', method_opt=> 'for all columns size 100' );  
end;

PL/SQL 프로시저가 성공적으로 완료되었습니다.



select num_distinct, num_buckets, histogram
  from user_tab_col_statistics
 where table_name  = 'MEMBER'
   and column_name = 'AGE' ;


                           NUM_DISTINCT                             NUM_BUCKETS HISTOGRAM     
--------------------------------------- --------------------------------------- ---------------
                                     83                                      83 FREQUENCY      

h3.(3) 높이균형 히스토그램

• 컬럼이 가진 값의 수보다 적은 버킷을 요청할 때 만들어진다.
• 버킷 개수보다 값의 수가 많기 때문에 하나의 버킷이 여러 개 값을담당
• 값의 수가 100개인데 10개의 버킷을요청하면하나의 버킷이 평균적으로 10개의 값을 대표한다.

• 예제 테이블에는 총 2000건이 입력돼있고, 버킷이 20개H 이므로 각 버킷마다 100(2000/20)개 레코드에 해당하는 높이(=빈도수)를 갖는다. 분포는 각 5%씩 이다.
  

begin
  dbms_stats.gather_table_stats( user, 'MEMBER', method_opt=> 'for all columns size 20' );  
end;

PL/SQL 프로시저가 성공적으로 완료되었습니다.

select num_distinct, num_buckets, histogram
  from user_tab_col_statistics
 where table_name  = 'MEMBER'
   and column_name = 'AGE' ;


                           NUM_DISTINCT                             NUM_BUCKETS HISTOGRAM     
--------------------------------------- --------------------------------------- ---------------
                                     83                                      20 HEIGHT BALANCED


-- endpoint_number : 버킷 변호
-- endpoint3alue   : 버킷이 담당하는 가장 큰 값


select endpoint_number, endpoint_value
  from user_histograms
 where table_name  = 'MEMBER'
   and column_name = 'AGE' 
 order by 1;

                        ENDPOINT_NUMBER                          ENDPOINT_VALUE
--------------------------------------- ---------------------------------------
                                      0                                       1  <- 최소값을 표현하는 용도
                                      1                                      22
                                      2                                      27
                                      3                                      30
                                      4                                      32
                                      5                                      33
                                      6                                      35
                                      7                                      37
                                      8                                      38
                                     16                                      40  <- popular vlaue
                                     17                                      41
                                     18                                      46
                                     19                                      53
                                     20                                      99

==========================================================================================

* endpoint_number=l 벼킷은 1~21 연령대 구간을， endpoint_number= 20 버킷은 56~99 연령대 구간을 대표한다.
* popular vlaue ENDPOINT_NUMBER 8->16 으로 증가된것을 볼수있다. ENDPOINT_NUMBER 가 생략된 형태