1、问题
一个查询未走索引，从一个视图中查询，视图定义为5张表的union all。
有明显的条件TMH12 = '1001271876'
2、排查
@?/rdbms/admin/sqltrpt 脚本生成优化建议
通过建议来看，是生成索引的建议，对列进行格式转换，TO_CHAR("TMH12")，
1- Index Finding (see explain plans section below)
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 99.48%)
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index HICANO_POS.IDX
2
0260001
o
n
H
I
C
A
N
O
P
O
S
.
E
C
P
H
P
D
A
(
T
O
C
H
A
R
(
"
T
M
H
12
"
)
)
;
−
C
o
n
s
i
d
e
r
r
u
n
n
i
n
g
t
h
e
A
c
c
e
s
s
A
d
v
i
s
o
r
t
o
i
m
p
r
o
v
e
t
h
e
p
h
y
s
i
c
a
l
s
c
h
e
m
a
d
e
s
i
g
n
o
r
c
r
e
a
t
i
n
g
t
h
e
r
e
c
o
m
m
e
n
d
e
d
i
n
d
e
x
.
c
r
e
a
t
e
i
n
d
e
x
H
I
C
A
N
O
P
O
S
.
I
D
X" role="presentation">20260001onHICANOPOS.ECPHPDA(TOCHAR("TMH12"));−ConsiderrunningtheAccessAdvisortoimprovethephysicalschemadesignorcreatingtherecommendedindex.createindexHICANOPOS.IDX
2
0260001
o
n
H
I
C
A
N
O
P
O
S
.
E
C
P
H
P
D
A
(
T
O
C
H
A
R
(
"
T
M
H
12
"
)
)
;
−
C
o
n
s
i
d
e
r
r
u
n
n
i
n
g
t
h
e
A
c
c
e
s
s
A
d
v
i
s
o
r
t
o
i
m
p
r
o
v
e
t
h
e
p
h
y
s
i
c
a
l
s
c
h
e
m
a
d
e
s
i
g
n
o
r
c
r
e
a
t
i
n
g
t
h
e
r
e
c
o
m
m
e
n
d
e
d
i
n
d
e
x
.
c
r
e
a
t
e
i
n
d
e
x
H
I
C
A
N
O
P
O
S
.
I
D
X
_20260002 on
HICANO_POS.E_CPXSDA(TO_CHAR("TMH12"));
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index HICANO_POS.IDX
2
0260003
o
n
H
I
C
A
N
O
P
O
S
.
E
J
L
Z
C
P
C
P
X
X
M
X
B
(
"
T
M
H
8
"
)
;
−
C
o
n
s
i
d
e
r
r
u
n
n
i
n
g
t
h
e
A
c
c
e
s
s
A
d
v
i
s
o
r
t
o
i
m
p
r
o
v
e
t
h
e
p
h
y
s
i
c
a
l
s
c
h
e
m
a
d
e
s
i
g
n
o
r
c
r
e
a
t
i
n
g
t
h
e
r
e
c
o
m
m
e
n
d
e
d
i
n
d
e
x
.
c
r
e
a
t
e
i
n
d
e
x
H
I
C
A
N
O
P
O
S
.
I
D
X" role="presentation">20260003onHICANOPOS.EJLZCPCPXXMXB("TMH8");−ConsiderrunningtheAccessAdvisortoimprovethephysicalschemadesignorcreatingtherecommendedindex.createindexHICANOPOS.IDX
2
0260003
o
n
H
I
C
A
N
O
P
O
S
.
E
J
L
Z
C
P
C
P
X
X
M
X
B
(
"
T
M
H
8
"
)
;
−
C
o
n
s
i
d
e
r
r
u
n
n
i
n
g
t
h
e
A
c
c
e
s
s
A
d
v
i
s
o
r
t
o
i
m
p
r
o
v
e
t
h
e
p
h
y
s
i
c
a
l
s
c
h
e
m
a
d
e
s
i
g
n
o
r
c
r
e
a
t
i
n
g
t
h
e
r
e
c
o
m
m
e
n
d
e
d
i
n
d
e
x
.
c
r
e
a
t
e
i
n
d
e
x
H
I
C
A
N
O
P
O
S
.
I
D
X
_20260004 on
HICANO_POS.E_CPTHDA(TO_CHAR("TMH12"));
我们先不急去创建这样的索引，先看看为什么进行了格式转换，从执行计划中也看到了filter进行了to_char转换，
查看表定义发现有三张表的TMH2字段是number类型的，第四张表对应的union字段是varchar类型的，
实际上是牺牲了3个表的字段转换，去适应一张表的varchar类型，要么从表涉及字段上统一，要么牺牲一张表去换三张表的性能。
3、调整
调整后由原来的30ms下降到11ms，
Elapsed: 00:00:00.30--》Elapsed: 00:00:00.10，提升2倍速度，执行计划time由25s下降到1s，cost从2049 下降到21
4、总结
字段类型转换会造成无法使用索引的情况，如果非要进行转换，可以创建带有转换类型的索引，函数索引。
