如何在PL/sql中判断某表的执行计划是否走索引??????

Question

如何在PL/sql中判断某表的执行计划是否走索引??????是否可以判断??若可以该怎样实现???

Answer 1

lz给你个示例：SQL> @?/rdbms/admin/utlxplan.sql;Table created.
SQL> alter session set "_always_semi_join"=off;Session altered.SQL> explain plan for 
  2  select q.id as questionid, q.text as questiontext, q.is_deleted, q.create_date, q.created_by, q.last_update_date, q.last_updated_by, q.image_url, q.image_location, q.category_id, q.allow_html, q.site_id, qbq.question_bank_id, qbq.question_id as qstid
from ilearn.question q,ilearn.question_bank_question qbq,ilearn.response_type rt
where exists
      (select 'x'
       from ilearn.response_value
       where response_value.response_type_id = rt.id and response_value.is_deleted != 'y') and
       q.id = qbq.question_id and
       rt.question_id = q.id and
       rt.is_deleted != 'y' and
       q.is_deleted != 'y' and
       qbq.question_bank_id = :1  3    4    5    6    7    8    9   10   11   12  
 13  /Explained.SQL> @?/rdbms/admin/utlxpls.sql;PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    |  Name                       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                             |     6 |   810 |   142 |
|*  1 |  FILTER                      |                             |       |       |       |
|*  2 |   HASH JOIN                  |                             |     6 |   810 |   130 |
|   3 |    NESTED LOOPS              |                             |   118 | 14278 |    86 |
|*  4 |     TABLE ACCESS FULL        | QUESTION                    | 51988 |  5533K|    86 |
|*  5 |     INDEX UNIQUE SCAN        | QUESTION_BANK_QUESTION_PK   |     1 |    12 |       |
|*  6 |    TABLE ACCESS FULL         | RESPONSE_TYPE               |  2599 | 36386 |    43 |
|*  7 |   TABLE ACCESS BY INDEX ROWID| RESPONSE_VALUE              |     4 |    32 |     2 |
|*  8 |    INDEX RANGE SCAN          | RESPONSE_VALUE_RTYPE_INDEX  |     4 |       |     1 |
--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   1 - filter( EXISTS (SELECT /*+ */ 0 FROM "ILEARN"."RESPONSE_VALUE" "RESPONSE_VALUE"
              WHERE "RESPONSE_VALUE"."RESPONSE_TYPE_ID"=:B1 AND "RESPONSE_VALUE"."IS_DELETED"<>'y'))
   2 - access("SYS_ALIAS_1"."QUESTION_ID"="Q"."ID")
   4 - filter("Q"."IS_DELETED"<>'y')
   5 - access("QBQ"."QUESTION_BANK_ID"=TO_NUMBER(:Z) AND "Q"."ID"="QBQ"."QUESTION_ID")
   6 - filter("SYS_ALIAS_1"."IS_DELETED"<>'y')
   7 - filter("RESPONSE_VALUE"."IS_DELETED"<>'y')
   8 - access("RESPONSE_VALUE"."RESPONSE_TYPE_ID"=:B1)Note: cpu costing is off28 rows selected.
这个不是一个好的执行计划，因为一上来就是全表扫描（table full access）走索引的话，会是：INDEX scan

Answer 2

谢谢你但我没太看明白/我想在pl/sql的过程中判断一个表的执行计划是否走索引.请问该怎么实现呢我是新学.请多多帮忙啊!
:)

Answer 3

哦
看来我要想其他的办法了.谢谢tgm78的热心帮助.:)