--看一下测试吧,用加引号和不加引号都使用了user_id上面的唯一索引,SQL> set serverout on SQL> set autotrace traceonly SQL> select * from fnd_user a where a.user_id=1055; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=214) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'FND_USER' (Cost=1 Card=1 Bytes=214) 2 1 INDEX (UNIQUE SCAN) OF 'FND_USER_U1' (UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 1 physical reads 0 redo size 1201 bytes sent via SQL*Net to client 308 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedSQL> select * from fnd_user a where a.user_id='1055'; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=214) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'FND_USER' (Cost=1 Card=1 Bytes=214) 2 1 INDEX (UNIQUE SCAN) OF 'FND_USER_U1' (UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 1 physical reads 0 redo size 1218 bytes sent via SQL*Net to client 308 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedSQL>
以下情况不使用索引 select * from tablename where id+0=1 where user_name||''= 'smith' where to_number(字段)=4 where user_name like '%D' where user_name like 'D%' where user_name like '%D%' where user_name like 'D%D' where to_char(字段)='k' where nvl(字段,'0')='3' where to_date(字段) ...... --以上排除建了函数索引的情况,如果想用索引则建立函数索引.提高执行速度: 1.尽量少在条件中用to_number,to_date等的计算, 2.尽量少用复杂的表达式. 3.尽量少用where id=nvl(:id,id) 4.字段变长时用where user_name ='name',定长时用where user_name like 'name' .....
SQL> set autotrace traceonly
SQL> select * from fnd_user a where a.user_id=1055;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=214)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'FND_USER' (Cost=1 Card=1
Bytes=214) 2 1 INDEX (UNIQUE SCAN) OF 'FND_USER_U1' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
1 physical reads
0 redo size
1201 bytes sent via SQL*Net to client
308 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedSQL> select * from fnd_user a where a.user_id='1055';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=214)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'FND_USER' (Cost=1 Card=1
Bytes=214) 2 1 INDEX (UNIQUE SCAN) OF 'FND_USER_U1' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
1 physical reads
0 redo size
1218 bytes sent via SQL*Net to client
308 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedSQL>
select * from tablename where id+0=1
where user_name||''= 'smith'
where to_number(字段)=4
where user_name like '%D'
where user_name like 'D%'
where user_name like '%D%'
where user_name like 'D%D'
where to_char(字段)='k'
where nvl(字段,'0')='3'
where to_date(字段)
......
--以上排除建了函数索引的情况,如果想用索引则建立函数索引.提高执行速度:
1.尽量少在条件中用to_number,to_date等的计算,
2.尽量少用复杂的表达式.
3.尽量少用where id=nvl(:id,id)
4.字段变长时用where user_name ='name',定长时用where user_name like 'name'
.....