虚拟索引-cuug网络公开课《索引应用一勺烩》txt课件.pdfVIP

IT高端实训 企业人才基地 中国UNIX用户协会培训中心 NING@ningselect * from v$version; BANNER Oracle Database 10g Enterprise Edition Release .0 - Prod PL/SQL Release .0 - Production CORE .0 Production TNS for 32-bit Windows: Version .0 - Production NLSRTL Version .0 - Production 1.创建虚拟索引 NING@ningcreate table test(id int,name varchar2(30)); Table created. NING@ninginsert into test select rownum,object_name from all_objects where rownum1001; 1000 rows created. NING@ningcommit; Commit complete. NING@ningcreate unique index ix_test on test(id) nosegment; Index created. NING@ninganalyze table test compute statistics; Table analyzed. 2.使用虚拟索引 NING@ningexplain plan for select * from test where id=1; Explained. NING@ningselect * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT Plan hash value: 1357081020 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | 1 | 17 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TEST | 1 | 17 | 3 (0)| 00:00:01 | 必须设置隐含参数”_use_nosegment_indexes ”=true （默认为false)后，CBO 才能使用虚拟索引ix_test NING@ningalter session set _use_nosegment_indexes=true; Session altered. NING@ningselect * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT Plan hash value: 166686173 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT