oracle如何确定索引的顺序,一个关于函数索引的查询-为何前后顺序不同执行计划会变？...

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL> select blocks,num_rows from dba_tables where table_name='T_REPORT_DQCFSHWWC'

2  ;

BLOCKS   NUM_ROWS

---------- ----------

258972   15822228

SQL> select count(*)

from cra.T_REPORT_DQCFSHWWC r

where  to_char(CREATE_DATE,'yyyy-MM-dd')='2014-09-08' and csnm='6000013613';

2    3

Execution Plan

----------------------------------------------------------

Plan hash value: 3271164137

-----------------------------------------------------------------------------------------

| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                    |     1 |    19 | 57026   (1)| 00:11:25 |

|   1 |  SORT AGGREGATE    |                    |     1 |    19 |            |          |

|*  2 |   TABLE ACCESS FULL| T_REPORT_DQCFSHWWC |     1 |    19 | 57026   (1)| 00:11:25 |

-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("CSNM"='6000013613' AND TO_CHAR(INTERNAL_FUNCTION("CREATE_DATE"),'y

yyy-MM-dd')='2014-09-08')

Statistics

----------------------------------------------------------

8  recursive calls

1  db block gets

253105  consistent gets

253076  physical reads

0  redo size

526  bytes sent via SQL*Net to client

520  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> SQL> select count(*)  from cra.T_REPORT_DQCFSHWWC where csnm='6000013613'

and to_char(CREATE_DATE,'yyyy-mm-dd')='2014-09-08' ;  2

Execution Plan

----------------------------------------------------------

Plan hash value: 1905859349

---------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                    |     1 |    22 |  6397   (1)| 00:01:17 |

|   1 |  SORT AGGREGATE              |                    |     1 |    22 |            |          |

|*  2 |   TABLE ACCESS BY INDEX ROWID| T_REPORT_DQCFSHWWC |     1 |    22 |  6397   (1)| 00:01:17 |

|*  3 |    INDEX RANGE SCAN          | IDX_DQCFSHWWC_PLUS |   272K|       |  1065   (1)| 00:00:13 |

---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("CSNM"='6000013613')

3 - access(TO_CHAR(INTERNAL_FUNCTION("CREATE_DATE"),'yyyy-mm-dd')='2014-09-08')

Statistics

----------------------------------------------------------

8  recursive calls

0  db block gets

17828  consistent gets

17823  physical reads

0  redo size

526  bytes sent via SQL*Net to client

520  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed