SQL> drop table t1 purge ;
表已删除。
SQL> create table t1 as select * from dba_objects;
表已创建。
SQL> insert into t1 select * from t1;
已创建72665行。
SQL> /
已创建145330行。
SQL> commit;
提交完成。
SQL> create index t_idx2 on t1(owner,object_name,status) nologging;
索引已创建。
SQL> select owner,object_name from t1 a where status='INVALID';
已选择16行。
执行计划
----------------------------------------------------------
Plan hash value: 2575195509
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 145K| 5411K| 412 (1)| 00:00:05 |
|* 1 | INDEX FAST FULL SCAN| T_IDX2 | 145K| 5411K| 412 (1)| 00:00:05 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"='INVALID')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1938 consistent gets
0 physical reads
0 redo size
744 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16 rows processed
SQL>
SQL> select /*+ parallel(a 4)*/ owner,object_name from t1 a where status='INVALID';
已选择16行。
执行计划
----------------------------------------------------------
Plan hash value: 2494645258
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 145K| 5411K| 252 (1)| 00:00:04 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 145K| 5411K| 252 (1)| 00:00:04 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 145K| 5411K| 252 (1)| 00:00:04 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| T1 | 145K| 5411K| 252 (1)| 00:00:04 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("STATUS"='INVALID')
统计信息
----------------------------------------------------------
12 recursive calls
0 db block gets
4256 consistent gets
4125 physical reads
0 redo size
871 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16 rows processed
INDEX FAST FULL SCAN 多快读 也无法走并行