2273 bytes sent via SQL*Net to client
514 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
33 rows processed
oracle默认采用NL进行全表扫描
此时的逻辑读=consistent gets+db block gets=4194
5、在sql语句中加入hint（1）
SQL> select /*+use_hash(b,a)*/ A.CHANNEL_ID, B.CHANNEL_NAME
2 from USER_TO_CHANNEL A, PUB_CHANNEL B
3 where A.USER_ID = 283
4 and A.CHANNEL_ID = B.CHANNEL_ID
5 and B.CHANNEL_TYPE = 'HTTP';
33 rows selected.
Execution Plan
----------------------------------------------------------
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 1496 | 27 |
| 1 | HASH JOIN | | 34 | 1496 | 27 |
| 2 | INDEX RANGE SCAN | PK_USER_TO_CHANNEL | 34 | 272 | 2 |
| 3 | TABLE ACCESS FULL| PUB_CHANNEL | 4053 | 142K| 24 |
-------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
112 consistent gets
0 physical reads
0 redo size
2273 bytes sent via SQL*Net to client
514 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
33 rows processed
在sql语句中加入hint，此时的逻辑读有明显下降
此时的逻辑读=consistent gets+db block gets=112
6、在sql语句中加入hint（2）
SQL> select /*+ use_nl(b,a) leading(a) */ A.CHANNEL_ID, B.CHANNEL_NAME
2 from USER_TO_CHANNEL A, PUB_CHANNEL B
3 where A.USER_ID = 283
4 and A.CHANNEL_ID = B.CHANNEL_ID
5 and B.CHANNEL_TYPE = 'HTTP';
33 rows selected.
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------------------
---
| Id | Operation | Name | Rows | Bytes | Cost
|
--------------------------------------------------------------------------------
---
| 0 | SELECT STATEMENT | | 34 | 1496 | 3
6 |
| 1 | NESTED LOOPS | | 34 | 1496 | 3
6 |
责任编辑：小草
您现在的位置： 