这几周一直在研究索引虚拟之类的问题,现在正好有机会和大家共享一下.
SQL> create table tb1 as select owner,object_name,object_id from dba_objects; --创立测试表
Table created.
SQL> select count(*) from tb1;
COUNT(*)
----------
50518
SQL> explain plan for select * from tb1 where object_id=108;
Explained.
SQL> select * from table(dbms_xplan.display); ---未创立虚拟索引前的执行计划
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3226679318
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 384 | 78 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB1 | 4 | 384 | 78 (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("OBJECT_ID"=108)
Note
-----
- dynamic sampling used for this statement
17 rows selected.
SQL> alter session set "_use_nosegment_indexes"=true; --改修关相参数
Session altered.
SQL> create index tb1_object_id_idx on tb1(object_id) nosegment; --创立虚拟索引
Index created.
SQL> explain plan for select * from tb1 where object_id=108; --创立虚拟索引侯的执行计划
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 959502086
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 384 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB1 | 4 | 384 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TB1_OBJECT_ID_IDX | 219 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("OBJECT_ID"=108)
Note
-----
- dynamic sampling used for this statement
18 rows selected.
文章结束给大家分享下程序员的一些笑话语录:
不会,Intel会维持高利润,也会维持竞争局面,国外的竞争不是打死对方的那种。你看日本有尼康,佳能,索尼,都做相机,大家都过得很滋润。别看一堆厂,其实真正控制的是后面的那几个财团——有些竞争对手,后面其实是一家人。