On the combined index, oracle uses only some columns for query (leading columns must be included in the query, otherwise the whole table will be scanned)
Experimental environment: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production
1. Create a table to insert data
SQL > create table txtx (id int,name char (2), tx char (3), id1 int,primary key (id,name,tx)); table created. SQL > insert into txtx values (1); 1 line created. SQL > insert into txtx values (2) 1 line has been created. SQL > insert into txtx values; 1 line has been created. SQL > commit;SQL > select * from txtx; ID NA TX ID1- 1 txtx 1 2 txtx 2 3 txtx 3
2. Implement the plan
SQL > explain plan for select * from txtx where id=1 and id1 = 1 and tx='tx'; has been explained. SQL > set linesize 200SQL > select * from table (DBMS_XPLAN.DISPLAY) PLAN_TABLE_OUTPUT- -Plan hash value: 4191381592 -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 35 | 3 (0) | 00:00:01 | | * 1 | TABLE ACCESS FULL | TXTX | 1 | 35 | 3 (0) | 00:00:01 |- -Predicate Information (identified by operation id):-PLAN_TABLE_OUTPUT- - -1-filter ("ID" = 1 AND "ID1" = 1 AND "TX" = 'tx') Note--dynamic sampling used for this statement (level=2) has selected 17 lines.
From the above execution plan, we can see that the full table scan is carried out without the leading column. The query speed is up when the leading column is used below.
SQL > explain plan for select * from txtx where id=1 and name = 'tx' and tx='tx'; has been explained. SQL > select * from table (DBMS_XPLAN.DISPLAY) PLAN_TABLE_OUTPUT- -Plan hash value: 913771524 -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 35 | 1 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | TXTX | | 1 | 35 | 1 (0) | 00:00:01 | | * 2 | INDEX UNIQUE SCAN | SYS_C0024000 | 1 | | 1 (0) | 00:00:01 |-- | -Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT - -2-access ("ID" = 1 AND "NAME" = 'tx' AND "TX" =' tx') 14 lines have been selected.
