请教EXPLAIN的用法

liumangya

我在论坛里看到，可以使用set explain on;来查看索引使用的情况。
可是我怎么弄也没看到用的哪个索引，只有Explain set.，请大侠们帮帮忙啊。

我执行的SQL语句：
set explain on;
select * from sie_sr11_stattrunk_tgalm_hour
where metataskinstance_id=8596 and flag is null and disp is null
and time_stamp='2006-07-27';

得到的结果：
metataskinstance_+  8596
nename              CSG08
tgno                ALLCCS
lno                 268
cic
bicic
eqtyp               TRUNK
opmode              IC
ltg                 4- 7
lc                  0-16
status              RNUC & MDIU
time_stamp          2006-07-27
send_time           17:43:12
flag
disp
conclusion

Explain set.

wenlq

楼主 没看sqexplain.out 文件

差点没看懂，原来是dbaccess 的输出

liumangya

楼上的，好人做到底吧，sqexplain.out 再哪放着啊。

wenlq

退出dbaccess
l

liumangya

看到结果了，这个结果是什么意思呢？我在这个表上建有3个索引，一个是再metataskinstance_id，flag，disp，time_stamp，一个是再time_stamp，第三个先不管。我怎么区分这两个select语句分别是用的那个索引呢？

QUERY:
------
select * from sie_sr11_stattrunk_tgalm_hour
where metataskinstance_id=8596 and flag is null and disp is null
and time_stamp='2006-07-27'

Estimated Cost: 1
Estimated # of Rows Returned: 1

  1) tmn.sie_sr11_stattrunk_tgalm_hour: INDEX PATH

    (1) Index Keys: metataskinstance_id flag disp time_stamp   (Serial, fragments: ALL)
        Lower Index Filter: (((tmn.sie_sr11_stattrunk_tgalm_hour.disp IS NULL AND tmn.sie_sr11_stattrunk_tgalm_hour.flag IS NULL ) AND tmn.sie_sr11_stattrunk_tgalm_hour.time_stamp = datetime(2006-07-27) year to day ) AND tmn.sie_sr11_stattrunk_tgalm_hour.metataskinstance_id = 8596 )



QUERY:
------
select * from sie_sr11_stattrunk_tgalm_hour
where
time_stamp='2006-07-27'

Estimated Cost: 1
Estimated # of Rows Returned: 1

  1) tmn.sie_sr11_stattrunk_tgalm_hour: SEQUENTIAL SCAN

        Filters: tmn.sie_sr11_stattrunk_tgalm_hour.time_stamp = datetime(2006-07-27) year to day

liumangya

大侠们，帮帮忙，给看看吧

liumangya

大家帮忙看看吧，都好几天了

IFMXDBA

For query 1:
index, index key used "metataskinstance_id flag disp time_stamp"
For query 2:
no index being used, sequential scan

I am wondering how many records in the table, how the data distribution it is on the time_stamp field.  If you are using IDS 7.3 or later, you can use SQL Optimizer Directive to force using index scan, even point out which index you are going to use.

IFMXDBA

moreover, try update statistics then run it with set explain on, see if there is any changes.