select bureau_no,count(*) from
(
select distinct bureau_no,ord_no from tom_sheet_manage where
crt_sheet_date > to_date('2009-03-25 00:00:00','YYYY-MM-DD hh24:mi:ss')
and crt_sheet_date < to_date('2009-03-26 00:00:00','YYYY-MM-DD hh24:mi:ss')
and bureau_no like '00171%'
and sheet_type in ('J','J1') and sheet_state = '1' and seq = 0
and ord_no not in
(select ord_no from tba_ask_list where serv_no like '2%')
union
select distinct bureau_no,ord_no from tom_sheet_manage_his where
crt_sheet_date > to_date('2009-03-25 00:00:00','YYYY-MM-DD hh24:mi:ss')
and crt_sheet_date < to_date('2009-03-26 00:00:00','YYYY-MM-DD hh24:mi:ss')
and bureau_no like '00171%'
and sheet_type in ('J','J1') and sheet_state = '1' and seq = 0
and ord_no not in
(select ord_no from tba_ask_list where serv_no like '2%')
) group by bureau_no;直接写时间非常快,最多几秒能得出结果,但是如果改成:
select bureau_no,count(*) from
(
select distinct bureau_no,ord_no from tom_sheet_manage where
crt_sheet_date > sysdate-1
and crt_sheet_date <sysdate and bureau_no like '00171%'
and sheet_type in ('J','J1') and sheet_state = '1' and seq = 0
and ord_no not in
(select ord_no from tba_ask_list where serv_no like '2%')
union
select distinct bureau_no,ord_no from tom_sheet_manage_his where
crt_sheet_date > sysdate-1
and crt_sheet_date < sysdate and bureau_no like '00171%'
and sheet_type in ('J','J1') and sheet_state = '1' and seq = 0
and ord_no not in
(select ord_no from tba_ask_list where serv_no like '2%')
) group by bureau_no只不过把一个具体的时间值to_date('2009-03-26 00:00:00','YYYY-MM-DD hh24:mi:ss')改成了Sysdate,结果这样的语句要执行超过三十分钟可能才有结果!
哪位高人能解释下为什么?
哪位高人能解释下为什么?
改sysdate 因為oracle需要多浪費CPU COST來計算和取得
舉例來說
a)1+1+1*3=5
b)5
c)2+3上面三種COST大到小 a>c>b
我经常用SYSDATE的
到to_date('2009-03-26 00:00:00','YYYY-MM-DD hh24:mi:ss')
的时间,
和
从sysdate-1
到sysdate
的时间不是一样的,sysdate-1到sysdate可能是2009-03-24 20:00:00到2009-03-25 20:00:00,
如果2009-03-24 20:00:00到2009-03-24 23:59:59有大量数据,
而2009-03-25 00:00:00到2009-03-25 23:59:59有较少的数据,
执行时间自然有很大的差异了.很好理解.
到to_date('2009-03-26 00:00:00','YYYY-MM-DD hh24:mi:ss')
或者说我用Sysdate表达的时间段肯定是和上面直接写出的时间段是非常接近的,统计得到的结果也基本一致,但是执行效率差别极大。这一点可能我没表达清楚。
使用sysdate得到的奇怪结果区 间
基数 (9.2.0.6) (12:00 p.m.)
a) Sysdate and sysdate + 1
144
b) Trunc(sysdate) and trunc(sysdate) + 1
180
c) Sysdate - 1 and sysdate
180
d) Trunc(sysdate) - 1 and trunc(sysdate)
144
e) Sysdate - 1 and sysdate + 1
16
f) Trunc(sydate) - 1 and trunc(sysdate) + 1
16
这些结果非常不准确;此外,它们甚至都不是自相容的;例如,我们认为(e)的基数应该是(a)的基数和(c)的基数之和,但显示的结果却并非如此。原因很简单,优化器将sysdate(包括trunc(sysdate)及一些其他函数)看作在分析时已知的常量,但是sysdate+N就成为了未知数值,因此将其看作有界变量进行处理—— 即固定为5%的基数(尤其需要注意的是,sysdate+0和sysdate下得到的基数不相同)。接下来,需要对sysdate+N进行分析,针对这种情况,优化器同样可以将between子句转换为两个独立的谓词,因此,inutes between sysdate–1 and sysdate + 1就可以转换为minutes >= :bind1and minutes <= :bind2因此,针对这两个谓词肯定为0.25%的选择率,也就是上表中(e)和(f)两种情况下的取值。为了进一步进行演示,接下来对(a)下的算法进行分析,即where minutes >= sysdateand minutes <= {unknown bind value}表中存储了4.5天的分钟数,其中有两天指的是未来的日期(当然,前提是在正午运行脚本),因此,第一个谓词的选择率是(半闭区间):(required range) / (total range) + 1 / number of distinct values =(2 * 1440) / (4.5 * 1440) + 1/6480 =0.4445987654321● 第二个谓词的选择率是0.05(作为一个绑定变量)。● 联合谓词的选择率为0.05 * 0.4445987654321 = 0.02222994。● 因此基数为0.02222994 * 6480 = 144.05—— 这与演示的结果是一致的。因此,即使我们按照正确的格式存储了日期,优化器仍然有可能采用了错误的算法来计算选择率和基数。类似于 “all of last week”和“the last 24 hours”等的查询是最为常用的日期查询形式。在这一点上,我们认为使用字面字符串来进行查询而不是绑定变量或者诸如trunc(sysdate) – 7等表达式要好得多。升级到Oracle 10g时,由于该问题已经被发现并且有了解决方案,因此表6-5中的示例都可以得到恰当的结果。这个正在被修正的问题其影响究竟有多么不好?我们首先看一下由于优化器通过10(例如(b)和(c))或者80(例如(e)和(f))的因子来计算基数,因此有多少代码能够出现这样的问题?如果优化器获得了正确的基数,这些代码将返回什么样的结果?有多少连接顺序需要反转?有多少索引访问路径需要变为表扫描?有多少嵌套循环将变为散列连接(可能还会中途停止分区)?因此,修复优化器的bug需要深思熟虑。