看看执行计划? 1. 在当前用户下创建PLAN_TABLE:CREATE GLOBAL TEMPORARY TABLE PLAN_TABLE (STATEMENT_ID VARCHAR2(30) NULL, TIMESTAMP DATE NULL, REMARKS VARCHAR2(80) NULL, OPERATION VARCHAR2(30) NULL, OPTIONS VARCHAR2(30) NULL, OBJECT_NODE VARCHAR2(128) NULL, OBJECT_OWNER VARCHAR2(30) NULL, OBJECT_NAME VARCHAR2(30) NULL, OBJECT_INSTANCE NUMBER NULL, OBJECT_TYPE VARCHAR2(30) NULL, OPTIMIZER VARCHAR2(255) NULL, SEARCH_COLUMNS NUMBER NULL, ID NUMBER NULL, PARENT_ID NUMBER NULL, POSITION NUMBER NULL, COST NUMBER NULL, CARDINALITY NUMBER NULL, BYTES NUMBER NULL, OTHER_TAG VARCHAR2(255) NULL, PARTITION_START VARCHAR2(255) NULL, PARTITION_STOP VARCHAR2(255) NULL, PARTITION_ID NUMBER NULL, OTHER LONG NULL) ON COMMIT DELETE ROWS;2. 运行如下SQL:DELETE FROM PLAN_TABLE; EXPLAIN PLAN SET STATEMENT_ID = '1' FOR SELECT * FROM DEALER WHERE DISTID=:a ;SELECT LPAD(' ',2*(LEVEL-1))||operation||' '||options ||' '||object_name ||' '||DECODE(id, 0, 'Cost = '||position) "Query Plan" FROM plan_table START WITH id = 0 AND statement_id = '1' CONNECT BY PRIOR id = parent_id AND statement_id ='1';注意:将红色部分替换为要解释的SQL即可.如果用Command的SQL包含参数符号'?',则要 将'?'依次替换为:a, :b, ...等的参数形式.3. 上面的SQL将作如下格式的输出Query Plan ------------------------------------------------------------------------------------ SELECT STATEMENT Cost = 3 TABLE ACCESS BY INDEX ROWID DEALER INDEX UNIQUE SCAN PK_DEALER上面是我最常看的输出形式,还有其他一些输出格式的SQL,可参考Oracle文档的 Explain Plan命令 --根据此方法,可以不执行SQL,看执行计划
1. 在当前用户下创建PLAN_TABLE:CREATE GLOBAL TEMPORARY TABLE PLAN_TABLE (STATEMENT_ID VARCHAR2(30) NULL,
TIMESTAMP DATE NULL,
REMARKS VARCHAR2(80) NULL, OPERATION VARCHAR2(30) NULL,
OPTIONS VARCHAR2(30) NULL, OBJECT_NODE VARCHAR2(128) NULL,
OBJECT_OWNER VARCHAR2(30) NULL, OBJECT_NAME VARCHAR2(30) NULL,
OBJECT_INSTANCE NUMBER NULL, OBJECT_TYPE VARCHAR2(30) NULL,
OPTIMIZER VARCHAR2(255) NULL, SEARCH_COLUMNS NUMBER NULL,
ID NUMBER NULL, PARENT_ID NUMBER NULL, POSITION NUMBER NULL,
COST NUMBER NULL, CARDINALITY NUMBER NULL, BYTES NUMBER NULL,
OTHER_TAG VARCHAR2(255) NULL, PARTITION_START VARCHAR2(255) NULL,
PARTITION_STOP VARCHAR2(255) NULL, PARTITION_ID NUMBER NULL, OTHER LONG
NULL)
ON COMMIT DELETE ROWS;2. 运行如下SQL:DELETE FROM PLAN_TABLE;
EXPLAIN PLAN SET STATEMENT_ID = '1'
FOR
SELECT * FROM DEALER WHERE DISTID=:a
;SELECT LPAD(' ',2*(LEVEL-1))||operation||' '||options
||' '||object_name
||' '||DECODE(id, 0, 'Cost = '||position) "Query Plan"
FROM plan_table
START WITH id = 0 AND statement_id = '1'
CONNECT BY PRIOR id = parent_id AND statement_id ='1';注意:将红色部分替换为要解释的SQL即可.如果用Command的SQL包含参数符号'?',则要
将'?'依次替换为:a, :b, ...等的参数形式.3. 上面的SQL将作如下格式的输出Query Plan
------------------------------------------------------------------------------------
SELECT STATEMENT Cost = 3
TABLE ACCESS BY INDEX ROWID DEALER
INDEX UNIQUE SCAN PK_DEALER上面是我最常看的输出形式,还有其他一些输出格式的SQL,可参考Oracle文档的
Explain Plan命令
--根据此方法,可以不执行SQL,看执行计划
建议楼主检查并重编译索引