create table txtx(id i..."/>
您好,登錄后才能下訂單哦!
實(shí)驗(yàn)環(huán)境:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
1、創(chuàng)建表插入數(shù)據(jù)
SQL> create table txtx(id int,name char(2),tx char(3),id1 int,primary key(id,name,tx)); 表已創(chuàng)建。 SQL> insert into txtx values(1,'tx','tx',1); 已創(chuàng)建 1 行。 SQL> insert into txtx values(2,'tx','tx',2); 已創(chuàng)建 1 行。 SQL> insert into txtx values(3,'tx','tx',3); 已創(chuàng)建 1 行。 SQL> commit;
SQL> select * from txtx; ID NA TX ID1 ---------- -- --- ---------- 1 tx tx 1 2 tx tx 2 3 tx tx 3
2、執(zhí)行計(jì)劃
SQL> explain plan for select * from txtx where id=1 and id1 =1 and tx='tx'; 已解釋。 SQL> set linesize 200 SQL> 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) 已選擇17行。
通過以上執(zhí)行計(jì)劃,可以看出,不含前導(dǎo)列,進(jìn)行了全表掃描,以下使用了前導(dǎo)列,查詢速度就上來了
SQL> explain plan for select * from txtx where id=1 and name ='tx' and tx='tx'; 已解釋。 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行。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。