In 8.1.5 and above it is possible to create virtual indexes
Virtual indexes have a data dictionary definition, but no associated segment
Effectiveness of new indexes can be tested by generating theoretical execution plans
The CBO will consider virtual indexes if the hidden parameter _use_nosegment_indexes is set to true
做测试:
SQL> CREATE TABLE t1 AS
2 SELECT * FROM dba_objects
3 WHERE ROWNUM < 1000;
Table created.
SQL> ANALYZE TABLE t1 COMPUTE STATISTICS;
Table analyzed.
SQL> CREATE INDEX i1 ON t1 (owner, object_name) NOSEGMENT;
Index created.(创建虚拟索引使用NOSEGMENT clause)
SQL> EXECUTE DBMS_STATS.GENERATE_STATS (USER,'I1');
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly
SQL> SELECT object_id FROM t1
2 WHERE owner = USER AND object_name = 'T1';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 21 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='T1' AND "OWNER"=USER@!)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
278 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
(要使用虚拟索引,需要设置隐含参数_use_nosegment_indexes)
SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;
Session altered.
SQL> SELECT object_id FROM t1
2 WHERE owner = USER AND object_name = 'T1';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1704772559
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 21 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"=USER@! AND "OBJECT_NAME"='T1')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
278 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
Virtual indexes have a data dictionary definition, but no associated segment
Effectiveness of new indexes can be tested by generating theoretical execution plans
The CBO will consider virtual indexes if the hidden parameter _use_nosegment_indexes is set to true
做测试:
SQL> CREATE TABLE t1 AS
2 SELECT * FROM dba_objects
3 WHERE ROWNUM < 1000;
Table created.
SQL> ANALYZE TABLE t1 COMPUTE STATISTICS;
Table analyzed.
SQL> CREATE INDEX i1 ON t1 (owner, object_name) NOSEGMENT;
Index created.(创建虚拟索引使用NOSEGMENT clause)
SQL> EXECUTE DBMS_STATS.GENERATE_STATS (USER,'I1');
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly
SQL> SELECT object_id FROM t1
2 WHERE owner = USER AND object_name = 'T1';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 21 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='T1' AND "OWNER"=USER@!)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
278 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
(要使用虚拟索引,需要设置隐含参数_use_nosegment_indexes)
SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;
Session altered.
SQL> SELECT object_id FROM t1
2 WHERE owner = USER AND object_name = 'T1';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1704772559
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 21 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"=USER@! AND "OBJECT_NAME"='T1')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
278 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed