housheng33
- 浏览: 238482 次
- 性别:
- 来自: 北京
-
文章分类
最新评论
-
kitleer：
据我所知，国内有款ETL调度监控工具TaskCTL，支持ket ...
kettle \data-integration\spoon\pantaho 启动问题 -
herextinct：
按照楼主写的操作的，最后出现这个错误： "fail ...
oracle提示TNS:无监听程序的解决思路 -
vinceall：
看不懂啊。。。
数据治理(Data Governance) -
西铁城：
有点高考作文的感觉...
IT励志【成功者大多拥有专注精神 】 -
housheng33：
敏捷，灵活利用～
多学，用时头脑清晰些～
正在边使用边学 ...
数据治理(Data Governance)
A:
最多使用ffs，其他没有什么好方法！
两边都有%，就不会走index了。。
如果表不是很大，可以考虑keep之。。
B:
这个问题首先是在TAOBAO DBA的BLOG上看到丹臣写的关于Like和INSTR的性能问题。不过他只是给出了结果。我对这个函数性能感到有趣，之前一直没有关注过，遂自己详细测试了下。
Oracle 9208:
SQL> select count(*) from item;
COUNT(*)
----------
2781806
SQL> select count(*) from item where item like '%A0';
COUNT(*)
----------
9036
Elapsed: 00:00:04.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1134 Card=1 Bytes=16
)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'PK_ITEM' (UNIQUE) (Cost=1134
Card=138698 Bytes=2219168)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10369 consistent gets
0 physical reads
0 redo size
519 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到走了FFS, 10396 Consistent Gets, Elapsed tail=4.03
再看SUBSTR
SQL> select count(*) from item where substr(item,-2)='A0';
COUNT(*)
----------
9036
Elapsed: 00:00:00.84
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1134 Card=1 Bytes=16
)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'PK_ITEM' (UNIQUE) (Cost=1134
Card=27740 Bytes=443840)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10369 consistent gets
0 physical reads
0 redo size
519 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到PLAN一样，Consistent Gets一样，Elapsed tail=0.84
SUBSTR的耗时只有LIKE的21%左右，鉴于PLAN和Consistent Gets一致，所以可以认为是SUBSTR的CPU Cost要小于LIKE。
NOT LIKE的情况和LIKE相似。
INSTR的情况也如SUBSTR
确实如丹臣所言，Oracle的函数有相当的优化。
但9i的Optimizer在计算Cost的时候是以IO为准，那么在以CPU为准的10G上呢，Cost会有什么差别？
下面在10203上测试
SQL> select count(*) from item where item like '%A0';
COUNT(*)
----------
9104
Elapsed: 00:00:03.03
Execution Plan
----------------------------------------------------------
Plan hash value: 642095792
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | 15 | 2211 (3)| 00:00:27
|
| 1 | SORT AGGREGATE | | 1 | 15 | |
|
|* 2 | INDEX FAST FULL SCAN| PK_ITEM | 139K| 2037K| 2211 (3)| 00:00:27
|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ITEM" LIKE '%A0')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10117 consistent gets
0 physical reads
0 redo size
516 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)
1 rows processed
SQL> select count(*) from item where substr(item,-2)='A0';
COUNT(*)
----------
9104
Elapsed: 00:00:01.24
Execution Plan
----------------------------------------------------------
Plan hash value: 642095792
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | 15 | 2220 (3)| 00:00:27
|
| 1 | SORT AGGREGATE | | 1 | 15 | |
|
|* 2 | INDEX FAST FULL SCAN| PK_ITEM | 27819 | 407K| 2220 (3)| 00:00:27
|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SUBSTR("ITEM",-2)='A0')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10117 consistent gets
0 physical reads
0 redo size
516 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)
1 rows processed
结果是SUBSTR-2220-elapsed 1.24，Like-2211-elapsed 3.03。
显然SUBSTR在CPU占用上要高那么一点， 但是其相对于Like仅 41% 的耗时，使得它完全可以取代Like在查询以XX结尾的SQL中的地位。
BTW，我在一台Idle Server上的测试表明，使用SUBSTR不仅在耗时上缩短，而且CPU使用率也较LIKE低(9.5%/12.5%)
类似的测试表明INSTR相对LIKE ‘%XX%’的优势。
（函数取代LIKE ‘XX%’就别想了，一个Index Range Scan相对FFS的优势太大了）
C:
SQL> select count(*) from t;
COUNT(*)
----------
51838
SQL> insert /*+append*/ into t select * from t;
已创建51838行。
SQL> commit;
提交完成。
SQL> insert /*+append*/ into t select * from t;
已创建103676行。
SQL> commit;
提交完成。
SQL> insert /*+append*/ into t select * from t;
已创建207352行。
SQL> commit;
提交完成。
SQL> insert /*+append*/ into t select * from t;
已创建414704行。
SQL> commit;
提交完成。
SQL> select count(*) from t;
COUNT(*)
----------
829408
SQL> set autot traceonly exp stat
SQL> select * from t where object_type like '%TYPE%';
已选择34064行。
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2592 | 235K| 160 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 2592 | 235K| 160 (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE" LIKE '%TYPE%')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
13623 consistent gets
11364 physical reads
0 redo size
1466572 bytes sent via SQL*Net to client
25370 bytes received via SQL*Net from client
2272 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
34064 rows processed
SQL> SQL> select /*+index(t,t_idx)*/ * from t where object_type like '%TYPE%';
已选择34064行。
执行计划
----------------------------------------------------------
Plan hash value: 3778778741
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2592 | 235K| 260 (2)| 00:00:04 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 2592 | 235K| 260 (2)| 00:00:04 |
|* 2 | INDEX FULL SCAN | T_IDX | 2592 | | 146 (2)| 00:00:02 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_TYPE" LIKE '%TYPE%')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
14131 consistent gets
1982 physical reads
290188 redo size
1477872 bytes sent via SQL*Net to client
25370 bytes received via SQL*Net from client
2272 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
34064 rows processed
SQL> SQL>
SQL>
SQL>
SQL> select * from t where instr(object_type,'TYPE')>0;
已选择34064行。
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2592 | 235K| 161 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 2592 | 235K| 161 (3)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INSTR("OBJECT_TYPE",'TYPE')>0)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
13623 consistent gets
9952 physical reads
0 redo size
1466572 bytes sent via SQL*Net to client
25370 bytes received via SQL*Net from client
2272 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
34064 rows processed
SQL> SQL> SELECT * from t where rowid in(select /*+index_ffs(t,t_idx)*/ rowid from t where object_type like '%TYPE%');
已选择34064行。
执行计划
----------------------------------------------------------
Plan hash value: 628352769
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2592 | 288K| 197 (4)| 00:00:03 |
|* 1 | HASH JOIN | | 2592 | 288K| 197 (4)| 00:00:03 |
|* 2 | INDEX FAST FULL SCAN| T_IDX | 2592 | 54432 | 35 (6)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T | 51838 | 4707K| 161 (3)| 00:00:02 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(ROWID=ROWID)
2 - filter("OBJECT_TYPE" LIKE '%TYPE%')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
17559 consistent gets
9991 physical reads
0 redo size
1466572 bytes sent via SQL*Net to client
25370 bytes received via SQL*Net from client
2272 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
34064 rows processed
小结：
1，目前看instr比rowid及全表报描的性能更高及index_ffs更高
2,index_ffs虽强制走了索引，但成本最高
D:
free介绍的是一种方法；RudolfLu曾经也介绍过一种方法，就是根据搜索字段+rowid建立一个物理表，对这个物理表进行全表，相较ffs会更快一些，不过需要定期刷新物理表，适用于变化较少，查询要求实时性要求不高的环境。
其实也可以用Oracle的全文检索技术，可能会有更好的结果，看我以下的一个例子：
[php]
1.这是模糊查询的结果
SQL> select count(*) from jivemessage where subject like '%abc%';
COUNT(*)
----------
13
Elapsed: 00:00:15.31
2.这是全文检索的结果
SQL> select count(*) from jivemessage where contains(subject,'abc')>0;
COUNT(*)
----------
25
Elapsed: 00:00:00.03
3.模糊查询的内容
SQL> select subject from jivemessage where subject like '%abc%';
SUBJECT
--------------------------------------------------------------------------------
abc
测试发贴""abc
abc
Re: abc
Re: abc
Re: abc
<testabcde>
初夜abc
abcd
abc
abc
SUBJECT
--------------------------------------------------------------------------------
哈哇abc
http;//baoxing.168abc.com
13 rows selected.
Elapsed: 00:00:03.29
4.也许这是我们更想要的结果
SQL> select subject from jivemessage where contains(subject,'abc')>0;
SUBJECT
--------------------------------------------------------------------------------
【游戏】 把你的名字的首字母用智能ABC打出，看能出来什么？
游戏——把你的名字首字母用智能ABC打出来
智能ABC暗藏杀机
ABC
ABC
ABC
ABC
ABC
ＡＢＣ
哈哇abc
abc
SUBJECT
--------------------------------------------------------------------------------
abc
ABC
振奋爱的激情方案ABC
智能ABC的错吗？
ABC全选
瓜果美容ABC
经典英文歌曲ABC，不好你拿版砖砍我，好就回帖顶一下！
初夜abc
Re: abc
Re: abc
Re: abc
SUBJECT
--------------------------------------------------------------------------------
abc
测试发贴""abc
abc
25 rows selected.
Elapsed: 00:00:00.04
D:
select * from foo where rowid in (select /*+index_ffs(foo ind_name) */rowid from foo where name like '%ddd%' )
e:
SELECT
org.*
FROM test_ffs org, (SELECT /*+no_merge index_ffs(test_ffs object_name ) */
ROWID AS r, object_name
FROM test_ffs
WHERE object_name LIKE '%A%' ) tmp
WHERE org.ROWID = tmp.r ;
INDEX_FFS(table index_name)所查询的列必须全部被索引才可使用
f:
1。eygle 的 全文检索法：
对于用于条件的column length 占TABLE的record length的大部分的，速度应该是最快的。如果用于条件的column length 占TABLE的record length的比例并不是很大，那样优势就不明显，毕竟要多安装和管理一个组件。
2。RudolfLu的新表法
和上面对应的是，如果用于条件的column length 占TABLE的record length的比例并不是很大，比较好用，如果用于条件的column length 占TABLE的record length的大部分的话，那就没什么用了，另外，要多维护一个table,还要考虑两个表同步的问题，不建议使用。
3。我的index_ffs + rowid 法
主要优劣势和 RudolfLu的新表法 接近，但是index可以自动更新。而且，在某种环境里，可能已经有相关的index,可能并不需要增加新的index.
另外，没做过测试，不知道相同数据量的index_ffs 和 FTS 性能区别。
g:
1、尽量不要使用 like '%..%'
2、对于 like '..%..' (不以 % 开头)，Oracle可以应用 colunm上的index
3、对于 like '%...' 的 (不以 % 结尾)，可以利用 reverse + function index 的形式，变化成 like '..%' 代码
建测试表和Index。
注意：重点在于带reverse的function index。同时，一定要使用CBO才行......
SQL> select reverse('123') from dual;
REVERSE('123')
--------------------------------
321
1 row selected.
SQL> create table test_like as select object_id,object_name from dba_objects;
Table created.
SQL> create index test_like__name on test_like(object_name);
Index created.
SQL> create index test_like__name_reverse on test_like(reverse(object_name));
Index created.
SQL> analyze table test_like compute statistics for table for all indexes;
Table analyzed.
SQL> set autot trace
--常量开头的like , 会利用index ，没问题......
SQL> select * from test_like where object_name like AS%';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=655 Bytes=15720)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_LIKE' (Cost=2 Card=655Bytes=15720)
2 1 INDEX (RANGE SCAN) OF 'TEST_LIKE__NAME' (NON-UNIQUE) (Cost=2 Card=118)
-- 开头和结尾都是%,对不起，很难优化
SQL> select * from test_like where object_name like '%%';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=655 Bytes=15720)
1 0 TABLE ACCESS (FULL) OF 'TEST_LIKE' (Cost=6 Card=655 ytes=15720)
-- 以常量结束，直接写的时候是不能应用index的
SQL> select * from test_like where object_name like '%S';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=655 Bytes=15720)
1 0 TABLE ACCESS (FULL) OF 'TEST_LIKE' (Cost=6 Card=655 Bytes=15720)
--'以常量结束的，加个reverse 函数，又可以用上index了'
SQL> select * from test_like where reverse(object_name)like reverse('%AS');
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=655 Bytes=15720)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_LIKE' (Cost=2 Card=655 Bytes=15720)
2 1 INDEX (RANGE SCAN) OF 'TEST_LIKE__NAME_REVERSE' (NON-UNIQUE) (Cost=2 Card=118)
最多使用ffs，其他没有什么好方法！
两边都有%，就不会走index了。。
如果表不是很大，可以考虑keep之。。
B:
这个问题首先是在TAOBAO DBA的BLOG上看到丹臣写的关于Like和INSTR的性能问题。不过他只是给出了结果。我对这个函数性能感到有趣，之前一直没有关注过，遂自己详细测试了下。
Oracle 9208:
SQL> select count(*) from item;
COUNT(*)
----------
2781806
SQL> select count(*) from item where item like '%A0';
COUNT(*)
----------
9036
Elapsed: 00:00:04.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1134 Card=1 Bytes=16
)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'PK_ITEM' (UNIQUE) (Cost=1134
Card=138698 Bytes=2219168)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10369 consistent gets
0 physical reads
0 redo size
519 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到走了FFS, 10396 Consistent Gets, Elapsed tail=4.03
再看SUBSTR
SQL> select count(*) from item where substr(item,-2)='A0';
COUNT(*)
----------
9036
Elapsed: 00:00:00.84
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1134 Card=1 Bytes=16
)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'PK_ITEM' (UNIQUE) (Cost=1134
Card=27740 Bytes=443840)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10369 consistent gets
0 physical reads
0 redo size
519 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到PLAN一样，Consistent Gets一样，Elapsed tail=0.84
SUBSTR的耗时只有LIKE的21%左右，鉴于PLAN和Consistent Gets一致，所以可以认为是SUBSTR的CPU Cost要小于LIKE。
NOT LIKE的情况和LIKE相似。
INSTR的情况也如SUBSTR
确实如丹臣所言，Oracle的函数有相当的优化。
但9i的Optimizer在计算Cost的时候是以IO为准，那么在以CPU为准的10G上呢，Cost会有什么差别？
下面在10203上测试
SQL> select count(*) from item where item like '%A0';
COUNT(*)
----------
9104
Elapsed: 00:00:03.03
Execution Plan
----------------------------------------------------------
Plan hash value: 642095792
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | 15 | 2211 (3)| 00:00:27
|
| 1 | SORT AGGREGATE | | 1 | 15 | |
|
|* 2 | INDEX FAST FULL SCAN| PK_ITEM | 139K| 2037K| 2211 (3)| 00:00:27
|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ITEM" LIKE '%A0')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10117 consistent gets
0 physical reads
0 redo size
516 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)
1 rows processed
SQL> select count(*) from item where substr(item,-2)='A0';
COUNT(*)
----------
9104
Elapsed: 00:00:01.24
Execution Plan
----------------------------------------------------------
Plan hash value: 642095792
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | 15 | 2220 (3)| 00:00:27
|
| 1 | SORT AGGREGATE | | 1 | 15 | |
|
|* 2 | INDEX FAST FULL SCAN| PK_ITEM | 27819 | 407K| 2220 (3)| 00:00:27
|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SUBSTR("ITEM",-2)='A0')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10117 consistent gets
0 physical reads
0 redo size
516 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)
1 rows processed
结果是SUBSTR-2220-elapsed 1.24，Like-2211-elapsed 3.03。
显然SUBSTR在CPU占用上要高那么一点， 但是其相对于Like仅 41% 的耗时，使得它完全可以取代Like在查询以XX结尾的SQL中的地位。
BTW，我在一台Idle Server上的测试表明，使用SUBSTR不仅在耗时上缩短，而且CPU使用率也较LIKE低(9.5%/12.5%)
类似的测试表明INSTR相对LIKE ‘%XX%’的优势。
（函数取代LIKE ‘XX%’就别想了，一个Index Range Scan相对FFS的优势太大了）
C:
SQL> select count(*) from t;
COUNT(*)
----------
51838
SQL> insert /*+append*/ into t select * from t;
已创建51838行。
SQL> commit;
提交完成。
SQL> insert /*+append*/ into t select * from t;
已创建103676行。
SQL> commit;
提交完成。
SQL> insert /*+append*/ into t select * from t;
已创建207352行。
SQL> commit;
提交完成。
SQL> insert /*+append*/ into t select * from t;
已创建414704行。
SQL> commit;
提交完成。
SQL> select count(*) from t;
COUNT(*)
----------
829408
SQL> set autot traceonly exp stat
SQL> select * from t where object_type like '%TYPE%';
已选择34064行。
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2592 | 235K| 160 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 2592 | 235K| 160 (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE" LIKE '%TYPE%')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
13623 consistent gets
11364 physical reads
0 redo size
1466572 bytes sent via SQL*Net to client
25370 bytes received via SQL*Net from client
2272 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
34064 rows processed
SQL> SQL> select /*+index(t,t_idx)*/ * from t where object_type like '%TYPE%';
已选择34064行。
执行计划
----------------------------------------------------------
Plan hash value: 3778778741
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2592 | 235K| 260 (2)| 00:00:04 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 2592 | 235K| 260 (2)| 00:00:04 |
|* 2 | INDEX FULL SCAN | T_IDX | 2592 | | 146 (2)| 00:00:02 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_TYPE" LIKE '%TYPE%')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
14131 consistent gets
1982 physical reads
290188 redo size
1477872 bytes sent via SQL*Net to client
25370 bytes received via SQL*Net from client
2272 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
34064 rows processed
SQL> SQL>
SQL>
SQL>
SQL> select * from t where instr(object_type,'TYPE')>0;
已选择34064行。
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2592 | 235K| 161 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 2592 | 235K| 161 (3)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INSTR("OBJECT_TYPE",'TYPE')>0)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
13623 consistent gets
9952 physical reads
0 redo size
1466572 bytes sent via SQL*Net to client
25370 bytes received via SQL*Net from client
2272 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
34064 rows processed
SQL> SQL> SELECT * from t where rowid in(select /*+index_ffs(t,t_idx)*/ rowid from t where object_type like '%TYPE%');
已选择34064行。
执行计划
----------------------------------------------------------
Plan hash value: 628352769
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2592 | 288K| 197 (4)| 00:00:03 |
|* 1 | HASH JOIN | | 2592 | 288K| 197 (4)| 00:00:03 |
|* 2 | INDEX FAST FULL SCAN| T_IDX | 2592 | 54432 | 35 (6)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T | 51838 | 4707K| 161 (3)| 00:00:02 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(ROWID=ROWID)
2 - filter("OBJECT_TYPE" LIKE '%TYPE%')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
17559 consistent gets
9991 physical reads
0 redo size
1466572 bytes sent via SQL*Net to client
25370 bytes received via SQL*Net from client
2272 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
34064 rows processed
小结：
1，目前看instr比rowid及全表报描的性能更高及index_ffs更高
2,index_ffs虽强制走了索引，但成本最高
D:
free介绍的是一种方法；RudolfLu曾经也介绍过一种方法，就是根据搜索字段+rowid建立一个物理表，对这个物理表进行全表，相较ffs会更快一些，不过需要定期刷新物理表，适用于变化较少，查询要求实时性要求不高的环境。
其实也可以用Oracle的全文检索技术，可能会有更好的结果，看我以下的一个例子：
[php]
1.这是模糊查询的结果
SQL> select count(*) from jivemessage where subject like '%abc%';
COUNT(*)
----------
13
Elapsed: 00:00:15.31
2.这是全文检索的结果
SQL> select count(*) from jivemessage where contains(subject,'abc')>0;
COUNT(*)
----------
25
Elapsed: 00:00:00.03
3.模糊查询的内容
SQL> select subject from jivemessage where subject like '%abc%';
SUBJECT
--------------------------------------------------------------------------------
abc
测试发贴""abc
abc
Re: abc
Re: abc
Re: abc
<testabcde>
初夜abc
abcd
abc
abc
SUBJECT
--------------------------------------------------------------------------------
哈哇abc
http;//baoxing.168abc.com
13 rows selected.
Elapsed: 00:00:03.29
4.也许这是我们更想要的结果
SQL> select subject from jivemessage where contains(subject,'abc')>0;
SUBJECT
--------------------------------------------------------------------------------
【游戏】 把你的名字的首字母用智能ABC打出，看能出来什么？
游戏——把你的名字首字母用智能ABC打出来
智能ABC暗藏杀机
ABC
ABC
ABC
ABC
ABC
ＡＢＣ
哈哇abc
abc
SUBJECT
--------------------------------------------------------------------------------
abc
ABC
振奋爱的激情方案ABC
智能ABC的错吗？
ABC全选
瓜果美容ABC
经典英文歌曲ABC，不好你拿版砖砍我，好就回帖顶一下！
初夜abc
Re: abc
Re: abc
Re: abc
SUBJECT
--------------------------------------------------------------------------------
abc
测试发贴""abc
abc
25 rows selected.
Elapsed: 00:00:00.04
D:
select * from foo where rowid in (select /*+index_ffs(foo ind_name) */rowid from foo where name like '%ddd%' )
e:
SELECT
org.*
FROM test_ffs org, (SELECT /*+no_merge index_ffs(test_ffs object_name ) */
ROWID AS r, object_name
FROM test_ffs
WHERE object_name LIKE '%A%' ) tmp
WHERE org.ROWID = tmp.r ;
INDEX_FFS(table index_name)所查询的列必须全部被索引才可使用
f:
1。eygle 的 全文检索法：
对于用于条件的column length 占TABLE的record length的大部分的，速度应该是最快的。如果用于条件的column length 占TABLE的record length的比例并不是很大，那样优势就不明显，毕竟要多安装和管理一个组件。
2。RudolfLu的新表法
和上面对应的是，如果用于条件的column length 占TABLE的record length的比例并不是很大，比较好用，如果用于条件的column length 占TABLE的record length的大部分的话，那就没什么用了，另外，要多维护一个table,还要考虑两个表同步的问题，不建议使用。
3。我的index_ffs + rowid 法
主要优劣势和 RudolfLu的新表法 接近，但是index可以自动更新。而且，在某种环境里，可能已经有相关的index,可能并不需要增加新的index.
另外，没做过测试，不知道相同数据量的index_ffs 和 FTS 性能区别。
g:
1、尽量不要使用 like '%..%'
2、对于 like '..%..' (不以 % 开头)，Oracle可以应用 colunm上的index
3、对于 like '%...' 的 (不以 % 结尾)，可以利用 reverse + function index 的形式，变化成 like '..%' 代码
建测试表和Index。
注意：重点在于带reverse的function index。同时，一定要使用CBO才行......
SQL> select reverse('123') from dual;
REVERSE('123')
--------------------------------
321
1 row selected.
SQL> create table test_like as select object_id,object_name from dba_objects;
Table created.
SQL> create index test_like__name on test_like(object_name);
Index created.
SQL> create index test_like__name_reverse on test_like(reverse(object_name));
Index created.
SQL> analyze table test_like compute statistics for table for all indexes;
Table analyzed.
SQL> set autot trace
--常量开头的like , 会利用index ，没问题......
SQL> select * from test_like where object_name like AS%';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=655 Bytes=15720)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_LIKE' (Cost=2 Card=655Bytes=15720)
2 1 INDEX (RANGE SCAN) OF 'TEST_LIKE__NAME' (NON-UNIQUE) (Cost=2 Card=118)
-- 开头和结尾都是%,对不起，很难优化
SQL> select * from test_like where object_name like '%%';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=655 Bytes=15720)
1 0 TABLE ACCESS (FULL) OF 'TEST_LIKE' (Cost=6 Card=655 ytes=15720)
-- 以常量结束，直接写的时候是不能应用index的
SQL> select * from test_like where object_name like '%S';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=655 Bytes=15720)
1 0 TABLE ACCESS (FULL) OF 'TEST_LIKE' (Cost=6 Card=655 Bytes=15720)
--'以常量结束的，加个reverse 函数，又可以用上index了'
SQL> select * from test_like where reverse(object_name)like reverse('%AS');
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=655 Bytes=15720)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_LIKE' (Cost=2 Card=655 Bytes=15720)
2 1 INDEX (RANGE SCAN) OF 'TEST_LIKE__NAME_REVERSE' (NON-UNIQUE) (Cost=2 Card=118)
分享到：
发表评论
-
MYSQL建立索引{资料}
2013-05-21 08:53 11331.建立索引的时机：若 ... -
informix数据库连接、导出数据到文件的脚本.txt
2013-05-08 10:11 17110、连接informiX库 第一种方式： 开始---》所有程序 ... -
DB2 大数据导入注意 与 客户端连接数据库用名
2013-03-29 14:29 756db2 导入大数据时， C:\>db2 import f ... -
db2 CREATE trigger
2013-02-27 14:24 912--监控某个表，当数据插入到表后，执行触发。 CREAT ... -
DB2 sql 上期（月、季、年）上月月末，上季末，上年末(统计中常用到)
2013-02-23 16:59 4417SELECT current timestamp -da ... -
sql0668n
2012-10-18 10:10 864sql0668n 解决方案： 在服务器端CLP执 ... -
oracle提示TNS:无监听程序的解决思路
2012-08-02 10:45 11988登录提示：无监听程序的解决思路 看了一下F:\oracle\ ... -
powerdesigner将数据库生成PDM
2012-05-15 10:34 17392012/5/15 9:46:39 1\安装数据库所对应用 ... -
aqua data studio 连接 db2
2012-05-10 14:40 1606按你平常的经验去填吧！ -
WIN 7下安装ORACLE 10g心得
2012-04-18 21:18 1051WIN 7下安装ORACLE 10g心得 正在检查操作系统需 ... -
Sybase IQ从远程数据库插入到本地数据库
2012-04-09 09:39 2212Sybase IQ Insert location使用 ... -
有效创建oracle dblink 的两种方式
2012-03-15 10:38 761两台不同的数据库服务器，从一台数据库服务器的一个用户读取另 ... -
sybase ase:windows 无法启动sysam服务
2012-03-12 11:18 962sybase ASE安装： SA的默认密码为空 warn ... -
Oracle与MySQL的几点区别
2012-02-03 16:00 835Oracle数据库与MySQL数据库的区别是本文我们主要介绍的 ... -
命令启动ORACLE9I的两个服务启动与关闭节约内存
2012-02-18 18:45 1283lsnrctl start net start OracleS ... -
Oracle SQL性能优化
2012-01-13 11:01 745（1） 选择最有效率的表名顺序(只在基于规则的优化器 ... -
oracle常用sql语句
2012-01-13 11:01 899正在看的ORACLE教程是:oracle常用sql语句。SQL ... -
imp exp
2012-01-08 10:21 815Oracle的导入实用程序(Import utility)允许 ... -
oracle 自增 auto_increment (附plsql wizard)
2012-01-02 18:24 1032oracle 实现自增需要创建 ... -
PL/SQL中如何导出完整数据库备份
2012-01-02 16:24 5262exp system/manager file=bible_d ...
相关推荐
Oracle SQL 优化 Oracle SQL 优化是数据库性能优化的关键部分。为了提高数据库的性能，我们需要从五个方面进行调整：去掉不必要的大型表的全表扫描、缓存小型表的全表扫描、检验优化索引的使用、检验优化的连接技术...
### MySQL数据库SQL优化 #### 一、SQL优化 在MySQL数据库管理中，SQL查询的性能直接影响到系统的响应时间和资源消耗。通过合理的SQL优化，可以显著提高数据处理速度，降低服务器负载，提升用户体验。 ##### 1.1 ...
### SQL Server 优化知识点 #### 一、SQL性能优化技巧 **1.1 查询的模糊匹配** - **问题描述**：使用 `LIKE '%parm1%'` 的查询方式会导致索引失效，降低查询效率。 - **解决方案**： - **前端改进**：改变用户...
### SQL优化 '%5400%' LIKE操作符 在数据库查询优化中，SQL语句的编写方式对于查询性能有着至关重要的影响。本文将基于提供的标题、描述和部分内容，深入探讨几种常用的SQL优化策略以及特定场景下的优化技巧。 ###...
- **操作符优化**：优化器会尝试将某些操作符（如`LIKE`、`IN`）转换为更高效的形式，比如将`ename LIKE 'SMITH'`转换为`ename = 'SMITH'`，或者将`IN`列表转换为一系列`OR`语句。然而，这种转换依赖于字段类型和...
### SQL Server SQL优化 #### 一、SQL优化的重要性与原则 在SQL Server的实际运行过程中，随着数据量的增长和用户访问频率的提升，数据库性能问题逐渐显现。根据所谓的“二八法则”，即大约20%的慢查询消耗了系统...
SQL语句优化原则是数据库管理和应用开发中的关键环节，它涉及到如何提高查询效率，减少资源消耗，提升系统整体性能。以下是一些针对SQL语句优化的重要原则和方法： 1. **利用索引**：索引是提高查询速度的关键。...
### SQL书写优化与性能优化详解 #### 一、书写风格 **1. SQL语句全部使用小写** 为了保持代码一致性与可读性，所有SQL语句均需使用小写字母编写。这一规则适用于所有的关键字、函数名称及操作符等。 **2. 引用...
### SQL语句优化数据库Java #### 标题与描述中的知识点概述 在标题“SQL语句优化数据库Java”中，我们可以看出文章主要讨论的是如何通过优化SQL语句来提高数据库性能，尤其是对于Java应用程序而言。描述部分则...
SQL 查询优化是数据库优化的重要部分，查询优化器是 SQL SERVER 中的一个组件，可以自动优化查询语句，提高查询效率。本文将详细介绍查询优化器的工作原理、SARG 的定义和应用、查询优化的 tips 等。 一、查询优化...
在数据库系统中，SQL语句的优化是确保系统性能稳定、响应快速的关键环节。在大规模数据环境下，SQL语句可能消耗掉70%至90%的数据库资源，因此，劣质的SQL语句和优质SQL语句之间的性能差距可高达百倍。优化的目标是在...
SQL优化规则_01_Alias相关: 别名使用规则是SQL优化中非常基础但又非常重要的一个方面。首先，建议使用AS关键字来显示声明列或表的别名。在SQL语句中，使用AS关键字可以使别名的定义更加明确，减少歧义，使得SQL语句...
### Oracle SQL语句优化技术分析 #### 概述 Oracle SQL语句优化是数据库管理中的一个重要环节，通过优化SQL语句可以显著提升系统的响应速度、提高数据库资源的利用率以及改善用户体验。本文将从多个角度出发，详细...
### SQL语句优化详解 #### 一、引言 在数据库设计与管理中，SQL语句的优化是一项至关重要的工作。合理的SQL优化不仅能显著提升数据处理速度，还能有效降低服务器资源消耗，从而提高整个系统的响应时间和用户体验。...
文章首先介绍了基本的 SQL 语句，包括 LEFT JOIN 和 INNER JOIN 的区别、LIKE 和 NOT LIKE 的模糊查询、DECODE 函数的使用、CASE WHEN THEN 语句的应用、字符串拼接的方法、日期函数 TO_DATE 和 TO_CHAR 的使用、...
在数据库管理中，SQL语句优化是提升系统性能的关键步骤，尤其对于大型系统而言，一个高效的SQL查询可以显著减少响应时间，改善用户体验。本文将详细阐述"sql语句优化之降龙十八掌"，逐一解析这十八个优化技巧，帮助...
针对上述问题，本文将详细介绍几种有效的LIKE优化方法。 ##### 1. 避免使用 `LIKE '%%'` **问题分析**：当LIKE语句中的模式包含前导“%”，如`LIKE '%XYZ%'`时，Oracle数据库很难直接利用索引来加速查询过程。这是...
SQL语句优化是提高软件系统效率的关键技术，尤其是在大数据量的场景下，高效的SQL能够显著提升数据库查询速度，降低系统资源消耗。以下是一些关于SQL优化的重要知识点： 1. **IN操作符**： - 使用IN操作符虽然使得...
SQL 优化是一个复杂而关键的任务，它涉及到数据库的性能、资源利用率和响应时间。以下是对标题和描述中提到的知识点的详细解释： 1. **没有索引或未使用索引**：索引是提高查询效率的关键，如果没有为常用查询条件...
在SQL中，LIKE '%xxx%'这样的查询意味着我们需要查找包含特定字符串"xxx"的所有记录，不论该字符串出现在字段的哪个位置。由于%"xxx"%前后都有通配符，传统的B树索引无法直接帮助查询，因为它们是为顺序查找设计的，...