|
同一条SQL语句,只有查询条件不一样,查询返回的结果集都为0,一个走了全表扫描,一个走索引。查看全表扫描的SQL语句:
SQL走全表,产生了2422609个逻辑读,cost为535K
SQL> SELECT URL,YHZH,HFRZY,HFLR,SPURL,TPURL,YPURL,SCSJ,LY,JCSJ
FROM YHXX_HFXX T
WHERE T.URL='http://club.kdnet.net/dispbbs.asp?id=10165509_boardid=1' 2 3
4 /
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2068618995
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 917K| 266M| 535K (1)| 01:47:05 |
|* 1 | TABLE ACCESS FULL| YHXX_HFXX | 917K| 266M| 535K (1)| 01:47:05 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."URL"='http://club.kdnet.net/dispbbs.asp?id=10165509_b
oardid=1')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2422609 consistent gets
3 physical reads
5520 redo size
880 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
强制HINT使用索引,产生4个逻辑读,但是cost比全表扫描高:643K
SQL> SELECT /*+index(YHXX_HFXX IDX_YHXX_HFXX_URL)*/
URL,YHZH,HFRZY,HFLR,SPURL,TPURL,YPURL,SCSJ,LY,JCSJ
FROM YHXX_HFXX
WHERE URL='http://club.kdnet.net/dispbbs.asp?id=10165509_boardid=1' 2 3 4
5 /
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 518948569
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 917K| 266M| 643K (1)| 02:08:48 |
| 1 | TABLE ACCESS BY INDEX ROWID| YHXX_HFXX | 917K| 266M| 643K (1)| 02:08:48 |
|* 2 | INDEX RANGE SCAN | IDX_YHXX_HFXX_URL | 917K| | 10735 (1)| 00:02:09 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("URL"='http://club.kdnet.net/dispbbs.asp?id=10165509_boardid=1')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
880 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
请教如何不通过hint或者sql profile的方法让SQL走索引?谢谢
|
|