看看這同一句sql,scan index佔用的資源大了很多!!

users: 1815331行 index: inx_mobilephone inx_status pk_username

spool: 1073行 pk_username

SQL>
SQL> select spool.username, spool.sender, spool.message, spool.sid,
users.mobilephone from spool, users
where spool.username = users.username and users.status = '0'
and spool.sender!='pica' and
and users.mobilephone is not null and rownum <= 30; 2 3 4 5

no rows selected

Elapsed: 00:00:01.21

Execution Plan
----------------------------------------------------------
Plan hash value: 119197225

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

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

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

| 0 | SELECT STATEMENT | | 30 | 66390 | 6
1 (22)| 00:00:01 |

|* 1 | COUNT STOPKEY | | | |
| |

|* 2 | HASH JOIN | | 564 | 1218K| 6
1 (22)| 00:00:01 |

|* 3 | TABLE ACCESS FULL | SPOOL | 564 | 1188K| 3
2 (0)| 00:00:01 |

|* 4 | TABLE ACCESS BY INDEX ROWID| USERS | 334K| 9148K| 1
6 (0)| 00:00:01 |

|* 5 | INDEX FULL SCAN | INDEX_MOBILEPHONE | 1967K| |
1 (0)| 00:00:01 |

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

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

1 - filter(ROWNUM<=30)
2 - access("SPOOL"."USERNAME"="USERS"."USERNAME")
3 - filter("SPOOL"."SENDER"<>'pica' AND "SPOOL"."SENDER"<>'@pica')
4 - filter("USERS"."STATUS"='0')
5 - filter("USERS"."MOBILEPHONE" IS NOT NULL)

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

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
252154 consistent gets
0 physical reads
0 redo size
292 bytes sent via SQL*Net to client
235 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

發現 consistent gets : 252154 ,比較大,其中用了全表索引掃描,應該是這個導致了consistent gets很高

我下面加了hint,不用全索引掃描,效能得到大的提高!

SQL> select /*+ no_index(users,INDEX_MOBILEPHONE,INX_STATUS) */ spool.username, spool.sender, spool.message, spool.sid,
users.mobilephone from spool, users
where spool.username = users.username and users.status = '0'
and spool.sender!='pica' and
and users.mobilephone is not null and rownum <= 30;
2 3 4 5
no rows selected

Elapsed: 00:00:00.09
SQL>
SQL> set autotrace traceonly
SQL>
SQL> /

no rows selected

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 2723135770

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

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

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

| 0 | SELECT STATEMENT | | 30 | 65550 | 202 (1
)| 00:00:03 |

|* 1 | COUNT STOPKEY | | | |
| |

| 2 | NESTED LOOPS | | 564 | 1203K| 202 (1
)| 00:00:03 |

|* 3 | TABLE ACCESS FULL | SPOOL | 564 | 1188K| 4 (0
)| 00:00:01 |

|* 4 | TABLE ACCESS BY INDEX ROWID| USERS | 1 | 28 | 1 (0
)| 00:00:01 |

|* 5 | INDEX UNIQUE SCAN | PK_USERS_UN | 1 | | 1 (0
)| 00:00:01 |

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

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

1 - filter(ROWNUM<=30)
3 - filter("SPOOL"."SENDER"<>'pica' AND "SPOOL"."SENDER"<>'@pica')
4 - filter("USERS"."MOBILEPHONE" IS NOT NULL AND "USERS"."STATUS"='0')
5 - access("SPOOL"."USERNAME"="USERS"."USERNAME")

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

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2053 consistent gets
0 physical reads
0 redo size
285 bytes sent via SQL*Net to client
235 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

[@more@]