明天做了几个 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 processed
-
Text 形式检索
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