这几天杭州雾蒙蒙的，马上进入夏季了， 杭州的炎热不是厦门可以比拟的，相比起来，这里是个大蒸笼. 我的工作环境还好，至少还算不热

郁闷，只是价格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>