In the actual optimization work, it is an important method to change the full table scan of the large tables in the execution plan into an index scan. When tuning a large table, it is inevitable to try to create an index, this is not very good. It is best to create a virtual index and check the CBO evaluation. This method is more efficient. Next we will conduct an experiment and use 10046 to explore its principles.
SQL> drop table test purge;
SQL> create table test as select * from dba_objects;
SQL> alter session set events '10046 trace name context forever, level 12 ';
SQL> create index ind_test_id on test (object_id) nosegment;
SQL> alter session set events '10046 trace name context off ';
SQL> alter session set "_ use_nosegment_indexes" = true;
-- Set an implicit parameter.
SQL> create index ind_test_id on test (object_id) nosegment;-- Create a virtual Index
-- This is not a real execution plan, but a plan in plan_table.
SQL> explain plan for select * from test where object_id = 1;
SQL> set linesize 1000
SQL> select * from table (dbms_xplan.display ());
PLAN_TABLE_OUTPUT
Bytes -------------------------------------------------------------------------------------------
Plan hash value: 1064545891
Bytes -------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -------------------------------------------------------------------------------------------
| 0 | select statement | 8 | 1416 | 5 (0) | 00:00:01 |
| 1 | table access by index rowid | TEST | 8 | 1416 | 5 (0) | 00:00:01 |
| * 2 | index range scan | IND_TEST_ID | 200 | 1 (0) | 00:00:01 |
Bytes -------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("OBJECT_ID" = 1)
SQL> set autotrace traceonly
SQL> select * from test where object_id = 1;
Unselected row
Execution Plan
----------------------------------------------------------
Plan hash value: 1064545891
Bytes -------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -------------------------------------------------------------------------------------------
| 0 | select statement | 8 | 1416 | 5 (0) | 00:00:01 |
| 1 | table access by index rowid | TEST | 8 | 1416 | 5 (0) | 00:00:01 |
| * 2 | index range scan | IND_TEST_ID | 200 | 1 (0) | 00:00:01 |
Bytes -------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("OBJECT_ID" = 1)
Statistics
----------------------------------------------------------
4 recursive cballs
0 db block gets
772 consistent gets
279 physical reads
0 redo size
992 bytes sent via SQL * Net to client
374 bytes encoded ed via SQL * Net from client
1 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> set autotrace off
-- The following figure shows the actual execution plan, which is obtained from the performance_pool and is obviously not indexed.
SQL> alter session set statistics_level = all;
SQL> select * from test where object_id = 1;
SQL> select * from table (dbms_xplan.display_cursor (null, null, 'allstats last '));
PLAN_TABLE_OUTPUT
Bytes ------------------------------------------------------------------------------------
SQL _ID btuhzhv88wwv3, child number 0
-------------------------------------
Select * from test where object_id = 1
Plan hash value: 1357081020
Bytes ------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Time | Buffers |
Bytes ------------------------------------------------------------------------------------
| * 1 | table access full | TEST | 1 | 8 | 0 | 00:00:00. 01 | 706 |
Bytes ------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter ("OBJECT_ID" = 1)
This index cannot be found in user_indexes.
SQL> select index_name, status from user_indexes where table_name = 'test ';
Unselected row
To explore the Principle of Virtual index, we only add an index record to the data dictionary, so that the optimizer can realize the existence of an index and determine whether to use the index as the access path. We found that the index information was inserted into the data dictionary through the 10046 trace.
SQL> alter session set events '10046 trace name context forever, level 12 ';
SQL> create index ind_test_id on test (object_id) nosegment;
SQL> alter session set events '10046 trace name context off ';
Insert into obj $ (owner #, name, namespace, obj #, type #, ctime, mtime, stime, status,
Remoteowner, linkname, subname, dataobj #, flags, oid $, spare1, spare2)
Values
(: 1,: 2,: 3,: 4,: 5,: 6,: 7,: 8,: 9,: 10,: 11,: 12,: 13,: 14,: 15,: 16,: 17)
Insert into icol $ (obj #, bo #, intcol #, pos #, segcol #, segcollength, offset, col #,
Spare1, spare2)
Values
(: 1,: 2,: 3,: 4, 0, 0,: 5,: 6,: 7)
Insert into ind $ (bo #, obj #, ts #, file #, block #, intcols, type #, flags, property,
Pctfree $, initrans, maxtrans, blevel, leafcnt, distkey, lblkkey, dblkkey, clufac,
Cols, analyzetime, samplesize, dataobj #, degree, instances, rowcnt, pctthres $,
Indmethod #, trunccnt, spare1, spare4, spare2, spare6)
Values
(: 1,: 2,: 3,: 4,: 5,: 6,: 7,: 8,: 9,: 10,: 11,: 12,: 13,: 14,: 15,: 16,: 17,: 18,: 19,: 20,: 21,
: 22, decode (: 256, null,: 23), decode (: 24, 1, null,: 24),: 25,: 32 * +: 26,: 27 ,: 28,
: 29,: 30,: 31,: 33)
SQL> select o. obj #, o. owner #, o. name from obj $ o where name = upper ('ind _ test_id ');
OBJ # OWNER # NAME
--------------------------------------------------
61081 61 IND_TEST_ID
