SQL> select * from DHR019 where TI006 <='20100531' and TI006>='20100501';已選取 36524 個資料列.
統計值
Plan hash value: 2839338516----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 389 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DHR019 | 1 | 389 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 1 - filter("TI006"<=U'20100531' AND "TI006">=U'20100501')
参璸
----------------------------------------------------------
0 recursive calls
0 db block gets
12235 consistent gets
0 physical reads
0 redo size
2394456 bytes sent via SQL*Net to client
27159 bytes received via SQL*Net from client
2436 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36524 rows processed而我在TI006上面已经建立了索引.为何不会使用呢.
統計值
Plan hash value: 2839338516----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 389 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DHR019 | 1 | 389 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 1 - filter("TI006"<=U'20100531' AND "TI006">=U'20100501')
参璸
----------------------------------------------------------
0 recursive calls
0 db block gets
12235 consistent gets
0 physical reads
0 redo size
2394456 bytes sent via SQL*Net to client
27159 bytes received via SQL*Net from client
2436 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36524 rows processed而我在TI006上面已经建立了索引.为何不会使用呢.
analyze table DHR019 compute statistics for all indexes;Oracle 10g Statistic数据统计
http://blog.csdn.net/tianlesoftware/archive/2009/10/15/4668723.aspx
------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群:62697716(满); DBA2 群:62697977
DBA3 群:63306533; 聊天 群:40132017
方法二、select /*+ index(DHR019 TI006) */ * from DHR019 where TI006 <='20100531' and TI006>='20100501';
select /*+ index(DHR019 TI006) */ * from DHR019 where TI006 <='20100531' and TI006>='20100501';
方法二、更换成to_char('20100531','yyyymmdd'/*确定你的TI006字段中数据的格式*/)。
另外这把这个数据库弄了两份.应该是一模一样.但是一个上面走索引.一个上面走全表扫描.
我很不明白.select * from DHR019 where TI006 <=to_char('20100531') and TI006>=to_char('20100501')
如此后走了索引.可是另外一个为什么不用这样也是走索引呢.
这两个数据库都是我从同一个SLQSERVER数据库移植过来的.应该是一模一样的.
7楼.这个不会.
为何2个应该一样的结果会不一样呢.
另外.使用to_char() 转型之后却会使用索引了.