为什么索引不能使用呢
SQL文如下:
SELECT KOTEINO, SETUBAN,KSETUBAN
FROM DES_UPKEI
WHERE KOTEINO = '0604000823'
AND UPKBN = '070'DES_UPKEI 的索引中DES_UPKEI_INDEX4中的索引列是KOTEINO,UPKBN但是通过TRACE发现,这个查询依然是全表检索,为什么呢
索引重构之后,依然不可请教各位!谢谢
SQL文如下:
SELECT KOTEINO, SETUBAN,KSETUBAN
FROM DES_UPKEI
WHERE KOTEINO = '0604000823'
AND UPKBN = '070'DES_UPKEI 的索引中DES_UPKEI_INDEX4中的索引列是KOTEINO,UPKBN但是通过TRACE发现,这个查询依然是全表检索,为什么呢
索引重构之后,依然不可请教各位!谢谢
如果还是不使用索引,楼主把具体的数据库优化模式,表数据量大小,分析计划等放上来看看
Sort options: default********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************SELECT KOTEINO, SETUBAN,KSETUBAN
FROM DES_UPKEI
WHERE KOTEINO = '0604000823'
AND UPKBN = '070'call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 42 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 42 0 4Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 24 (CANVAS2)Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'DES_UPKEI'
********************************************************************************OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTScall count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 42 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 42 0 4Misses in library cache during parse: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0Misses in library cache during parse: 0 2 user SQL statements in session.
0 internal SQL statements in session.
2 SQL statements in session.
1 statement EXPLAINed in this session.
********************************************************************************
Trace file: orcl_ora_7339.trc
Trace file compatibility: 9.00.01
Sort options: default 1 session in tracefile.
2 user SQL statements in trace file.
0 internal SQL statements in trace file.
2 SQL statements in trace file.
1 unique SQL statements in trace file.
1 SQL statements EXPLAINed using schema:
CANVAS2.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
55 lines in trace file.请各位分析!
谢谢