Oracle uses only a subset of the columns to query on the combined index (the query must contain a leading column or a full table scan).

Lab environment: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

1. Create TABLE to insert data

Sql> CREATE TABLE txtx (ID int,name char (2), TX char (3), Id1 int,primary key (ID,NAME,TX)); sql> INSERT INTO TXTX values (1, ' TX ', ' TX ', 1); 1 rows created. sql> INSERT INTO TXTX values (2, ' TX ', ' TX ', 2); 1 rows created. sql> INSERT into TXTX values (3, ' TX ', ' TX ', 3); 1 rows created. Sql> commit;

Sql> select * from TXTX;           ID NA TX ID1-------------------------1 tx TX 1 2 TX TX 2 3 TX TX 3

2. Implementation plan

Sql> explain plan for select * from txtx  where id=1  and id1 =1 and tx= ' TX '; 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 rows.

Through the above execution plan, you can see, without the leading column, a full table scan, the following use of the leading column, the query speed comes up

Sql> explain plan for select * from txtx  where id=1  and name = ' TX '  and tx= ' TX '; 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):P Lan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- -------------------------   2 - access ("ID" =1 and  "NAME" = ' TX '  and   "tx" = ' TX ') has selected 14 rows.

This article is from the "Corasql" blog, make sure to keep this source http://corasql.blog.51cto.com/5908329/1913521

Oracle uses only a subset of the columns to query on the combined index (the query must contain a leading column or a full table scan).