这句含有like的查询怎么优化?

显示全部楼层 · 2010-3-1 11:06:13

SQL> select n.*
2from NEWS_DATA n, NEWS_TO_CLASSIFY nc
3where n.NEWS_ID=nc.NEWS_ID
4and (nc.CLASSIFY_ID like 'A001%')
5order by n.NEWS_DATE desc;

232118 rows selected.

Elapsed: 00:01:52.48

Execution Plan
----------------------------------------------------------
Plan hash value: 1695460198
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation
| Name
| Rows| Bytes |TempSpc| Cost (%CPU)| Time|
---------------------------------------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT|
| 231K|40M|
| 35930 (1) | 00:07:12 |
| 1|SORT ORDER BY |
| 231K|40M| 92M| 35930 (1)| 00:07:12 |
|*2 | HASH JOIN
|
| 231K|40M|5832K| 26564 (1)| 00:05:19 |
|*3 |TABLE ACCESS FULL| NEWS_TO_CLASSIFY | 229K|3136K|
| 326 (3)| 00:00:04 |
| 4 |TABLE ACCESS FULL | NEWS_DATA
| 431K|70M|
| 22208 (1)| 00:04:27 |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("N"."NEWS_ID"="NC"."NEWS_ID")
3 - filter("NC"."CLASSIFY_ID" LIKE 'A001%')

Statistics
----------------------------------------------------------
481recursive calls
28db block gets
166311consistent gets
120828physical reads

0redo size
1134693596bytes sent via SQL*Net to client
649306986bytes received via SQL*Net from client
571020SQL*Net roundtrips to/from client

0sorts (memory)

1sorts (disk)
232118rows processed

SQL> select n.*
2from NEWS_DATA n, NEWS_TO_CLASSIFY nc
3where n.NEWS_ID=nc.NEWS_ID
4and (nc.CLASSIFY_ID='A001')
5order by n.NEWS_DATE desc;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 593205674
------------------------------------------------------------------------------------------------------------------------------------
|Id| Operation
| Name
| Rows| Bytes | Cost (%CPU)| Time|
------------------------------------------------------------------------------------------------------------------------------------
|0 | SELECT STATEMENT
|
| 282 | 52170 | 612 (1)| 00:00:08 |
|1 |SORT ORDER BY
|
| 282 | 52170 | 612 (1)| 00:00:08 |
|2 | NESTED LOOPS
|
| 282 | 52170 | 611 (0)| 00:00:08 |
|3 |TABLE ACCESS BY INDEX ROWID | NEWS_TO_CLASSIFY| 280 | 3920 | 51 (0)| 00:00:01 |
|*4 | INDEX RANGE SCAN
| IDX_CLASSIFY_ID | 280 |
| 3 (0)| 00:00:01 |
|5 |TABLE ACCESS BY INDEX ROWID | NEWS_DATA
| 1 | 171 | 2 (0)| 00:00:01 |
|*6 | INDEX UNIQUE SCAN
| PK_NEWS_DATA | 1 |
| 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("NC"."CLASSIFY_ID"='A001')
6 - access("N"."NEWS_ID"="NC"."NEWS_ID")

Statistics
----------------------------------------------------------

3recursive calls

0db block gets
29consistent gets

0physical reads

0redo size
20765bytes sent via SQL*Net to client
10104bytes received via SQL*Net from client
10SQL*Net roundtrips to/from client

1sorts (memory)

0sorts (disk)

4rows processed

把nc.CLASSIFY_IDlike 'A001%' 改成 nc.CLASSIFY_ID='A001'为什么会改变两张表的访问方式?
[ 本帖最后由 XQKA 于 2010-7-29 17:26 编辑 ]

千问 · 2010-3-1 11:06:13

一个返回22w一个访问4行,你比啥呢

千问 · 2010-3-1 11:06:13

呵呵，楼上的老大说的对，两条语句根本就不等价

千问 · 2010-3-1 11:06:13

改变一张表的条件为什么会改变两张表的访问方式？

千问 · 2010-3-1 11:06:13

原帖由 myth8860 于 2010-7-29 17:15 发表
呵呵，楼上的老大说的对，两条语句根本就不等价
难道我看不出来吗?

千问 · 2010-3-1 11:06:13

呵呵，不是这个意思，我只是觉得这两条语句完全不一样，没有可比性
第一条语句过滤条件选择性差，走全表扫描比用索引快了

千问 · 2010-3-1 11:06:13

可以把like改写成substr(a,x,x) = 'A001' ,然后建一个函数索引

千问 · 2010-3-1 11:06:13

原帖由 myth8860 于 2010-7-29 17:25 发表
呵呵，不是这个意思，我只是觉得这两条语句完全不一样，没有可比性
第一条语句过滤条件选择性差，走全表扫描比用索引快了
有道理哦，
他们是不是有联动的关系？
即: ("NC"."CLASSIFY_ID"='A001')的选择性良好导致("N"."NEWS_ID"="NC"."NEWS_ID")的选择性良好。
于是两张表的访问方式都改变了。
做了一下count
("NC"."CLASSIFY_ID" LIKE 'A001%')的选择性的确很差。

千问 · 2010-3-1 11:06:13

其实我不是比较这两条语句，只是我在测试时候发现这一现象。这是一个附加的问题。

我的问题主要还在于如何优化第一句sql。

千问 · 2010-3-1 11:06:13

nc.CLASSIFY_ID，这个字段有无柱状图信息？或者，加个索引提示，看看什么结果。