一个sql的优化，

郁闷，只是价格count 就不扫描表了
SQL> create index idx_luda_zhutou_obna on zhutou(upper(object_name)) online;

Index created.

SQL> select /*+ INDEX(a IDX_LUDA_ZHUTOU_OBNA)*/ count(a.object_name)
2 from zhutou a
3 where upper(a.object_name) like 'LUDA%';

Execution Plan
----------------------------------------------------------
Plan hash value: 4142235787

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 158 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ZHUTOU | 8 | 528 | 158 (0)| 00:00:02 |
|* 3 | INDEX RANGE SCAN | IDX_LUDA_ZHUTOU_OBNA | 464 | | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

3 - access(UPPER("OBJECT_NAME") LIKE 'LUDA%')
filter(UPPER("OBJECT_NAME") LIKE 'LUDA%')

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
73 consistent gets
1 physical reads
0 redo size
527 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> ^Cselect /*+ INDEX(a IDX_LUDA_ZHUTOU_OBNA)*/ count(upper(a.object_name))

SQL> from zhutou a
SP2-0734: unknown command beginning "from zhuto..." - rest of line ignored.
SQL> where upper(a.object_name) like 'LUDA%'
SP2-0734: unknown command beginning "where uppe..." - rest of line ignored.
SQL>
SQL> select /*+ INDEX(a IDX_LUDA_ZHUTOU_OBNA)*/ count(upper(a.object_name))
2 from zhutou a
3 where upper(a.object_name) like 'LUDA%';

Execution Plan
----------------------------------------------------------
Plan hash value: 3214512578

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
|* 2 | INDEX RANGE SCAN| IDX_LUDA_ZHUTOU_OBNA | 8 | 528 | 4 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

2 - access(UPPER("OBJECT_NAME") LIKE 'LUDA%')
filter(UPPER("OBJECT_NAME") LIKE 'LUDA%')

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
71 consistent gets
0 physical reads
0 redo size
534 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>

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24487368/viewspace-1043317/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24487368/viewspace-1043317/