例如:
表t_test(col1,col2,col3),并且col1,col2上创建了索引,
然后如此使用:
select
*
from
(select col1,col2 from t_test) t1
where
t1.col1 = '1'
and t2.col2 = '03'现在是否t1.col1,t1.col2就成了一般的没有索引功能的普通字段了?
表t_test(col1,col2,col3),并且col1,col2上创建了索引,
然后如此使用:
select
*
from
(select col1,col2 from t_test) t1
where
t1.col1 = '1'
and t2.col2 = '03'现在是否t1.col1,t1.col2就成了一般的没有索引功能的普通字段了?
select col1,col2 from t_test
where
t1.col1 = '1'
and t2.col2 = '03'
1';Explained.SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------Plan hash value: 4091508081---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 21 | 1 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T_TEST | 1 | 21 | 1 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | T_TEST1 | 1 | | 1 (0)| 00:00:01 |---------------------------------------------------------------------------------------PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("T1"."COL1"='1' AND "T1"."COL2"='1')14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=24 Card=8168 Bytes
=16336) 1 0 COLLECTION ITERATOR (PICKLER FETCH) OF 'DISPLAY'
Statistics
----------------------------------------------------------
1249 recursive calls
0 db block gets
1346 consistent gets
127 physical reads
0 redo size
1296 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
63 sorts (memory)
0 sorts (disk)
14 rows processedSQL> explain plan for SELECT * FROM (SELECT col1,col2 FROM t_test) t1 WHERE t1.c
ol1 = '1' AND t1.col2 = '03';Explained.SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------Plan hash value: 3016185506----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| T_TEST1 | 1 | 14 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - access("COL1"='1' AND "COL2"='03')13 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=24 Card=8168 Bytes
=16336) 1 0 COLLECTION ITERATOR (PICKLER FETCH) OF 'DISPLAY'
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1128 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
13 rows processedSQL>
alter index index_name unusable; --取消索引
alter index index_name rebuild;--索引归正