Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.