明天做了几个Clob类型数据的检索比拟，后果如下：
- Like 形式检索
set timing on set lin 150 pages 999 set autotrace traceonly select * from lob_tbl where upper(c) LIKE '%2022-04-01 02:05:31.709329%'; 経過: 00:00:02.24 実行計画 ---------------------------------------------------------- Plan hash value: 1723626472 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4017 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| LOB_TBL | 1 | 4017 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(UPPER("C") LIKE '%2022-04-01 02:05:31.709329%') Note ----- - dynamic sampling used for this statement (level=2) 統計 ---------------------------------------------------------- 10 recursive calls 335256 db block gets 128501 consistent gets 28998 physical reads 124 redo size 2368 bytes sent via SQL*Net to client 1819 bytes received via SQL*Net from client 7 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed dbms_lob.instr 形式检索
alter system flush buffer_cache; select * from lob_tbl where dbms_lob.instr(c,'2022-04-01 02:05:31.70932')>0; 経過: 00:00:00.18 実行計画 ---------------------------------------------------------- Plan hash value: 1723626472 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4017 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| LOB_TBL | 1 | 4017 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("DBMS_LOB"."INSTR"("C",'2022-04-01 02:05:31.70932')>0) Note ----- - dynamic sampling used for this statement (level=2)
統計 ---------------------------------------------------------- 633 recursive calls 0 db block gets 47809 consistent gets 14554 physical reads 0 redo size 2368 bytes sent via SQL*Net to client 1819 bytes received via SQL*Net from client 7 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedText 形式检索
CREATE INDEX Content_Tbl_Index ON lob_tbl (c) INDEXTYPE IS CTXSYS.CONTEXT; alter system flush buffer_cache; select * from lob_tbl where contains(c,'%2022-04-01 02:05:31.70932%')>0; 経過: 00:00:00.11 実行計画 ---------------------------------------------------------- Plan hash value: 1854112279 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4029 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| LOB_TBL | 1 | 4029 | 4 (0)| 00:00:01 | |* 2 | DOMAIN INDEX | CONTENT_TBL_INDEX | | | 4 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CTXSYS"."CONTAINS"("C",'%2022-04-01 02:05:31.70932%')>0) Note ----- - dynamic sampling used for this statement (level=2)
統計 ---------------------------------------------------------- 2426 recursive calls 0 db block gets 5753 consistent gets 2632 physical reads 0 redo size 615 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 221 sorts (memory) 0 sorts (disk) 0 rows processed