oracle: date类型在10053 trace中的min/max值代表什么

最新推荐文章于 2024-06-03 09:06:41 发布

原创最新推荐文章于 2024-06-03 09:06:41 发布 · 257 阅读

0 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

11 篇文章

订阅专栏

使用以下查询可得julian日以来的天：

SQL> SELECT TO_CHAR(TO_DATE('08-28-2012', 'MM-DD-YYYY'),'J') from dual;

TO_CHAR
-------
2456167 ---与10053事件的trace中min对应值一致

SQL> select TO_CHAR(TO_DATE('BC4712010100:00', 'BCYYYYMMDDHH24:MI'),'J') from dual;

TO_CHAR
-------
0000001

Note: Oracle Database uses the astronomical system of calculating
Julian days儒略日, in which the year 4713 BC is specified as -4712. The
historical system of calculating Julian days, in contrast, specifies 4713
BC as -4713. If you are comparing Oracle Julian days with values
calculated using the historical system, then take care to allow for the
365-day difference in BC dates. For more information, see
http://aa.usno.navy.mil/faq/docs/millennium.html.

测试：

truncate table test;
insert into test(created) values(to_date('20120828 00:00:00','YYYYMMDD HH24:MI:SS'));
insert into test(created) values(to_date('20120827 00:00:00','YYYYMMDD HH24:MI:SS'));
commit;
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','TEST');
SELECT * FROM DBA_tab_col_STATISTICS WHERE OWNER='SYS' AND TABLE_NAME='TEST' and column_name='CREATED';

10053 trace内容：

SINGLE TABLE ACCESS PATH
  Column (#7): CREATED(DATE)
    AvgLen: 8.00 NDV: 2 Nulls: 0 Density: 0.5 Min: 2456167 Max: 2456168
  Table: TEST  Alias: TEST     
    Card: Original: 2  Rounded: 2  Computed: 2.00  Non Adjusted: 2.00
  Access Path: TableScan
    Cost:  2.00  Resp: 2.00  Degree: 0
      Cost_io: 2.00  Cost_cpu: 8661
      Resp_io: 2.00  Resp_cpu: 8661
  Best:: AccessPath: TableScan
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 2.00  Bytes: 0

SQL> SELECT TO_CHAR(TO_DATE('08-27-2012', 'MM-DD-YYYY'),'J') from dual;

TO_CHAR
-------
2456167