小男孩‘自慰网亚洲一区二区,亚洲一级在线播放毛片,亚洲中文字幕av每天更新,黄aⅴ永久免费无码,91成人午夜在线精品,色网站免费在线观看,亚洲欧洲wwwww在线观看

搜索
分享

【原】奇奇怪怪的ORA-01841錯(cuò)誤，分析處理過(guò)程（全）

 數(shù)據(jù)和云 2021-01-08

墨墨導(dǎo)讀：由于執(zhí)行計(jì)劃中，對(duì)過(guò)濾謂詞順序的改變，導(dǎo)致SQL運(yùn)行報(bào)錯(cuò)。

最近，遇到了一個(gè)關(guān)于ORA-01841的報(bào)錯(cuò)，起初，認(rèn)為這個(gè)錯(cuò)誤處理起來(lái)應(yīng)該不困難，但實(shí)際上折騰了很久，才最終找到問(wèn)題原因，并解決掉，下面將本次解決和分析的過(guò)程用樣例來(lái)說(shuō)明。

ORA-01841的錯(cuò)誤提示是“(full) year must be between -4713 and +9999, and not be 0”，翻譯過(guò)來(lái)，大意是完整的年份值需在-4712到+9999之間，并且不得為0。出現(xiàn)這個(gè)錯(cuò)誤，通常都是數(shù)據(jù)本身存在問(wèn)題導(dǎo)致的，但本案例中，又不僅僅是數(shù)據(jù)的問(wèn)題。

下面就來(lái)回顧一下問(wèn)題處理的過(guò)程。為了簡(jiǎn)化問(wèn)題，方便理解，以下描述均是在事后構(gòu)建的模擬環(huán)境中進(jìn)行的：

執(zhí)行以下SQL時(shí)，發(fā)生了ora-01841的報(bào)錯(cuò)：

SQL> select * from ( select * from test_tab1 where c1 not like 'X%' )where to_date(c1,'yyyy-mm-dd') > date'2020-11-01' ;ERROR:ORA-01841: (full) year must be between -4713 and +9999, and not be 0
no rows selected

結(jié)合SQL和報(bào)錯(cuò)信息，最初的懷疑是內(nèi)層查詢的結(jié)果集的C1列上，有不正常的數(shù)據(jù)，導(dǎo)致出現(xiàn)了報(bào)錯(cuò)。因此，首先檢查內(nèi)層查詢的結(jié)果：

SQL> select * from test_tab1 where c1 not like 'X%' ;
 ID C1---------- -------------------------------- 1 2020-10-04 2 2020-09-17 3 2020-10-14 4 2020-11-03   5 2020-12-04

我們可以看到，內(nèi)層查詢的結(jié)果集中，并沒(méi)有不正常的數(shù)據(jù)。

到此時(shí)，想了許久，也做了各種測(cè)試，但均沒(méi)有找到問(wèn)題原因。決定看一下執(zhí)行計(jì)劃：

SQL> set autot onSQL> select * from ( select * from test_tab1 where c1 not like 'X%' )where to_date(c1,'yyyy-mm-dd') > date'2020-11-01' ;ERROR:ORA-01841: (full) year must be between -4713 and +9999, and not be 0


no rows selected

Execution Plan----------------------------------------------------------Plan hash value: 1698440217
-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST_TAB1 | 1 | 14 | 3 (0)| 00:00:01 |-------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
 1 - filter(TO_DATE("TEST_TAB1"."C1",'yyyy-mm-dd')>TO_DATE(' 2020-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "C1" NOT LIKE'X%')


Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 419 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
SQL>

從執(zhí)行計(jì)劃中看，CBO對(duì)該SQL做了自動(dòng)改寫，將外層查詢的條件，推到了內(nèi)層查詢。而且，從謂詞信息部分，我們可以看到SQL中的條件“to_date(c1,‘yyyy-mm-dd’) > date’2020-11-01’”在兩個(gè)過(guò)濾條件中，是位于靠前的位置。

也就是說(shuō)，當(dāng)數(shù)據(jù)庫(kù)對(duì)表中的數(shù)據(jù)做過(guò)濾時(shí)，是先用“to_date(c1,‘yyyy-mm-dd’) > date’2020-11-01’”來(lái)檢查。這樣，如果有某行數(shù)據(jù)的C1列中的值不正常，就會(huì)導(dǎo)致這樣的報(bào)錯(cuò)。

我們來(lái)驗(yàn)證一下：

SQL> select * from test_tab1;
 ID C1---------- -------------------------------- 1 2020-10-04 2 2020-09-17 3 2020-10-14 4 2020-11-03 5 2020-12-04 6 XXXXXXXXX1
6 rows selected.


果然，最后一行的C1列中的值是不能正常轉(zhuǎn)換為日期的。

未被CBO自動(dòng)改寫的原始SQL，其內(nèi)層查詢，會(huì)將不能正常轉(zhuǎn)換為日期的數(shù)據(jù)排除掉，然后在外層再去做TO_DATE的轉(zhuǎn)換。如果CBO按照這種方式來(lái)處理，就不會(huì)報(bào)錯(cuò)了。

知道了原因，那我們要如何處理呢？
我們可以改寫SQL，使其必須先執(zhí)行內(nèi)層查詢，然后再執(zhí)行外層查詢。
比如可以在內(nèi)層查詢中加入ROWNUM。

SQL> select * from ( select t.*, rownum rn from test_tab1 t where c1 not like 'X%' )where to_date(c1,'yyyy-mm-dd') > date'2020-11-01'; 2 3 4 5 6 7 8
ID C1 RN---------- -------------------------------- ---------- 4 2020-11-03 4 5 2020-12-04 5

Execution Plan----------------------------------------------------------Plan hash value: 4134971776
---------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 5 | 220 | 3 (0)| 00:00:01 ||* 1 | VIEW | | 5 | 220 | 3 (0)| 00:00:01 || 2 | COUNT | | | | | ||* 3 | TABLE ACCESS FULL| TEST_TAB1 | 5 | 70 | 3 (0)| 00:00:01 |---------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
 1 - filter(TO_DATE("C1",'yyyy-mm-dd')>TO_DATE(' 2020-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 3 - filter("C1" NOT LIKE 'X%')

Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 redo size 711 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2  rows processed


如上所示，我們可以看到，新的執(zhí)行計(jì)劃如我們所愿，先進(jìn)行內(nèi)層查詢的執(zhí)行

再將TO_DATE轉(zhuǎn)換施加到內(nèi)層查詢的結(jié)果之上。

或者，在內(nèi)層查詢上，對(duì)C1進(jìn)行一些不影響結(jié)果值的運(yùn)算。例如：

SQL> select * from ( select id, c1||'' c1 from test_tab1 where c1 not like 'X%' )where to_date(c1,'yyyy-mm-dd') > date'2020-11-01'; 2 3 4 5 6 7
ID C1---------- -------------------------------- 4 2020-11-03 5 2020-12-04

Execution Plan----------------------------------------------------------Plan hash value: 1698440217
-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST_TAB1 | 1 | 14 | 3 (0)| 00:00:01 |-------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
 1 - filter("C1" NOT LIKE 'X%' AND TO_DATE("C1"||'','yyyy-mm-dd')>TO_DATE(' 2020-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 redo size 645 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed

如上所示，這種處理方法，雖然外層的過(guò)濾條件被推入到了內(nèi)層，但會(huì)放到后邊執(zhí)行，這樣，當(dāng)前邊的條件已經(jīng)將不正常的數(shù)據(jù)過(guò)濾掉后，也就不會(huì)報(bào)錯(cuò)了。
同理，對(duì)C1做一些UPPER，LOWER的函數(shù)運(yùn)算，也有同樣的效果。

但是，這又引起了我的一個(gè)新的疑問(wèn)，如果初始SQL就是只有一層（如下所示），兩個(gè)過(guò)濾條件在一起時(shí)，CBO是先用哪個(gè)過(guò)濾條件來(lái)過(guò)濾呢？

select * from test_tab1where c1 not like 'X%'  and to_date(c1,'yyyy-mm-dd') > date'2020-11-01';

執(zhí)行后的結(jié)果如下：

SQL> set autot on SQL> select * from test_tab1where c1 not like 'X%' and to_date(c1,'yyyy-mm-dd') > date'2020-11-01'; 2 3 4 ERROR:ORA-01841: (full) year must be between -4713 and +9999, and not be 0
no rows selected
Execution Plan----------------------------------------------------------Plan hash value: 1698440217
-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST_TAB1 | 1 | 14 | 3 (0)| 00:00:01 |-------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
 1 - filter(TO_DATE("C1",'yyyy-mm-dd')>TO_DATE(' 2020-11-01  00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "C1" NOT LIKE 'X%')
Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 434 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0  rows processed


如上所示，我們發(fā)現(xiàn)仍然會(huì)報(bào)ora-01841的錯(cuò)誤。
和過(guò)濾條件在WHERE子句中出現(xiàn)的順序是否有關(guān)呢？試試調(diào)換條件后的結(jié)果;

SQL> select * from test_tab1where to_date(c1,'yyyy-mm-dd') > date'2020-11-01' and c1 not like 'X%'; 2 3 4 ERROR:ORA-01841: (full) year must be between -4713 and +9999, and not be 0
no rows selected
Execution Plan----------------------------------------------------------Plan hash value: 1698440217
-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST_TAB1 | 1 | 14 | 3 (0)| 00:00:01 |-------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
 1 - filter(TO_DATE("C1",'yyyy-mm-dd')>TO_DATE(' 2020-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "C1" NOT LIKE 'X%')
Statistics---------------------------------------------------------- 1 recursive calls 4 db block gets 4 consistent gets 0 physical reads 0 redo size 434 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0  rows processed

如上所示，看來(lái)和條件出現(xiàn)的順序是無(wú)關(guān)的。
但是，如果是RBO（基于規(guī)則的優(yōu)化器）模式，則會(huì)是先使用最后出現(xiàn)的條件，再使用前邊的。即，從后往前施加條件。這也是為什么網(wǎng)上曾流傳過(guò)的一個(gè)SQL編寫技巧–將過(guò)濾性最好的條件寫到WHERE子句中的最后。但，自O(shè)racle 10g以后，默認(rèn)就是CBO（基于成本的優(yōu)化器）了，除非像上面實(shí)驗(yàn)?zāi)菢邮褂肦ULE的提示，否則，都會(huì)是以CBO方式來(lái)運(yùn)作。

這正好給了我們一個(gè)啟示，在CBO下，在選擇先執(zhí)行哪個(gè)過(guò)濾條件時(shí)，是否會(huì)依據(jù)統(tǒng)計(jì)信息，計(jì)算并排序各個(gè)過(guò)濾條件的選擇性，選擇性越好的，則越會(huì)先被執(zhí)行呢？

我們測(cè)試驗(yàn)證一下。主要測(cè)試思路如下：
1、默認(rèn)情況下，CBO估算大部分非相等的過(guò)濾條件時(shí)，都會(huì)采用5%這樣一個(gè)選擇率。所以，條件“to_date(c1,‘yyyy-mm-dd’) > date’2020-11-01’”的選擇率會(huì)是5%，即，經(jīng)過(guò)該條件過(guò)濾后，CBO認(rèn)為會(huì)返回總記錄的5%的行數(shù)。

2、CBO在計(jì)算NOT LIKE這類條件時(shí)，其計(jì)算思路是先計(jì)算出LIKE的選擇率（類似于相等條件，是條件列中唯一值數(shù)量的倒數(shù)），然后用1-（like的選擇率）就是NOT LIKE的選擇率。

3、向表中再插入94行形如‘XXXXXXXXX1’這樣的記錄。構(gòu)造一個(gè)有100行記錄的表，其中c1列上有100個(gè)唯一值，然后收集統(tǒng)計(jì)信息（注意，不要收集列上的直方圖信息，因?yàn)樵谟兄狈綀D時(shí)，其計(jì)算邏輯和方法都要復(fù)雜得多，這里，我們只用列上的非直方圖的統(tǒng)計(jì)信息）。操作過(guò)程如下：

SQL> insert into test_tab1 select 6+rownum id,lpad(rownum+1,10,'X') c1 from dual connect by rownum<=94;
94 rows created.
SQL> commit;Commit complete.
SQL> exec dbms_stats.gather_table_stats('DEMO','TEST_TAB1',method_opt=>'for columns c1 size 1');
PL/SQL procedure successfully completed.
SQL> select count(*) cnt,count(distinct c1) cnt_c1 from test_tab1;
 CNT CNT_C1---------- ---------- 100 100

分別來(lái)驗(yàn)證一下施加單個(gè)條件時(shí)，CBO的估算結(jié)果

看看是否與前邊的理解是吻合的：

SQL> set autot on expSQL> select * from test_tab1where to_date(c1,'yyyy-mm-dd') > date'2020-11-01'; 2 3 ERROR:ORA-01841: (full) year must be between -4713 and +9999, and not be 0
no rows selected
Execution Plan----------------------------------------------------------Plan hash value: 1698440217
-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 5 | 70 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST_TAB1 | 5 | 70 | 3 (0)| 00:00:01 |-------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
 1 - filter(TO_DATE("C1",'yyyy-mm-dd')>TO_DATE(' 2020-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

如上所示，對(duì)條件“to_date(c1,‘yyyy-mm-dd’) > date’2020-11-01’”返回行數(shù)的估算是5行。由于表中總共有100行，所以，選擇率是5/100=5%。與我們的理解是吻合的。

再來(lái)看對(duì)NOT LIKE的選擇率：

SQL> set autot traceonly expSQL> select * from test_tab1where c1 like 'X%'; 2 3
Execution Plan----------------------------------------------------------Plan hash value: 1698440217
-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST_TAB1 | 1 | 14 | 3 (0)| 00:00:01 |-------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
 1 - filter("C1" LIKE 'X%')
SQL> select * from test_tab1where c1 NOT like 'X%'; 2 3
Execution Plan----------------------------------------------------------Plan hash value: 1698440217
-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 99 | 1386 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST_TAB1 | 99 | 1386 | 3 (0)| 00:00:01 |-------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
   1 - filter("C1" NOT LIKE 'X%')

如上所示，我們看到對(duì)LIKE和NOT LIKE的估算，與我們的理解也是吻合的。
如果我們”先執(zhí)行過(guò)濾性好的條件“的猜測(cè)是正確的，那么這種情形下，顯然，條件“to_date(c1,‘yyyy-mm-dd’) > date’2020-11-01’”的過(guò)濾性（5%）要好過(guò)條件“c1 NOT like ‘X%’”的過(guò)濾性(99%)，所以，會(huì)先執(zhí)行前者。
我們來(lái)驗(yàn)證一下：

SQL> set autot traceonlySQL> select * from test_tab1where c1 not like 'X%' and to_date(c1,'yyyy-mm-dd') > date'2020-11-01'; 2 3 4 ERROR:ORA-01841: (full) year must be between -4713 and +9999, and not be 0
no rows selected
Execution Plan----------------------------------------------------------Plan hash value: 1698440217
-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 5 | 70 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST_TAB1 | 5 | 70 | 3 (0)| 00:00:01 |-------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
 1 - filter(TO_DATE("C1",'yyyy-mm-dd')>TO_DATE(' 2020-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "C1" NOT LIKE 'X%')
Statistics---------------------------------------------------------- 0 recursive calls 4 db block gets 4 consistent gets 0 physical reads 0 redo size 434 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed

那我們?cè)賮?lái)驗(yàn)證一下，如果可以讓條件“c1 NOT like ‘X%’”的選擇率低于5%，那么我們就可能讓CBO選擇先執(zhí)行該條件了。即1-1/n<0.05，顯然，N要小于1.053，由于N表示的是唯一值的數(shù)量，所以，一定是個(gè)整數(shù)，即N只能是1了。


為了滿足這個(gè)條件，我們將表中C1列的值，全部更新為同一個(gè)值：‘XXXXXXXXX1’后，收集統(tǒng)計(jì)信息后，如下所示：
SQL> set autot offSQL> update test_tab1 set c1='XXXXXXXXX1';
100 rows updated.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('DEMO','TEST_TAB1',method_opt=>'for columns c1 size 1');
PL/SQL procedure successfully completed.

我們先來(lái)驗(yàn)證一下前述兩個(gè)條件的選擇性是否如我們所愿，已經(jīng)發(fā)生了改變：

SQL> set autot traceonly expSQL> select * from test_tab1where to_date(c1,'yyyy-mm-dd') > date'2020-11-01'; 2 3
Execution Plan----------------------------------------------------------Plan hash value: 1698440217
-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 5 | 70 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST_TAB1 | 5 | 70 | 3 (0)| 00:00:01 |-------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
 1 - filter(TO_DATE("C1",'yyyy-mm-dd')>TO_DATE(' 2020-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
SQL> select * from test_tab1where c1 like 'X%'; 2 3
Execution Plan----------------------------------------------------------Plan hash value: 1698440217
-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 100 | 1400 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST_TAB1 | 100 | 1400 | 3 (0)| 00:00:01 |-------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
 1 - filter("C1" LIKE 'X%')
SQL> select * from test_tab1where c1 NOT like 'X%'; 2 3
Execution Plan----------------------------------------------------------Plan hash value: 1698440217
-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST_TAB1 | 1 | 14 | 3 (0)| 00:00:01 |-------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
   1 - filter("C1" NOT LIKE 'X%')

如上所示，條件“to_date(c1,‘yyyy-mm-dd’) > date’2020-11-01’”的選擇率未變，仍然是5%，但條件“c1 NOT like ‘X%’”的選擇率已經(jīng)低于5%，目前估算只有大約1行記錄滿足該條件。

那么我們?cè)俅螆?zhí)行測(cè)試SQL，看看結(jié)果如何：

SQL> select * from test_tab1where c1 not like 'X%' and to_date(c1,'yyyy-mm-dd') > date'2020-11-01'; 2 3 4
Execution Plan----------------------------------------------------------Plan hash value: 1698440217
-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST_TAB1 | 1 | 14 | 3 (0)| 00:00:01 |-------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
 1 - filter("C1" NOT LIKE 'X%' AND TO_DATE("C1",'yyyy-mm-dd')>TO_DATE(' 2020-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
SQL> select * from test_tab1where to_date(c1,'yyyy-mm-dd') > date'2020-11-01' and c1 not like 'X%'; 2 3 4
Execution Plan----------------------------------------------------------Plan hash value: 1698440217
-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST_TAB1 | 1 | 14 | 3 (0)| 00:00:01 |-------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
 1 - filter("C1" NOT LIKE 'X%' AND TO_DATE("C1",'yyyy-mm-dd')>TO_DATE(' 2020-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
SQL>

如上所示，這時(shí)，CBO已經(jīng)先執(zhí)行條件“c1 NOT like ‘X%’”了。
同理，即使這時(shí)我們執(zhí)行最初的兩層SQL，其也應(yīng)該是先執(zhí)行條件“c1 NOT like ‘X%’”。驗(yàn)證一下：

SQL> select * from ( select * from test_tab1 where c1 not like 'X%' )where to_date(c1,'yyyy-mm-dd') > date'2020-11-01' ; 2 3 4 5 6 7
Execution Plan----------------------------------------------------------Plan hash value: 1698440217
-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST_TAB1 | 1 | 14 | 3 (0)| 00:00:01 |-------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
 1 - filter("C1" NOT LIKE 'X%' AND TO_DATE("TEST_TAB1"."C1",'yyyy-mm-dd')>TO_DATE(' 2020-11-01 00:00:00',  'syyyy-mm-dd hh24:mi:ss'))

果然是這樣。

附錄：提供上述模擬數(shù)據(jù)的生成腳本

SQL> create table test_tab1 (id number,c1 varchar2(32));
Table created.
SQL> insert into test_tab1 select rownum id,to_char(sysdate-dbms_random.value(1,100),'yyyy-mm-dd') c1 from dual connect by rownum<=5;
5 rows created.
SQL> insert into test_tab1 select 5+rownum id,lpad(rownum,10,'X') c1 from dual connect by rownum<=1;
1 row created.
SQL>commit;
SQL> exec dbms_stats.gather_table_stats('DEMO','TEST_TAB1');
PL/SQL procedure successfully completed.

墨天輪原文鏈接：https://www./db/42008（復(fù)制到瀏覽器中打開(kāi)或者點(diǎn)擊“閱讀原文”立即查看）

    轉(zhuǎn)藏 分享 獻(xiàn)花（0）

    0條評(píng)論

    發(fā)表

    請(qǐng)遵守用戶 評(píng)論公約

    類似文章 更多