实验环境：Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
1、创建表插入数据
|
1
2
3
4
5
6
7
8
9
|
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 行。
SQL>
insert
into
txtx
values
(2,
'tx'
,
'tx'
,2);
已创建 1 行。
SQL>
insert
into
txtx
values
(3,
'tx'
,
'tx'
,3);
已创建 1 行。
SQL>
commit
;
|
|
1
2
3
4
5
6
|
SQL>
select
*
from
txtx;
ID NA TX ID1
---------- -- --- ----------
1 tx tx 1
2 tx tx 2
3 tx tx 3
|
2、执行计划
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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行。
|
通过以上执行计划，可以看出，不含前导列，进行了全表扫描，以下使用了前导列，查询速度就上来了
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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行。
|
本文转自 corasql 51CTO博客，原文链接：http://blog.51cto.com/corasql/1913521，如需转载请自行联系原作者
