[20220811]奇怪的隐式转换问题.txt

来源：这里教程网时间：2026-03-03 17:47:17 作者：

[20220811]奇怪的隐式转换问题.txt --//生产系统遇到一个奇怪的隐式转换问题，问题在于没有发生隐式转换，做一个分析调查。 --//后记：后面的分析存在一下偏差，不更正当时的分析，在这里做一个补充，实际上从12.2版本开始，oracle就支持这样的情况，当使 --//用绑定变量时，带入的绑定变量参数是timestamp类型时，不再存在隐式转换。即使秒后面的值非0!! --//我另外写一篇 [20220811]奇怪的隐式转换问题(12c补充测试).txt 来说明问题。 --//感觉oracle 一个小小的改进，很奇怪我感觉至少我这之前没见过别人写过或者遇到这样的情况。 1.环境： SYS@192.168.100.235:1521/orcl> @ pr ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 19.0.0.0.0 BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. 2.分析： SYS@192.168.100.235:1521/orcl> @dashtop sql_id 1=1 &day Total Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN --------- ------- ------- ------------- ------------------- ------------------- 18420 .2 36% 2022-08-10 08:54:16 2022-08-11 07:59:14 4420 .1 9% 4qz6aykj6gq6v 2022-08-10 08:54:37 2022-08-11 07:46:32 2640 .0 5% gxak7guzxkwbh 2022-08-10 08:54:27 2022-08-11 07:23:29 2000 .0 4% 3rhg88u6qnt7h 2022-08-10 10:01:05 2022-08-11 07:23:29 1690 .0 3% 79ymhrhpk0x3p 2022-08-10 08:55:47 2022-08-11 07:54:03 1600 .0 3% 9akuv534yadhw 2022-08-10 10:13:57 2022-08-11 07:55:33 1100 .0 2% 0bzc2mv4s5f30 2022-08-10 08:55:37 2022-08-11 07:46:42 1030 .0 2% 18xh7p6w137q5 2022-08-10 08:55:17 2022-08-11 07:55:43 ... 30 rows selected. --//分析sql_id=4qz6aykj6gq6v. SYS@192.168.100.235:1521/orcl> @ sql_id 4qz6aykj6gq6v --SQL_ID = 4qz6aykj6gq6v select a.*,1 as TenantId from lis_test a where test_date=:end_date and inst_id=:inst_id AND SENT_INST_FLAG = 0 AND SENT_COUNT<3 and state < :state; SYS@192.168.100.235:1521/orcl> @ dpc 4qz6aykj6gq6v '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 4qz6aykj6gq6v, child number 5 ------------------------------------- select a.*,1 as TenantId from lis_test a where test_date=:end_date and inst_id=:inst_id AND SENT_INST_FLAG = 0 AND SENT_COUNT<3 and state < :state Plan hash value: 557083498 ----------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | ----------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 52419 (100)| | |* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 4 | 2784 | 52419 (1)| 00:00:03 | |* 2 | INDEX SKIP SCAN | IX_LIS_TEST_TEST_NO__831848037 | 249 | | 52170 (1)| 00:00:03 | ----------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / A@SEL$1 2 - SEL$1 / A@SEL$1 Peeked Binds (identified by position): -------------------------------------- 1 - :1 (TIMESTAMP): [Not Printable] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 2 - :2 (NUMBER): 2490 3 - (NUMBER): 90 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("STATE"<:STATE AND "SENT_INST_FLAG"=0 AND "SENT_COUNT"<3)) 2 - access("TEST_DATE"=:END_DATE AND "INST_ID"=:INST_ID) filter(("TEST_DATE"=:END_DATE AND "INST_ID"=:INST_ID)) Note ----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level 42 rows selected. --//索引的名字命名有点怪怪的IX_LIS_TEST_TEST_NO__831848037，好像sql profile提示建立的索引不应该是这样的格式，猜测该产品 --//早期可能在ms sqlserver下开发的。执行计划走的是INDEX SKIP SCAN，前面一个字段是test_no，自然执行效率低下。 --//另外注意下划线可以发现参数1类型timestamp类型，开始没有注意这个细节。 SYS@192.168.100.235:1521/orcl> @ ind2 LIS.IX_LIS_TEST_TEST_NO__831848037 Display indexes where table or index name matches LIS.IX_LIS_TEST_TEST_NO__831848037... TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC -------------------- ------------------------------ ------------------------------ ---- ------------------------------ ---- LIS LIS_TEST IX_LIS_TEST_TEST_NO__831848037 1 TEST_NO 2 TEST_DATE 3 INST_ID 4 NO_PREFIX INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED DEGREE VISIBILIT -------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ --------- LIS LIS_TEST IX_LIS_TEST_TEST_NO__831848037 NORMAL YES VALID NO N 3 52158 8238727 8238727 8217923 2022-08-09 22:19:23 1 VISIBLE SYS@192.168.100.235:1521/orcl> @ descz LIS.LIS_TEST "column_name in ('TEST_NO','TEST_DATE','INST_ID','SENT_INST_FLAG','SENT_COUNT','STATE')" eXtended describe of LIS.LIS_TEST DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT OWNER.TABLE_NAME <filters> SAMPLE : @ TAB_LH TABLE_NAME "column_id between 3 and 5" IF NOT INPUT <filters> ,USE "1=1" . Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value ---------- -------------------- ----------- ------------------- ---- -------------------- ---------- -------------------- ------------ -------------- ---------- --------------- ----------- ------------------- ------------------- LIS LIS_TEST 8701 2022-08-09 22:07:12 3 TEST_NO NOT NULL NUMBER(19,0) 237680 .00000400000 0 HYBRID 254 -9020 2103294101535845721 8701 2022-08-09 22:07:12 4 TEST_DATE NOT NULL DATE(7) 561 .00177900000 0 HYBRID 254 0001-01-01 00:00:00 9773-06-08 00:00:00 8238717 2022-08-09 22:07:12 5 INST_ID NOT NULL NUMBER(10,0) 226 .00000006069 0 FREQUENCY 226 1530 4090 8238717 2022-08-09 22:07:12 28 SENT_INST_FLAG NOT NULL NUMBER(10,0) 2 .00000006069 0 FREQUENCY 2 0 1 8238717 2022-08-09 22:07:12 29 SENT_COUNT NOT NULL NUMBER(10,0) 224 .00000006069 0 FREQUENCY 224 0 393 8238717 2022-08-09 22:07:12 108 STATE NUMBER(10,0) 5 .00000006069 0 FREQUENCY 5 60 100 6 rows selected. --//呵呵还可以看出TEST_DATE存在垃圾数据。High_value=9773-06-08 00:00:00. --//查看绑定变量值如下： SYS@192.168.100.235:1521/orcl> @ bind_cap 4qz6aykj6gq6v '' SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING C30 ------------- ------------ --- --------- -------- ---------- ------------------- --------------- ------------ ------------------------------ 4qz6aykj6gq6v 5 YES :END_DATE 1 11 2022-08-11 08:54:14 TIMESTAMP 2022-08-11 00:00:00.000000000 YES :INST_ID 2 22 2022-08-11 08:54:14 NUMBER 2390 YES :STATE 3 22 2022-08-11 08:54:14 NUMBER 90 3 rows selected. --//注意一个细节变量:END_DATE的数据类型带入的是TIMESTAMP。而前面的显示执行计划显示的是access，filter确是 --//"TEST_DATE"=:END_DATE，而字段TEST_DATE的定义数据类型是date(7),也就是执行计划没有发生没有执行隐式转换。 --//开始怀疑是否存在打入参数即有date类型又有timestamp类型，开始没有注意Peeked Binds (identified by position):部分显示， Peeked Binds (identified by position): -------------------------------------- 1 - :1 (TIMESTAMP): [Not Printable] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 2 - :2 (NUMBER): 2490 3 - (NUMBER): 90 --//不然就不会在开始优化开始时走弯路了。 --//查询如下： SYS@192.168.100.235:1521/orcl> @ bind_cap_awr 4qz6aykj6gq6v END_DATE @bind_cap_awr sql_id [column] 1 row selected. SNAP_ID INSTANCE_NUMBER SQL_ID WAS LAST_CAPTURED NAME POSITION MAX_LENGTH DATATYPE_STRING VALUE_STRING C30 ---------- --------------- ------------- --- ------------------- --------- -------- ---------- --------------- ------------ ------------------------------ 14720 1 4qz6aykj6gq6v YES 2022-08-02 22:48:44 :END_DATE 1 11 TIMESTAMP 2022-08-02 00:00:00.000000000 14721 1 4qz6aykj6gq6v YES 2022-08-02 23:48:48 :END_DATE 1 11 TIMESTAMP 2022-08-02 00:00:00.000000000 14722 1 4qz6aykj6gq6v YES 2022-08-03 00:48:52 :END_DATE 1 11 TIMESTAMP 2022-08-03 00:00:00.000000000 14723 1 4qz6aykj6gq6v YES 2022-08-03 01:48:54 :END_DATE 1 11 TIMESTAMP 2022-08-03 00:00:00.000000000 14724 1 4qz6aykj6gq6v YES 2022-08-03 02:48:58 :END_DATE 1 11 TIMESTAMP 2022-08-03 00:00:00.000000000 14725 1 4qz6aykj6gq6v YES 2022-08-03 03:49:01 :END_DATE 1 11 TIMESTAMP 2022-08-03 00:00:00.000000000 --//snip.... 14921 1 4qz6aykj6gq6v YES 2022-08-11 07:54:12 :END_DATE 1 11 TIMESTAMP 2022-08-11 00:00:00.000000000 14922 1 4qz6aykj6gq6v YES 2022-08-11 08:54:14 :END_DATE 1 11 TIMESTAMP 2022-08-11 00:00:00.000000000 203 rows selected. --//很明显不存在参数即有date类型又有timestamp类型的情况。 SYS@192.168.100.235:1521/orcl> @ cntg LIS.LIS_TEST SENT_INST_FLAG select count(*) , SENT_INST_FLAG from LIS.LIS_TEST group by SENT_INST_FLAG order by 1 desc; COUNT(*) SENT_INST_FLAG ---------- -------------- 5129342 0 3135070 1 2 rows selected. SYS@192.168.100.235:1521/orcl> @ cntg LIS.LIS_TEST SENT_count select count(*) , SENT_count from LIS.LIS_TEST group by SENT_count order by 1 desc; COUNT(*) SENT_COUNT ---------- ---------- 6186388 0 2059983 1 13864 2 2230 3 ... 1 182 224 rows selected. --//建立索引在SENT_INST_FLAG以及SENT_COUNT也是不合理的，也就是根据以上分析建立复合索引：TEST_DATE,INST_ID.而且不存在隐式 --//转换的问题。 CREATE INDEX LIS.i_LIS_TEST_TEST_DATE_INST_ID ON LIS.LIS_TEST (TEST_DATE, INST_ID) LOGGING STORAGE ( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) NOPARALLEL; --//再看执行计划，发现 SYS@192.168.100.235:1521/orcl> @ dpc 4qz6aykj6gq6v '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 4qz6aykj6gq6v, child number 1 ------------------------------------- select a.*,1 as TenantId from lis_test a where test_date=:end_date and inst_id=:inst_id AND SENT_INST_FLAG = 0 AND SENT_COUNT<3 and state < :state Plan hash value: 2953700840 --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 160 (100)| | |* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 6 | 4176 | 160 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | I_LIS_TEST_TEST_DATE_INST_ID | 346 | | 4 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / A@SEL$1 2 - SEL$1 / A@SEL$1 Peeked Binds (identified by position): -------------------------------------- 1 - :1 (TIMESTAMP): [Not Printable] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 2 - :2 (NUMBER): 2890 3 - (NUMBER): 90 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("STATE"<:STATE AND "SENT_INST_FLAG"=0 AND "SENT_COUNT"<3)) 2 - access("TEST_DATE"=:END_DATE AND "INST_ID"=:INST_ID) Note ----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level --//你可以发现oracle可以使用我建立的索引。并且执行计划走INDEX RANGE SCAN使用I_LIS_TEST_TEST_DATE_INST_ID索引。 --//我手工生成脚本,注在sqlplus使用variable无法定义date，timestamp类型，只能使用字符串，注意带入日期的格式要符合要求： SYS@192.168.100.235:1521/orcl> @b9 4qz6aykj6gq6v 0 1 variable END_DATE VARCHAR2(32) variable INST_ID NUMBER variable STATE NUMBER begin :END_DATE := '2022/08/11 00:00:00'; :INST_ID := 2490; :STATE := 90; end; / set termout off set sqlblanklines on alter session set current_schema=LIS; alter session set statistics_level=all; select a.*,1 as TenantId from lis_test a where test_date=:end_date and inst_id=:inst_id AND SENT_INST_FLAG = 0 AND SENT_COUNT<3 and state < :state; set termout on set sqlblanklines off --@zws '' '' --@dpc '' '' @dpc '' outline rollback; alter session set current_schema=SYS ; --//注我取消秒后面的000000000.不然报错我开始就是通过这里发现存在数据类型"转换"问题的。 --//执行计划如下： SQL_ID 4qz6aykj6gq6v, child number 2 ------------------------------------- select a.*,1 as TenantId from lis_test a where test_date=:end_date and inst_id=:inst_id AND SENT_INST_FLAG = 0 AND SENT_COUNT<3 and state < :state Plan hash value: 2953700840 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 157 (100)| | 0 |00:00:00.01 | 335 | 4 | |* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 1 | 6 | 4176 | 157 (0)| 00:00:01 | 0 |00:00:00.01 | 335 | 4 | |* 2 | INDEX RANGE SCAN | I_LIS_TEST_TEST_DATE_INST_ID | 1 | 338 | | 4 (0)| 00:00:01 | 522 |00:00:00.01 | 5 | 4 | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / A@SEL$1 2 - SEL$1 / A@SEL$1 Peeked Binds (identified by position): -------------------------------------- 1 - :1 (VARCHAR2(30), CSID=852): '2022/08/11 00:00:00' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 2 - :2 (NUMBER): 2490 3 - (NUMBER): 90 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("STATE"<:STATE AND "SENT_INST_FLAG"=0 AND "SENT_COUNT"<3)) 2 - access("TEST_DATE"=:END_DATE AND "INST_ID"=:INST_ID) --//可以发现也可以使用索引，但是我的测试无法反映真实的情况,我带入的:END_DATE是varchar2(32)。toad下执行也很麻烦，toad带入 --//的参数类型没有timestamp类型，我也不知道如何处理。我记忆里在itpub上问过，toad下如何使用timestamp数据类型，没人解答这 --//个问题. --//我个人认为问题在于带入的timestamp类型没有秒后面的0值，就不会出现隐式转换。【后记：我有点想当然了】 --//也许oracle 19c版本支持这样的操作，当带入日期类型参数是timestamp类型时，如果秒后面的值全部是0，可以当作date类型使用。 --//导致没有存在隐式转换，我给在11g测试看看. --//加入秒后面的0的情况。 $ cat 4qz6aykj6gq6v.sql9_0 variable END_DATE VARCHAR2(32) variable INST_ID NUMBER variable STATE NUMBER begin :END_DATE := '2022-08-11 00:00:00.000000000'; ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ :INST_ID := 2490; :STATE := 90; end; / set termout off set sqlblanklines on alter session set current_schema=LIS; alter session set statistics_level=all; SElect a.*,1 as TenantId from lis_test a where test_date=:end_date and inst_id=:inst_id AND SENT_INST_FLAG = 0 AND SENT_COUNT<3 and state < :state; set termout on set sqlblanklines off --@zws '' '' --@dpc '' '' @dpc '' outline rollback; alter session set current_schema=SYS ; SYS@192.168.100.235:1521/orcl> @ 4qz6aykj6gq6v.sql9_0 PL/SQL procedure successfully completed. PLAN_TABLE_OUTPUT ----------------------------------------- Last statement has a PREV_SQL_ID of ZERO 2 rows selected. argment : typical all advanced partition predicate remote note parallel projection alias peeked_binds outline bytes argment : adaptive (12c) hint_report (19c) Rollback complete. Session altered. --//执行报错!!也就是这样我才发现带入类型是timestamp类型。 --//还可以通过生产系统的其它语句验证我的测试有效，建立的I_LIS_TEST_TEST_DATE_INST_ID索引有用。 SYS@192.168.100.235:1521/orcl> @ sql_id 18xh7p6w137q5 --SQL_ID = 18xh7p6w137q5 select nvl(max(test_no),0) test_No from lis_test where test_date=:test_date and inst_id=:inst_id and TEST_NO between :StartNo and :MaxNo and is_initial<=1; SYS@192.168.100.235:1521/orcl> @ bind_cap 18xh7p6w137q5 test_date C200 ------------------------------------------------------------------------------------------------------------------------------------------------------------- select nvl(max(test_no),0) test_No from lis_test where test_date=:test_date and inst_id=:inst_id and TEST_NO between :StartNo and :MaxNo and is_initial<=1 SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING C30 ------------- ------------ --- ---------- ---------- ---------- ------------------- --------------- ------------ ------------------------------ 18xh7p6w137q5 2 YES :TEST_DATE 1 11 2022-08-11 09:36:03 TIMESTAMP 2022-08-11 00:00:00.000000000 --//一样是timetamp类型。 SYS@192.168.100.235:1521/orcl> @ dpc 18xh7p6w137q5 '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 18xh7p6w137q5, child number 2 ------------------------------------- select nvl(max(test_no),0) test_No from lis_test where test_date=:test_date and inst_id=:inst_id and TEST_NO between :StartNo and :MaxNo and is_initial<=1 Plan hash value: 467228195 ----------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | ----------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 316 (100)| | | 1 | SORT AGGREGATE | | 1 | 20 | | | |* 2 | FILTER | | | | | | |* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 90 | 1800 | 316 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | I_LIS_TEST_TEST_DATE_INST_ID | 689 | | 5 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 3 - SEL$1 / LIS_TEST@SEL$1 4 - SEL$1 / LIS_TEST@SEL$1 Peeked Binds (identified by position): -------------------------------------- 1 - :1 (TIMESTAMP): [Not Printable] 2 - :2 (NUMBER): 1671 3 - (NUMBER): 1000 4 - (NUMBER): 2000 Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(:MAXNO>=:STARTNO) 3 - filter(("TEST_NO"<=:MAXNO AND "TEST_NO">=:STARTNO AND "IS_INITIAL"<=1)) 4 - access("TEST_DATE"=:TEST_DATE AND "INST_ID"=:INST_ID) Note ----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level --//索引有效!! SYS@192.168.100.235:1521/orcl> @ b9 18xh7p6w137q5 0 1 variable TEST_DATE VARCHAR2(32) variable INST_ID NUMBER variable STARTNO NUMBER variable MAXNO NUMBER begin :TEST_DATE := '2022/08/11 00:00:00.000000000'; :INST_ID := 1725; :STARTNO := 0; :MAXNO := 300; end; / set termout off set sqlblanklines on alter session set current_schema=LIS; alter session set statistics_level=all; select nvl(max(test_no),0) test_No from lis_test where test_date=:test_date and inst_id=:inst_id and TEST_NO between :StartNo and :MaxNo and is_initial<=1; set termout on set sqlblanklines off --@zws '' '' --@dpc '' '' @dpc '' outline rollback; alter session set current_schema=SYS ; SYS@192.168.100.235:1521/orcl> @18xh7p6w137q5.sql9_0 PL/SQL procedure successfully completed. PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------- Last statement has a PREV_SQL_ID of ZERO 2 rows selected. argment : typical all advanced partition predicate remote note parallel projection alias peeked_binds outline bytes argment : adaptive (12c) hint_report (19c) Rollback complete. Session altered. --//顺便改写如下： select a.*,1 as TenantId from lis_test a where test_date=to_timestamp('2022-08-11 00:00:00.000000000','yyyy-mm-dd hh24:mi:ss.ff') and inst_id=1; Plan hash value: 284515054 --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 696 | 564 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 1 | 696 | 564 (0)| 00:00:01 | |* 2 | INDEX SKIP SCAN | I_LIS_TEST_TEST_DATE_INST_ID | 1 | | 563 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / A@SEL$1 2 - SEL$1 / A@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "A"@"SEL$1") INDEX_SS(@"SEL$1" "A"@"SEL$1" ("LIS_TEST"."TEST_DATE" "LIS_TEST"."INST_ID")) OUTLINE_LEAF(@"SEL$1") ALL_ROWS DB_VERSION('19.1.0') OPTIMIZER_FEATURES_ENABLE('19.1.0') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("INST_ID"=1) filter("INST_ID"=1 AND INTERNAL_FUNCTION("TEST_DATE")=TIMESTAMP' 2022-08-11 00:00:00.000000000') --//可以验证发生隐式转换。总结： --//也就是在这种特殊的情况下隐式转换并没有发生，以前好像没有遇到类似的问题，看来19c做了一些改进，看来给在11g下测试看看。

编辑推荐：

返回列表

[20220811]奇怪的隐式转换问题.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推