SQL> select count(*) from test;
COUNT(*)
----------
500000
SQL> select count(*) from test where id=498671;
COUNT(*)
----------
1
在id上有索引，显然如果查where id=498671肯定会走索引：
SQL> exec :x :=498671
PL/SQL procedure successfully completed.
SQL> alter system flush shared_pool;
System altered.
SQL> set autot on
SQL> select * from test where id = :x;
ID NAME
---------- ------------------------------------------------------------
498671 498671
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 499K| 4386K| 269 (3)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 499K| 4386K| 269 (3)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=TO_NUMBER(:X))
Statistics
----------------------------------------------------------
710 recursive calls
0 db block gets
116 consistent gets
0 physical reads
0 redo size
584 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
1 rows processed
autotrace显示的执行计划是全表扫描，而实际的执行计划真的如此吗？
SQL> set autot off
SQL> select sql_id ,sql_text from v$sqlarea where sql_text like '%where id = :x%';
SQL_ID
-------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
32t8xz6c64um5
select sql_id ,sql_text from v$sqlarea where sql_text like '%where id = :x%'
8gaqs0a8u2327
select * from test where id = :x
f3b7q3xr8angs
EXPLAIN PLAN SET STATEMENT_ID='PLUS1053' FOR select * from test where id = :x
SQL> select * from table(dbms_xplan.display_cursor('8gaqs0a8u2327','','basic +peeked_binds'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select * from test where id = :x
Plan hash value: 2473784974
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST |
| 2 | INDEX RANGE SCAN | IDX_TEST |
------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :X (NUMBER): 498671
19 rows selected.
再来看不用绑定变量时autotrace显示的执行计划：
SQL> set autot on
SQL> select * from test where id =498671;
ID NAME
---------- ------------------------------------------------------------
498671 498671
Execution Plan
----------------------------------------------------------
Plan hash value: 2473784974
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 9 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST | 1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=498671)
Statistics
----------------------------------------------------------
1097 recursive calls
0 db block gets
201 consistent gets
0 physical reads
0 redo size
584 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
27 sorts (memory)
0 sorts (disk)
1 rows processed
这一次是正确的执行计划。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10972173/viewspace-714889/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
