Oracle不建议使用varchar2来存时间数据，实际案例说明

最新推荐文章于 2024-07-01 08:30:00 发布
最新推荐文章于 2024-07-01 08:30:00 发布
阅读量4.1k 收藏 4
点赞数 2
分类专栏： Oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/luis_ora/article/details/82106685
版权

问题现象
2015年9月客户系统中一条高逻辑读的SQL语句，在业务高峰期执行频率较高，导致系统逻辑读居高不下，同时带高了系统CPU，SQL语句主体部分如下

SELECT /* ^^*/
COUNT(DISTINCT ts_map.draftid) AS recordCount
FROM usr.BillStateMap ts_map
INNER JOIN usr.create ts_create
ON ts_create.draftid = ts_map.draftid
LEFT JOIN usr.search ts_search
ON ts_create.draftid = ts_search.draftid
LEFT JOIN usr.accept accept
ON ts_create.draftid = accept.draftid
LEFT JOIN usr.kfvistirecord ts_kfback
ON ts_kfback.draftid = ts_create.draftid
LEFT JOIN (SELECT DISTINCT .. .. .. . FROM usr.create_user t_user) ts_user
ON ts_create.draftid = ts_user.creatinfoid
WHERE 1 = 1
AND (ts_create.location = '0579' OR ts_user.location = '0579')
AND ts_create.CREATETIME >= '2015-06-23 00:00:00'
AND ts_create.CREATETIME <= '2015-09-21 23:59:59'
AND ts_map.billstate = '待报结'
ORDER BY ts_create.draftId DESC;

通过SQL语句的过滤谓词来确定SQL的过滤情况

通过执行计划可以看出SQL语句走的驱动表是usr.create，但通过过滤谓词检查的结果可以看出实际驱动表应该是usr.BillStateMap
进一步检查SQL语句相关表的索引(检查统计信息是最新收集的，排除统计信息不准的问题)

原因分析：
在此我们回顾下Oracle执行计划走NESTED LOOPS时的一些条件
1、过滤后的小表作为驱动表，大表作为被驱动表，表的大小由CBO评估后计算得到的。
2、被驱动表关联列需要有索引，这条SQL的关联列是DRAFTID，
通过上述索引的查询可以排除第二个条件，两个表上关联列上均有索引，所以当前SQL语句走错驱动表应该是CBO计算过滤后返回行出现错误导致的
检查BILLSTATEMAP表的BILLSTATE是否存在直方图

SQL> SELECT OWNER, TABLE_NAME, COLUMN_NAME, HISTOGRAM
  2    FROM DBA_TAB_COLUMNS
  3   WHERE TABLE_NAME = 'BILLSTATEMAP'
  4     AND HISTOGRAM <> 'NONE';
OWNER                TABLE_NAME                     COLUMN_NAME                    HISTOGRAM
-------------------- ------------------------------ ------------------------------ ---------------
NETFORCE             BILLSTATEMAP                   BILLSTATE                      FREQUENCY

可以看出虽然BILLSTATE字段上存在数据选择性不佳，切存在数据倾斜，但是该字段上存在直方图，并不会导致CBO在计算谓词过滤的时候出现错误，所以最终焦点确定在了create表上，为什么实际上create表过滤后有358760行，CBO任然选择他作为驱动表？
作为CBO的正常行为，通过计算后确定过滤后的行数少，即可作为驱动表， 基于成本计算来确定驱动表和被驱动表。 尝试使用hint来指定SQL语句的驱动表/*+ use_nl(ts_map,ts_create) leading(ts_map) */，得到执行计划如下

可以看出SQL的执行计划比较完美了，逻辑读大幅下降，再次看下create表在内存中的执行计划，可以发现E-Rows和A-Rows相差好几个数量级， E-Rows：代表着CBO评估的返回行数 A-Rows：代表SQL语句实际返回的行数

SQL> SELECT COUNT(*)
  2    FROM CREATE ts_create
  3   WHERE ts_create.CREATETIME >= '2015-06-23 00:00:00'
  4     AND ts_create.CREATETIME <= '2015-09-21 23:59:59';
 
  COUNT(*)
----------
    358760
 
Elapsed: 00:00:00.31
-------------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |      1 |        |      1 |00:00:00.31 |   19571 |
|   1 |  SORT AGGREGATE    |            |      1 |      1 |      1 |00:00:00.31 |   19571 |
|*  2 |   TABLE ACCESS FULL| TAB_CREATE |      1 |      1 |    358K|00:00:00.25 |   19571 | 
-------------------------------------------------------------------------------------------

可以确定SQL语句走错驱动表根本原因是CBO评估出错误的返回行，导致驱动表走错，但是为什么会计算错误？ 回顾下CBO在计算选择率的公式如下，在没有直方图统计信息足够新的情况下

"col BETWEEN val1 AND val2"的选择率计算如下
Sel = ((val2 - val1) / (high_value - low_value) + (2 / NDV)) * A4Nulls
注:
high_value  代表过滤列col的最大值
low_value   代表过滤列col的最小值
NDV   代表数据列的非重复值数量，即distinct_key
A4Nulls   代表该字段的非空率

CBO的rows是通过选择率计算出来的，所以选择率的计算直接影响着rows的结果
在统计信息准确的情况下，NDV、A4Nulls、high_value、low_value都是定值并且认为是准确的，在此条件下影响选择率的计算就只有val1和val2(即上述业务SQL在ts_create.CREATETIME上的过滤谓词)
AND ts_create.CREATETIME >= '2015-06-23 00:00:00'
AND ts_create.CREATETIME <= '2015-09-21 23:59:59'

在此回头查看SQL语句执行计划Predicate Information部分可以看出ts_create.CREATETIME字段上没有进行to_date隐式转换，也就是说ts_create.CREATETIME字段本身就是varchar2类型的，是否是由于varchar2类型的数据进行大小判断导致的呢？ 做了如下实验： 创建相同数据的两张表TAB_CREATE1和TAB_CREATE2，字段CREATETIME数据类型分别为varchar2和date，相关表查询通过CREATETIME字段过滤后的执行计划如下

SQL> SELECT COUNT(*)
  2    FROM TAB_CREATE1 ts_create
  3   WHERE ts_create.CREATETIME >= '2015-06-23 00:00:00'
  4     AND ts_create.CREATETIME <= '2015-09-21 23:59:59';
 
  COUNT(*)
----------
    358760
-------varchar2类型的存放时间的情况下E-Rows和A-Rows相差好几个数量级
Elapsed: 00:00:00.31
-------------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |      1 |        |      1 |00:00:00.31 |   19571 |
|   1 |  SORT AGGREGATE    |            |      1 |      1 |      1 |00:00:00.31 |   19571 |
|*  2 |   TABLE ACCESS FULL| TAB_CREATE1|      1 |      1 |    358K|00:00:00.25 |   19571 | 
-------------------------------------------------------------------------------------------
---date类型的存放时间的情况下E-Rows和A-Rows 在一个数量级了
-------------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |      1 |        |      1 |00:00:00.27 |   19571 |
|   1 |  SORT AGGREGATE    |            |      1 |      1 |      1 |00:00:00.27 |   19571 |
|*  2 |   TABLE ACCESS FULL| TAB_CREATE2|      1 |    156K|    358K|00:00:00.22 |   19571 |
-------------------------------------------------------------------------------------------

到这里问题就进一步明确了，是由于用varchar2类型存放数据导致的问题。 那么又有疑问了，Oracle在CBO是如何处理字符串大小比较的呢？其实没有那么复杂，CBO会先将varchar2数据转换统一转换为raw数据后再做大小比较
继续进行实验，生成测试数据

-----创建个测试表
create table tab_yong as 
select rownum as id,
               to_char(to_date('2015-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') + (rownum*133)/24/3600, 'yyyy-mm-dd hh24:mi:ss') as c_date,
               (to_date('2015-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') + (rownum*133)/24/3600) as d_date,
               dbms_random.string('x', 20) random_string
          from dual
        connect by level <= 1500000;
-----收集表的统计信息
SQL> DECLARE
  2  BEGIN
  3    DBMS_STATS.GATHER_TABLE_STATS(ownname => 'YONG',
  4                                  tabname => 'TAB_YONG',
  5                                  estimate_percent => 100,
  6                                  method_opt =>'for all columns size repeat',                                                    
  7                                  no_invalidate => FALSE, cascade => TRUE,
  8                                  degree => 16);
  9  END;
 10  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:15.07

测试表统计信息如下

使用varchar2 字段的SQL语句执行计划

SQL> select *
  2  from   TAB_YONG b
  3  where  b.c_date >= '2015-06-23 00:00:00'
  4  and    b.c_date <= '2015-09-21 23:59:59';
59116 rows selected.
Elapsed: 00:00:00.53
Execution Plan
----------------------------------------------------------
Plan hash value: 4254277647
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    54 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB_YONG  |     1 |    54 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_CDATE |     1 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."C_DATE">='2015-06-23 00:00:00' AND "B"."C_DATE"<='2015-09-21
              23:59:59')

使用date类型的SQL语句执行计划

SQL> select *
  2  from   TAB_YONG b
  3  where  b.d_date >= TO_DATE('2015-06-23 00:00:00')
  4  and    b.d_date <= TO_DATE('2015-09-21 23:59:59');
59116 rows selected.
Elapsed: 00:00:00.53
Execution Plan
----------------------------------------------------------
Plan hash value: 1934174936
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           | 59118 |  3117K|   655   (1)| 00:00:08 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB_YONG  | 59118 |  3117K|   655   (1)| 00:00:08 |
|*  2 |   INDEX RANGE SCAN          | IDX_DDATE | 59118 |       |   159   (0)| 00:00:02 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."D_DATE">=TO_DATE(' 2015-06-23 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "B"."D_DATE"<=TO_DATE(' 2015-09-21 23:59:59', 'syyyy-mm-dd
              hh24:mi:ss'))

通过上述执行计划可以看出来，使用varchar2类型的CBO评估出来的rows是1行，而date评估出来的行数是59118行，与实际的59116行一致
转换也没有想象中的复杂，通过下面的函数就可以进行varchar2转换到raw，具体函数代码详见本文结尾
通过上述函数我们计算下问题SQL的时间过滤谓词值

SQL> col var_raw for a40
SQL> select get_internal_value('2015-06-23 00:00:00') var_raw from dual;
VAR_RAW
----------------------------------------
260592297225015000000000000000000000
SQL> col var_raw for a40
SQL> select get_internal_value('2015-09-21 23:59:59') var_raw from dual;
VAR_RAW
----------------------------------------
260592297225015000000000000000000000

##计算结果可以看出两个值是相等的，也就是说CBO通过计算后认为下列的条件是等价的
AND    ts_create.CREATETIME >= '2015-06-23 00:00:00'
AND    ts_create.CREATETIME <= '2015-09-21 23:59:59'
CBO层面等价于下列条件
AND ts_create.CREATETIME = '2015-09-21 23:59:59'

而等值条件的选择率计算公式如下：
Sel = (1 / NDV) * A4Nulls

带入统计信息分别计算下两个选择率的值及E-rows
----等值情况下
Sel = (1 / NDV) * A4Nulls  =(1/1500000) * (1500000-0)/1500000=1/1500000
rows=Sel * NumRows=1/1500000 * 1500000=1
注:
NumRows =dba_tables.num_rows 表示全表的行数

-----实际between情况下
Sel = ((val2 - val1) / (high_value - low_value) + (2 / NDV)) * A4Nulls
    =((to_date('2015-09-21 23:59:59', 'yyyy-mm-dd hh24:mi:ss') -
       to_date('2015-06-23 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) /
       (to_date('2021-04-28 00:40:00', 'yyyy-mm-dd hh24:mi:ss') -
       to_date('2015-01-01 00:02:13', 'yyyy-mm-dd hh24:mi:ss')) +
       (2 / 1500000)) * 1
       =0.0394118809102899
rows=Sel * NumRows=0.0394118809102899 * 1500000=59117.8213654348≈59118    与上述执行计中CBO计算的一致

OK，到此问题就明朗了，Oracle在CBO计算varchar比较关系的时候会将varchar数据通过计算得出raw值，通过raw来进行比较计算，并依此来计算选择率。

 

 

Liu Yong
关注
专栏目录
oracle中往varchar2格式的字段中插入date时间格式的值会发生什么？
你若强大，来者不怕
07-03 3751
--建立表test1 create table TEST1 (   ID    VARCHAR2(40) default sys_guid(),   TDATE VARCHAR2(200) ) tablespace APP_TX_DATA   pctfree 10   pctused 40   initrans 1   maxtrans 255   storage   (
oracle导入dmp文件数据不全,Oracle dmp文件损坏恢复案例
weixin_26934905的博客
04-06 1417
前一段时间帮一个朋友的朋友恢复了一个损坏的dmp文件，大概100多个G，记录一下恢复过程并简单总结一下一、描述这个dmp文件是从一个Oracle 9i的数据库上exp出来的，在导入Oracle 11g版本的时候，可能会随机出现两类错误，如下(1)dmp文件导入的时候，一直停留在某张表上不动，两三天都是这样，导入操作无法进行，如下导入了 0 行. . 正在导入表 "D_xxxxxx". . 正在导入...
Oracle 日期数据保存在 varchar,Date 类型字段中的处理
weixin_30367945的博客
12-29 290
表: ZZJCBGD 字段：TianKaSJ 日期型 JiuZhenSJ varchar型 获取年份： select to_char(TianKaSJ,'YYYY') From ZZjcbgd; select to_char(to_date(JiuZhenSJ,'YYYY-MM-DD hh24:mi:ss'),'YYYY') From zzjcbgd; 转载...
Oracle、MySQL、PostGreSQL、SQL Server- VARCHAR与VARCHAR2
最新发布
weixin_tank88921的博客
07-01 687
Oracle 数据库中的 varchar 和 varchar2 数据类型都用于存储字母数字值的动态长度，也是最常用的字段数据类型。它们之间存在一些差异。varchar 数据类型是适用于所有关系数据库产品（Oracle、MySQL、PostgreSQL 和等）的 ANSI 标准数据类型，存储长度不同数据库差异较大。
oracle 使用 date timestamp 而不使用varchar2()的好处
weixin_34419321的博客
10-17 443
oracle数据库，表示日期数据基本是date型，只有年月的用varchar2或者char，好处见下： 1、数据规范。date对合法日期型会校验，包括闰年2月这种。避免字符型变量产生的某月32号，日期长度不对，日期格式不统一等问题。曾经有过痛苦经历，面对字符型的2013111这种数据不知道是11月1日还是1月11日，只想说日… 2、检索效率。如果你经常...
Oracle数据库中为什么不全用VARCHAR2类型
weixin_57413199的博客
12-16 960
1.VARCHAR2虽然比CHAR节省空间，但是假如一个VARCHAR2列经常被修改，而且每次 被修改的数据的长度不同，这会引起‘行迁移’(RowMigration)现象，而这会造成多 余的I/O，是数据库设计和调整中要尽力避免的，在这种情况下用CHAR代替VARCHAR2会 更好一些； 2.还有就是当我们存储已知固定长度的数据时，比如：手机号（11位）、身 份证号码（18位）等，可以考虑使用 char。因为，在查询数据时，对于 char 类型 字段，是全字符整体匹配；而 varchar2 是一个字符一个.
【工作问题】Orcale数据库中保存时间到底用varchar还是Date？
ccystewart的博客
03-02 1559
【工作问题】数据库中保存时间到底用varchar还是Date？ 近日工作中和DBA（姑且算是）发生了争论。起因是我建数据库的时候时间字段全部设为了Date类型，但DBA坚持要使用varchar（string）类型来保存时间。 那么到底谁对呢？来看下对比 Date类型存时间的好处如下： 存储空间：Date类型只占用7个字节 索引效率 函数运算：比较，排序 插入或更新数据时，Date可设为默认插入或...
oracle中varchar2（byte）和varchar2（char）.doc
10-28
本文将深入探讨`VARCHAR2(BYTE)`与`VARCHAR2(CHAR)`之间的差异，并通过具体的例子来说明它们在不同场景下的应用。 #### 二、概念解析 ##### 1. VARCHAR2(BYTE) - **定义**: `VARCHAR2(n BYTE)`定义了一个变长的字符...
润乾报表调用oracle存储过程案例说明
03-15
在实际的业务场景中，有时我们需要调用数据库中的存储过程来获取或处理数据，本篇将详细介绍如何在润乾报表中调用Oracle存储过程。 首先，我们要在Oracle数据库中创建存储过程。以下是一个简单的存储过程示例，名为...
120套Oracle经典案例库（命令表）.pdf
05-26
根据给定文件的标题、描述、标签和部分内容，我们将详细阐述其中包含的...这些案例不仅有助于学习Oracle SQL的基础知识，而且能够帮助DBA（数据库管理员）和开发者熟悉Oracle数据库的实际应用，提高解决问题的能力。
Oracle 的 PL-SQL使用案例详解.rar
02-26
在PL/SQL中，你可以声明并初始化各种数据类型的变量，如NUMBER、VARCHAR2、DATE等。理解每种数据类型的用途和限制是编写有效PL/SQL代码的基础。 3. **流程控制** PL/SQL支持IF-THEN-ELSE、CASE语句进行条件判断，...
数据库中存储日期的字段类型到底应该用varchar还是datetime
u014520797的专栏
06-30 4328
数据库中存储日期的字段类型到底应该用varchar还是datetime ？这两种方法各有优势，datetime可以借用sql函数库中运算函数，增加了时间在各种运算上的效率；而varchar类型则可以在字符编码上显出优势。在 存储的时间将来不需要进行大量计算 的前提下，可以考虑选择varchar类型，反之，选择datetime类型。
oracle获取日期中文,oracle检索中文日期（varchar2）方法
weixin_42545066的博客
04-06 1445
select *　from tblNamewhere to_date(replace(replace(replace(columnName,'年','-'),'月','-'),'日',''),'yyyy-mm-dd hh24:mi:ss') > to_date('2011-04-20','yyyy-mm-dd')用途：可以方便的查找表中某一时间范围内的记录，其中tblName可以是任何表，c...
为什么日期不建议使用VARCHAR2或者NUMBER？
bisal的专栏
05-06 666
通常在数据库表设计时，不建议将日期类型的字段定义为VARCHAR2或者NUMBER类型，语义是其中一方面的原因，从使用层面，还是有其他原因。若定义为VARCHAR2类型，模拟如下，SQL&...
格式化Oracle中的varchar类型的时间为时间类型
itmyhome的专栏
04-26 3653
OILDATE为varchar类型，欲将 20141130120140 格式化为 2014-11-30 12:01:40 select to_char(to_date(OILDATE, 'YYYYMMDD HH24MISS'), 'YYYY-MM-DD HH:MM:SS') AS OILDATE FROM PS_NYGK_OIL_GAS 先转换成date类型，然后再转换为varchar类型，必须...
oracle检索中文日期（varchar2）方法
folksdreamY的专栏
01-06 2892
select *　from tblName where to_date(replace(replace(replace(columnName,'年','-'),'月','-'),'日',''),'yyyy-mm-dd hh24:mi:ss') > to_date('2011-04-20','yyyy-mm-dd') 用途：可以方便的查找表中某一时间范围内的记录，其中tblName可以是任何表，c
oracle中时间varchar,oracle中往varchar2格式的字段中插入date时间格式的值会发生什么？...
weixin_34649252的博客
04-05 393
--建立表test1create table TEST1(ID VARCHAR2(40) default sys_guid(),TDATE VARCHAR2(200))tablespace APP_TX_DATApctfree 10pctused 40initrans 1maxtrans 255storage(initial 64Kminextents 1maxextents unlimit...
Oracle中Varchar 型数据 计算时间差
cjwxd126715的专栏
02-24 2122
   在开发程序时,我们经常会遇到要计算从startTime到endTime的时间差,但这个记录时间差的字段是Varchar型的,这就需要先进行数据类型的转换才能达到目的:      先来看看ORACLE系统时间的时间差计算方法: SELECT     SYSDATE,SYSDATE-500,     TO_CHAR(SYSDATE,'yyyy')-     TO_CHAR(SYSDATE-50...
Oracle中Date与TIMESTAMP在实际应用中的坑
wo2han123456的博客
03-24 1133
最近提数据的时候遇到了一个问题，日志表中有一个响应时间，需求是提取2022年1-3月的相应日志，那么我就根据这个响应时间去提取，例如响应时间是apply_time字段，那么我使apply_time > to_char('2022','yyyy')，逻辑上这样是可以的，根据多次测试，to_cahr 是一个字符串类型，是无法比大小的，而apply_time > to_date('2022','yyyy') ,在实际提取中，只能够提取到三月份，目前时间为2022年3月，应该是在2022...
Oracle数据库练习：创建表与数据插入示例
对于学习者来说，这个练习可以帮助他们熟悉Oracle数据库的基本操作，包括表的创建、数据结构设计、数据记录以及SQL语句的使用。此外，它还涵盖了日期时间处理，如TO_DATE函数在指定格式下将字符串转换为日期类型。 ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值