HelloDBA [中文]
Search Internet Search HelloDBA
  Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com Add to circle  acoug  acoug 

CBO Statistics data gathering and internal arithmetic analysis -- Converting to endpoint value

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2010-01-13 03:06:07

Share to  Twitter Facebook GMail Blogger Orkut Google Bookmarks Sina Weibo QQ Renren Tieba Kaixin Douban Taobao

3. Converting to endpoint value 

    If we draw the histogram, the endpoint value should be x axis, and the endpoint number should be y axis. For those columns with histogram, oracle will convert their values to the data type of endpoint value, say, number. We will discuss how does the endpoint number and endpoint value be stored.
 

Endpoint Number

    For Endpoint Number, I have concluded it in previous paper "Frequency or Height Balanced".

  •   In Frequency Histogram, the Endpoint Number is the accumunltive records number of the values in sequence. For example, there are 2 records are "1", 1 record is "2", 5 records are "3" ..., then, the correspond buckets' Endpoint Number are 2, 3(2+1), 8(3+5);
  •   In Height Balanced Histogram, the Endpoint Number is the number of average bucket. For example, value 1,2,3 are located in the 1st bucket; value 4 is a popular value, which occupied 2 buckets, who has been compressed as 1 bucket; value 5 is located in the 3rd bucket..., then, Endpoint Number of these 3 buckets are 1, 3(2+1), 4(3+1).

Endpoing Value

    We can find the data type of endpoint value from the dictionary table histgrm$. It means all kinds of datatype will be converted to number finally.
 

SQL代码
  1. HELLODBA.COM>desc histgrm$   
  2.  Name          Null?    Type   
  3.  ------------- -------- -------------------------------------   
  4.  OBJ#          NOT NULL NUMBER   
  5.  COL#          NOT NULL NUMBER   
  6.  ROW#                   NUMBER   
  7.  BUCKET        NOT NULL NUMBER   
  8.  ENDPOINT      NOT NULL NUMBER   
  9.  INTCOL#       NOT NULL NUMBER   
  10.  EPVALUE                VARCHAR2(1000)   
  11.  SPARE1                 NUMBER   
  12.  SPARE2                 NUMBER   

    Let's look into the conversion one datatype by one.
 

    Note: Data precision to be analyzed for creating histogram is not same as the endpoint value data precision, which will also be discussed below.
 

    Let's prepare the test data first.
 

SQL代码
  1. HELLODBA.COM>create table demo.htc3 (a number, b date, c raw(100), d varchar2(100), e clob, f rowid);   
  2.   
  3. Table created.   
  4.   
  5. HELLODBA.COM>insert into demo.htc3 values (1, to_date('2010-12-07 00:00:01''YYYY-MM-DD HH24:MI:SS'), '01''A''A''AAAxdYAAFAAAPJUAAA');   
  6.   
  7. 1 row created.   
  8.   
  9. HELLODBA.COM>insert into demo.htc3 values (2, to_date('2010-12-07 00:00:02''YYYY-MM-DD HH24:MI:SS'), '02''BB''B','AAAxdYAAFAAAPJUAAB');   
  10.   
  11. 1 row created.   
  12.   
  13. HELLODBA.COM>insert into demo.htc3 values (3, to_date('2010-12-07 00:00:03''YYYY-MM-DD HH24:MI:SS'), '03''CCC''C','AAAxdYAAFAAAPJUAAC');   
  14.   
  15. 1 row created.   
  16.   
  17. HELLODBA.COM>insert into demo.htc3 values (4, to_date('2010-12-07 00:00:04''YYYY-MM-DD HH24:MI:SS'), '04''DDDDD''D','AAAxdYAAFAAAPJUAAD');   
  18.   
  19. 1 row created.   
  20.   
  21. HELLODBA.COM>insert into demo.htc3 values (5, to_date('2010-12-07 00:00:05''YYYY-MM-DD HH24:MI:SS'), '05''EEEEEE''E','AAAxdYAAFAAAPJUAAE');   
  22.   
  23. 1 row created.   
  24.   
  25. HELLODBA.COM>insert into demo.htc3 values (6, to_date('2010-12-07 12:50:01''YYYY-MM-DD HH24:MI:SS'), '06''FFFFFFF''F','AAAxdYAAFAAAPJUAAF');   
  26.   
  27. 1 row created.   
  28.   
  29. HELLODBA.COM>insert into demo.htc3 values (7.654321, to_date('2010-12-07 12:50:02''YYYY-MM-DD HH24:MI:SS')+1, '07''FFFFFF1''G','AAAxdYAAFAAAPJUAAG');   
  30.   
  31. 1 row created.   
  32.   
  33. HELLODBA.COM>insert into demo.htc3 values (8.7654321, to_date('2010-12-07 12:50:03''YYYY-MM-DD HH24:MI:SS')+2, '08''FFFFFF2''H','AAAxdYAAFAAAPJUAAH');   
  34.   
  35. 1 row created.   
  36.   
  37. HELLODBA.COM>insert into demo.htc3 values (9.87654321, to_date('2010-12-07 12:50:04''YYYY-MM-DD HH24:MI:SS')+3, '09''FFFFFF3''I','AAAxdYAAFAAAPJUAAI');   
  38.   
  39. 1 row created.   
  40.   
  41. HELLODBA.COM>insert into demo.htc3 values (10.987654321, to_date('2010-12-07 12:50:05''YYYY-MM-DD HH24:MI:SS'), '0A''FFFFFFF''J','AAAxdYAAFAAAPJUAAJ');   
  42.   
  43. 1 row created.   
  44.   
  45. HELLODBA.COM>insert into demo.htc3 values (2.123456789123123456789123456789123456789E33, to_date('2010-12-07 12:50:01''YYYY-MM-DD HH24:MI:SS')+100,'AC1265231212CDAC1265231212CDAC1265231212CDAC1265231212CDAC1265231212CD', lpad('A',35,'C'), lpad('A',35,'C'),'AAAxdYAAFAAAPJUAAK');   
  46.   
  47. 1 row created.   
  48.   
  49. HELLODBA.COM>insert into demo.htc3 values (2.123456789123123456789123456789123456790E33, to_date('2010-12-07 12:50:01''YYYY-MM-DD HH24:MI:SS')+101,'AC1265231212CDAC1265231212CDAC1265231212CDAC1265231212CDAC1265231212AB', lpad('A',35,'C')||'1', lpad('A',35,'C')||'1','AAAxdYAAFAAAPJUAAL');   
  50.   
  51. 1 row created.   
  52.   
  53. HELLODBA.COM>insert into demo.htc3 values (2.123456789123123456789123456789123456789E35, to_date('2010-12-07 12:50:59''YYYY-MM-DD HH24:MI:SS'), 'AC1265231212CDAC1265231212CDAC1265231212CDAC1265231212CDAC1265231212EF', lpad('A',35,'C')||'2', lpad('A',35,'C')||'2','AAAxdYAAFAAAPJUAAM');   
  54.   
  55. 1 row created.   
  56.   
  57. HELLODBA.COM>commit;   
  58.   
  59. Commit complete.   
  60.   
  61. HELLODBA.COM>set serveroutput on  
  62. HELLODBA.COM>begin  
  63.   2   dbms_output.enable(1000000);   
  64.   3   dbms_stats.set_param('TRACE',16383);   
  65.   4   dbms_stats.gather_table_stats('DEMO','HTC3',NULL,0,FALSE,'FOR ALL COLUMNS');   
  66.   5  end;   
  67.   6  /   
  68. ...   

Number (NUMBER and the subtypes)

    We can acknowledge oracle use the original data to group the bucket for histogram from the traced query.
 

SQL代码
  1. select substrb(dump(val, 16, 0, 32), 1, 120) ep, cnt   
  2.   from (select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */   
  3.          "A" val, count(*) cnt   
  4.           from "DEMO"."HTC3" t   
  5.          where "A" is not null  
  6.          group by "A")   
  7.  order by val   

    To store a number value as endpoint value, oracle will ROUND it to the 15th position start from left end.
 

SQL代码
  1. HELLODBA.COM>set numw 50   
  2. HELLODBA.COM>select endpoint_number,   
  3.   2         endpoint_value   
  4.   3    from dba_histograms   
  5.   4   where owner = 'DEMO'  
  6.   5     and table_name = 'HTC3'  
  7.   6     and column_name = 'A';   
  8.   
  9.                          ENDPOINT_NUMBER                           ENDPOINT_VALUE   
  10. ---------------------------------------- ----------------------------------------   
  11.                                        1                                        1   
  12.                                        2                                        2   
  13.                                        3                                        3   
  14.                                        4                                        4   
  15.                                        5                                        5   
  16.                                        6                                        6   
  17.                                        7                                 7.654321   
  18.                                        8                                8.7654321   
  19.                                        9                               9.87654321   
  20.                                       10                             10.987654321   
  21.                                       11       2123456789123120000000000000000000   
  22.                                       12       2123456789123120000000000000000000   
  23.                                       13     212345678912312000000000000000000000   
  24.   
  25. 13 rows selected.   

    Wo can calculate the endpoint value from actual data value with rounding.
 

SQL代码
  1. HELLODBA.COM>select a, round(a,15-length(trunc(a))) from demo.htc3;   
  2.   
  3.                                        A             ROUND(A,15-LENGTH(TRUNC(A)))   
  4. ---------------------------------------- ----------------------------------------   
  5.                                        1                                        1   
  6.                                        2                                        2   
  7.                                        3                                        3   
  8.                                        4                                        4   
  9.                                        5                                        5   
  10.                                        6                                        6   
  11.                                 7.654321                                 7.654321   
  12.                                8.7654321                                8.7654321   
  13.                               9.87654321                               9.87654321   
  14.                             10.987654321                             10.987654321   
  15. 2123456789123123456789123456789123.45679       2123456789123120000000000000000000   
  16. 2123456789123123456789123456789123.45679       2123456789123120000000000000000000   
  17. 212345678912312345678912345678912345.679     212345678912312000000000000000000000   
  18.   
  19. 13 rows selected.   

    Because the difference of precision between data to create histogram and the endpoint value, there may be multiple buckets with same endpoint value. This will confuse the optimizer to estimate the cost. Look at below case.
 

SQL代码
  1. HELLODBA.COM>create table demo.htc5 (a number);   
  2.   
  3. Table created.   
  4.   
  5. HELLODBA.COM>insert into demo.htc5 values(123456789.123456789);   
  6.   
  7. 1 row created.   
  8.   
  9. HELLODBA.COM>insert into demo.htc5 values(123456789.123456799);   
  10.   
  11. 1 row created.   
  12.   
  13. HELLODBA.COM>insert into demo.htc5 values(123456789.123456799);   
  14.   
  15. 1 row created.   
  16.   
  17. HELLODBA.COM>insert into demo.htc5 values(123456789.123456799);   
  18.   
  19. 1 row created.   
  20.   
  21. HELLODBA.COM>insert into demo.htc5 values(123456789.123456799);   
  22.   
  23. 1 row created.   
  24.   
  25. HELLODBA.COM>insert into demo.htc5 values(123456789.123456799);   
  26.   
  27. 1 row created.   
  28.   
  29. HELLODBA.COM>insert into demo.htc5 values(123456799.123456799);   
  30.   
  31. 1 row created.   
  32.   
  33. HELLODBA.COM>insert into demo.htc5 values(123456799.123456799);   
  34.   
  35. 1 row created.   
  36.   
  37. HELLODBA.COM>commit;   
  38.   
  39. Commit complete.   
  40.   
  41. HELLODBA.COM>begin  
  42.   2   dbms_output.enable(1000000);   
  43.   3   dbms_stats.set_param('TRACE',16383);   
  44.   4   dbms_stats.gather_table_stats('DEMO','HTC5',NULL,0,FALSE,'FOR ALL COLUMNS');   
  45.   5  end;   
  46.   6  /   
  47.   
  48. PL/SQL procedure successfully completed.   
  49.   
  50. HELLODBA.COM>select endpoint_number,   
  51.   2         endpoint_value   
  52.   3    from dba_histograms   
  53.   4   where owner = 'DEMO'  
  54.   5     and table_name = 'HTC5'  
  55.   6     and column_name = 'A';   
  56.   
  57.                          ENDPOINT_NUMBER                           ENDPOINT_VALUE   
  58. ---------------------------------------- ----------------------------------------   
  59.                                        1                         123456789.123457   
  60.                                        6                         123456789.123457   
  61.                                        8                         123456799.123457   
 

    The endpoint value of the 1st & 2nd bucket are the same value. Let's explain the query to equal predict these values.
 

SQL代码
  1. HELLODBA.COM>set autot trace exp   
  2. HELLODBA.COM>select * from demo.htc5 where a=123456789.123456789;   
  3.   
  4. Execution Plan   
  5. ----------------------------------------------------------   
  6. Plan hash value: 4195264197   
  7.   
  8. --------------------------------------------------------------------------   
  9. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |   
  10. --------------------------------------------------------------------------   
  11. |   0 | SELECT STATEMENT  |      |     5 |    60 |     3   (0)| 00:00:01 |   
  12. |*  1 |  TABLE ACCESS FULL| HTC5 |     5 |    60 |     3   (0)| 00:00:01 |   
  13. --------------------------------------------------------------------------   
  14.   
  15. Predicate Information (identified by operation id):   
  16. ---------------------------------------------------   
  17.   
  18.    1 - filter("A"=123456789.123456789)   
  19.   
  20. HELLODBA.COM>select * from demo.htc5 where a=123456789.123456799;   
  21.   
  22. Execution Plan   
  23. ----------------------------------------------------------   
  24. Plan hash value: 4195264197   
  25.   
  26. --------------------------------------------------------------------------   
  27. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |   
  28. --------------------------------------------------------------------------   
  29. |   0 | SELECT STATEMENT  |      |     5 |    60 |     3   (0)| 00:00:01 |   
  30. |*  1 |  TABLE ACCESS FULL| HTC5 |     5 |    60 |     3   (0)| 00:00:01 |   
  31. --------------------------------------------------------------------------   
  32.   
  33. Predicate Information (identified by operation id):   
  34. ---------------------------------------------------   
  35.   
  36.    1 - filter("A"=123456789.123456799)   
  37.   
  38. HELLODBA.COM>select * from demo.htc5 where a=123456799.123456799;   
  39.   
  40. Execution Plan   
  41. ----------------------------------------------------------   
  42. Plan hash value: 4195264197   
  43.   
  44. --------------------------------------------------------------------------   
  45. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |   
  46. --------------------------------------------------------------------------   
  47. |   0 | SELECT STATEMENT  |      |     2 |    24 |     3   (0)| 00:00:01 |   
  48. |*  1 |  TABLE ACCESS FULL| HTC5 |     2 |    24 |     3   (0)| 00:00:01 |   
  49. --------------------------------------------------------------------------   
  50.   
  51. Predicate Information (identified by operation id):   
  52. ---------------------------------------------------   
  53.   
  54.    1 - filter("A"=123456799.123456799)   

    When estimate the number of rows of the value of the 1st bucket, the optimizer got the number of the 2nd bucket.
 

Date (DATE, TIMESTAMP and subtypes)

    Oracle also uses the original data to create histogram:
 

SQL代码
  1. select substrb(dump(val, 16, 0, 32), 1, 120) ep, cnt   
  2.   from (select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */   
  3.          "B" val, count(*) cnt   
  4.           from "DEMO"."HTC3" t   
  5.          where "B" is not null  
  6.          group by "B")   
  7.  order by val   

    To store the data as endpoint value, it needs convert the date to number. The rule is converting the value to days, both date & time parts. To convert time parts to day, it will convert it to seconds then divide 86400(24*60*60), which is the number of seconds of one day. After that, the number should also be ROUND to the 15th position before be stored.
 

SQL代码
  1. HELLODBA.COM>select endpoint_number,   
  2.   2         endpoint_value   
  3.   3    from dba_histograms   
  4.   4   where owner = 'DEMO'  
  5.   5     and table_name = 'HTC3'  
  6.   6     and column_name = 'B';   
  7.   
  8.                          ENDPOINT_NUMBER                           ENDPOINT_VALUE   
  9. ---------------------------------------- ----------------------------------------   
  10.                                        1                         2455538.00001157   
  11.                                        2                         2455538.00002315   
  12.                                        3                         2455538.00003472   
  13.                                        4                          2455538.0000463   
  14.                                        5                         2455538.00005787   
  15.                                        6                          2455538.5347338   
  16.                                        7                         2455538.53478009   
  17.                                        8                         2455538.53540509   
  18.                                        9                         2455539.53474537   
  19.                                       10                         2455540.53475695   
  20.                                       11                         2455541.53476852   
  21.                                       12                          2455638.5347338   
  22.                                       13                          2455639.5347338   
  23.   
  24. 13 rows selected.   
  25.   
  26. HELLODBA.COM>alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';   
  27.   
  28. Session altered.   
  29.   
  30. HELLODBA.COM>select b, round(dn,15-length(trunc(dn))) from (select b, TO_NUMBER(TO_CHAR(b, 'J'))+TO_NUMBER(TO_CHAR(b, 'SSSSS'))/(24*60*60) dn from demo.htc3 order by b);   
  31.   
  32. B                             ROUND(DN,15-LENGTH(TRUNC(DN)))   
  33. ------------------- ----------------------------------------   
  34. 2010-12-07 00:00:01                         2455538.00001157   
  35. 2010-12-07 00:00:02                         2455538.00002315   
  36. 2010-12-07 00:00:03                         2455538.00003472   
  37. 2010-12-07 00:00:04                          2455538.0000463   
  38. 2010-12-07 00:00:05                         2455538.00005787   
  39. 2010-12-07 12:50:01                          2455538.5347338   
  40. 2010-12-07 12:50:05                         2455538.53478009   
  41. 2010-12-07 12:50:59                         2455538.53540509   
  42. 2010-12-08 12:50:02                         2455539.53474537   
  43. 2010-12-09 12:50:03                         2455540.53475694   
  44. 2010-12-10 12:50:04                         2455541.53476852   
  45. 2011-03-17 12:50:01                          2455638.5347338   
  46. 2011-03-18 12:50:01                          2455639.5347338   
  47.   
  48. 13 rows selected.   

RAW

    When grouping RAW data to histogram buckets, if the maximum size of data is larger than 32 bytes, oracle will substrb the left 32 bytes, otherwise will use the original data directly.
 

SQL代码
  1. select substrb(dump(val, 16, 0, 32), 1, 120) ep, cnt   
  2.   from (select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */   
  3.          substrb("C", 1, 32) val, count(*) cnt   
  4.           from "DEMO"."HTC3" t   
  5.          where substrb("C", 1, 32) is not null  
  6.          group by substrb("C", 1, 32))   
  7.  order by val   

    To be stored as endpoint value, the left 30 bytes will be converted to decimal from hex. If it less than 30 bytes, oracle will fill with '0' before converting.
 

SQL代码
  1. HELLODBA.COM>select endpoint_number,   
  2.   2         endpoint_value   
  3.   3    from dba_histograms   
  4.   4   where owner = 'DEMO'  
  5.   5     and table_name = 'HTC3'  
  6.   6     and column_name = 'C';   
  7.   
  8.                          ENDPOINT_NUMBER                           ENDPOINT_VALUE   
  9. ---------------------------------------- ----------------------------------------   
  10.                                        1       5192296858534830000000000000000000   
  11.                                        2      10384593717069700000000000000000000   
  12.                                        3      15576890575604500000000000000000000   
  13.                                        4      20769187434139300000000000000000000   
  14.                                        5      25961484292674100000000000000000000   
  15.                                        6      31153781151209000000000000000000000   
  16.                                        7      36346078009743800000000000000000000   
  17.                                        8      41538374868278600000000000000000000   
  18.                                        9      46730671726813500000000000000000000   
  19.                                       10      51922968585348300000000000000000000   
  20.                                       13     893448155939095000000000000000000000   
  21.   
  22. 11 rows selected.   
  23.   
  24. HELLODBA.COM>select c, round(rn, 15 - length(trunc(rn)))   
  25.   2    from (select c,   
  26.   3                 to_number(substrb(c || '' ||   
  27.   4                                   lpad('0', 30 - length(c || ''), '0'),   
  28.   5                                   0,   
  29.   6                                   30),   
  30.   7                           lpad('X', 30, 'X')) rn   
  31.   8            from demo.htc3)   
  32.   9   order by rn;   
  33.   
  34. C                                                                      ROUND(RN,15-LENGTH(TRUNC(RN)))   
  35. ---------------------------------------------------------------------- ------------------------------------------   
  36. 01                                                                     5192296858534830000000000000000000   
  37. 02                                                                     10384593717069700000000000000000000   
  38. 03                                                                     15576890575604500000000000000000000   
  39. 04                                                                     20769187434139300000000000000000000   
  40. 05                                                                     25961484292674100000000000000000000   
  41. 06                                                                     31153781151209000000000000000000000   
  42. 07                                                                     36346078009743800000000000000000000   
  43. 08                                                                     41538374868278600000000000000000000   
  44. 09                                                                     46730671726813400000000000000000000   
  45. 0A                                                                     51922968585348300000000000000000000   
  46. AC1265231212CDAC1265231212CDAC1265231212CDAC1265231212CDAC1265231212CD 893448155939095000000000000000000000   
  47. AC1265231212CDAC1265231212CDAC1265231212CDAC1265231212CDAC1265231212AB 893448155939095000000000000000000000   
  48. AC1265231212CDAC1265231212CDAC1265231212CDAC1265231212CDAC1265231212EF 893448155939095000000000000000000000   
  49.   
  50. 13 rows selected.   

Character Set (CHAR、VARCHAR2、NCHAR、NVARCHAR2)

    Just like RAW, if the maximum size larger than 32 bytes, it will substrb to 32 bytes, otherwise, use the original data to group the buckets. One thing needs to be noted is that it's sorted by binary.
 

SQL代码
  1. select substrb(dump(val, 16, 0, 32), 1, 120) ep, cnt   
  2.   from (select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */   
  3.          substrb("D", 1, 32) val, count(*) cnt   
  4.           from "DEMO"."HTC3" t   
  5.          where substrb("D", 1, 32) is not null  
  6.          group by substrb("D", 1, 32))   
  7.  order by nlssort(val, 'NLS_SORT = binary')   

    To be stored as endpoint value, it will be converted to RAW first, then follow the converting/storing rule of RAW.

SQL代码
  1. HELLODBA.COM>select endpoint_number,   
  2.   2         endpoint_value,   
  3.   3         endpoint_actual_value   
  4.   4    from dba_histograms   
  5.   5   where owner = 'DEMO'  
  6.   6     and table_name = 'HTC3'  
  7.   7     and column_name = 'D';   
  8.   
  9.                          ENDPOINT_NUMBER                           ENDPOINT_VALUE                                   ENDPOINT_ACTUAL_VALUE   
  10. ---------------------------------------- ---------------------------------------- -------------------------------------------------------   
  11.                                        1     337499295804764000000000000000000000 A   
  12.                                        2     344030231697140000000000000000000000 BB   
  13.                                        3     349248119252167000000000000000000000 CCC   
  14.                                        6     349248140068978000000000000000000000 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC   
  15.                                        7     354460798875655000000000000000000000 DDDDD   
  16.                                        8     359673457682976000000000000000000000 EEEEEE   
  17.                                        9     364886116489977000000000000000000000 FFFFFF1   
  18.                                       10     364886116489977000000000000000000000 FFFFFF2   
  19.                                       11     364886116489977000000000000000000000 FFFFFF3   
  20.                                       13     364886116489977000000000000000000000 FFFFFFF   
  21.   
  22. 10 rows selected.   
  23.   
  24. HELLODBA.COM>select d, round(cn, 15 - length(trunc(cn)))   
  25.   2    from (select d,   
  26.   3                 to_number(substrb(utl_raw.cast_to_raw(d) || '' ||   
  27.   4                                   lpad('0', 30 - length(utl_raw.cast_to_raw(d) || ''), '0'),   
  28.   5                                   0,   
  29.   6                                   30),   
  30.   7                           lpad('X', 30, 'X')) cn   
  31.   8            from demo.htc3)   
  32.   9   order by cn;   
  33.   
  34. D                                                                                     ROUND(CN,15-LENGTH(TRUNC(CN)))   
  35. ------------------------------------------------------------------------------------- ----------------------------------------   
  36. A                                                                                     337499295804764000000000000000000000   
  37. BB                                                                                    344030231697140000000000000000000000   
  38. CCC                                                                                   349248119252167000000000000000000000   
  39. CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCA1                                                  349248140068978000000000000000000000   
  40. CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCA2                                                  349248140068978000000000000000000000   
  41. CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCA                                                   349248140068978000000000000000000000   
  42. DDDDD                                                                                 354460798875655000000000000000000000   
  43. EEEEEE                                                                                359673457682976000000000000000000000   
  44. FFFFFF1                                                                               364886116489977000000000000000000000   
  45. FFFFFF2                                                                               364886116489977000000000000000000000   
  46. FFFFFF3                                                                               364886116489977000000000000000000000   
  47. FFFFFFF                                                                               364886116489977000000000000000000000   
  48. FFFFFFF                                                                               364886116489977000000000000000000000   
  49.   
  50. 13 rows selected.   

    One thing should be payed attention to. After data be ROUND to left 15th position, the precision degraded much. If we convert it back to character, we will found the finally data just be kept at most 6~7 characters (rely on charater set) of original data.
 

SQL代码
  1. HELLODBA.COM>select utl_raw.cast_to_varchar2(trim(to_char(ev,lpad('X', 30, 'X'))))   
  2.   2  from  
  3.   3  (select d, round(cn, 15 - length(trunc(cn))) ev   
  4.   4    from (select d,   
  5.   5                 to_number(substrb(utl_raw.cast_to_raw(d) || '' ||   
  6.   6                                   lpad('0', 30 - length(utl_raw.cast_to_raw(d) || ''), '0'),   
  7.   7                                   0,   
  8.   8                                   30),   
  9.   9                           lpad('X', 30, 'X')) cn   
  10.  10            from demo.htc3)   
  11.  11   order by cn);   
  12.   
  13. UTL_RAW.CAST_TO_VARCHAR2(TRIM(TO_CHAR(EV,LPAD('X',30,'X'))))   
  14. ---------------------------------------------------------------------------   
  15. A      靠   
  16. BB    靠   
  17. CCC   `靠`   
  18. CCCCCCH靠   
  19. CCCCCCH靠   
  20. CCCCCCH靠   
  21. DDDDC靠?`   
  22. EEEEEEA靠   
  23. FFFFFFKZ靠   
  24. FFFFFFKZ靠   
  25. FFFFFFKZ靠   
  26. FFFFFFKZ靠   
  27. FFFFFFKZ靠   
  28.   
  29. 13 rows selected.   

    Such precision will increase the possibility of duplicated buckets, means will increase the possibility of optimizer estimating to the wrong result. To reduce such risk, when finally store the histogram, oracle will store the original data into histgrm$.EPVALUE as endpoint actual value if it found duplicated buckets. When optimizer estimating cost, if the prediction value matched the duplicated buckets, it will refer to the endpoint actual value to identity the real bucket.
 

    Note: Just character data may has the endpoint actual value.
 

ROWID

    Oracle also use the original ROWID data to group the histogram buckets. It will also convert it to RAW data and follow the convertiong/storing rules of RAW data to store it as endpoint value. We understand how to convert ROWID to RAW, we need understand its construct first. The 1st~4th bytes are the Data Object Number; the 5th, 6th bytes are the Data File Number in 64 hex; the 7th, 8th bytes are the Data Block Number; the 9th, 10th bytes are the Record Number.
 

SQL代码
  1. HELLODBA.COM>select endpoint_number,   
  2.   2         endpoint_value   
  3.   3    from dba_histograms   
  4.   4   where owner = 'DEMO'  
  5.   5     and table_name = 'HTC3'  
  6.   6     and column_name = 'F';   
  7.   
  8.                          ENDPOINT_NUMBER                           ENDPOINT_VALUE   
  9. ---------------------------------------- ----------------------------------------   
  10.                                        1         62696712745274800000000000000000   
  11.                                        2         62696712745274800000000000000000   
  12.                                        3         62696712745274800000000000000000   
  13.                                        4         62696712745274800000000000000000   
  14.                                        5         62696712745274800000000000000000   
  15.                                        6         62696712745274800000000000000000   
  16.                                        7         62696712745274800000000000000000   
  17.                                        8         62696712745274800000000000000000   
  18.                                        9         62696712745274800000000000000000   
  19.                                       10         62696712745274800000000000000000   
  20.                                       11         62696712745274800000000000000000   
  21.                                       12         62696712745274800000000000000000   
  22.                                       13         62696712745274800000000000000000   
  23.   
  24. 13 rows selected.   
  25.   
  26. HELLODBA.COM>select f, round(rn, 15 - length(trunc(rn))) ev   
  27.   2    from (select f,   
  28.   3                 to_number(substrb(fs || '' || lpad('0', 10, '0'), 0, 30),   
  29.   4                           lpad('X', 30, 'X')) rn   
  30.   5            from (select f,   
  31.   6                         lpad(trim(to_char(dbms_rowid.rowid_object(f), 'XXXXXX')), 8, '0') ||   
  32.   7                         lpad(trim(to_char(dbms_rowid.rowid_relative_fno(f) * 64, 'XXXX')), 4, '0') ||   
  33.   8                         lpad(trim(to_char(dbms_rowid.rowid_block_number(f), 'XXXX')), 4, '0') ||   
  34.   9                         lpad(trim(to_char(dbms_rowid.rowid_row_number(f), 'XXXX')), 4, '0') fs   
  35.  10                    from demo.htc3))   
  36.  11   order by rn;   
  37.   
  38. F                                                        EV   
  39. ------------------ ----------------------------------------   
  40. AAAxdYAAFAAAPJUAAA         62696712745274800000000000000000   
  41. AAAxdYAAFAAAPJUAAB         62696712745274800000000000000000   
  42. AAAxdYAAFAAAPJUAAC         62696712745274800000000000000000   
  43. AAAxdYAAFAAAPJUAAD         62696712745274800000000000000000   
  44. AAAxdYAAFAAAPJUAAE         62696712745274800000000000000000   
  45. AAAxdYAAFAAAPJUAAF         62696712745274800000000000000000   
  46. AAAxdYAAFAAAPJUAAG         62696712745274800000000000000000   
  47. AAAxdYAAFAAAPJUAAH         62696712745274800000000000000000   
  48. AAAxdYAAFAAAPJUAAI         62696712745274800000000000000000   
  49. AAAxdYAAFAAAPJUAAJ         62696712745274800000000000000000   
  50. AAAxdYAAFAAAPJUAAK         62696712745274800000000000000000   
  51. AAAxdYAAFAAAPJUAAL         62696712745274800000000000000000   
  52. AAAxdYAAFAAAPJUAAM         62696712745274800000000000000000   
  53.   
  54. 13 rows selected.   

Other Data Types

    For BLOB, CLOB, BFILE, CFILE, LONG, LONG RAW columns, oracle will not create histogram.
 

        --- Fuyuncat TBC ---

Top

Copyright ©2005, HelloDBA.Com All reseverd.

Declaration
by fuyuncat