sql执行计划请教谢谢了

显示全部楼层 · 2007-9-26 18:42:10

SQL> select /*+index(c,ind_ph_ton_opt_3)*/ count(*) an,to_char(optime,'YYYY-MM') ad from t_personall
ib_zj_10_080811 c group by to_char(optime,'YYYY-MM');
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=25291 Card=723 Bytes

=5784)
10 SORT (GROUP BY NOSORT) (Cost=25291 Card=723 Bytes=5784)
21 TABLE ACCESS (BY INDEX ROWID) OF 'T_PERSONALLIB_ZJ_10_08

0811' (Cost=826 Card=15775220 Bytes=126201760)
32 INDEX (FULL SCAN) OF 'IND_PH_TON_OPT_3' (NON-UNIQUE) (

Cost=26 Card=15775220)
IND_PH_TON_OPT_3 是这样的
create index IND_PH_TON_OPT_3 on T_PERSONALLIB_ZJ_10_080811 (TO_CHAR(OPTIME,'YYYY-MM'));
数据库是9207的
执行计划的第二步是必须的吗？？我本以为只需要读取索引就够了

千问 · 2007-9-26 18:42:10

select /*+index(c,ind_ph_ton_opt_3)*/ count(to_char(optime,'YYYY-MM')) an,to_char(optime,'YYYY-MM') ad from t_personallib_zj_10_080811 c group by to_char(optime,'YYYY-MM');
试试

千问 · 2007-9-26 18:42:10

SQL> select /*+index(c,ind_ph_ton_opt_3)*/ count(to_char(optime,'YYYY-MM')) an,to_char(optime,'YYYY-
MM') ad from t_personallib_zj_10_080811 c group by to_char(optime,'YYYY-MM');
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=25291 Card=723 Bytes

=5784)
10 SORT (GROUP BY NOSORT) (Cost=25291 Card=723 Bytes=5784)
21 TABLE ACCESS (BY INDEX ROWID) OF 'T_PERSONALLIB_ZJ_10_08

0811' (Cost=826 Card=15775220 Bytes=126201760)
32 INDEX (FULL SCAN) OF 'IND_PH_TON_OPT_3' (NON-UNIQUE) (

Cost=26 Card=15775220)

一样的

千问 · 2007-9-26 18:42:10

select * from user_ind_expressions where index_name=upper('ind_ph_ton_opt_3');
把这个发出来

千问 · 2007-9-26 18:42:10

SQL> select * from user_ind_expressions where index_name=upper('ind_ph_ton_opt_3');

INDEX_NAME
TABLE_NAME
COLUMN_EXPRESSION
COLUMN_POSITION
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ---------------
IND_PH_TON_OPT_3
T_PERSONALLIB_ZJ_10_080811 TO_CHAR("OPTIME",'YYYY-MM')
1

千问 · 2007-9-26 18:42:10

谢谢棉花糖ONE

千问 · 2007-9-26 18:42:10

explain plan for select /*+index(c,ind_ph_ton_opt_3)*/ count(*) an,to_char(optime,'YYYY-MM') ad from t_personall
ib_zj_10_080811 c group by to_char(optime,'YYYY-MM');

select ACCESS_PREDICATES ,FILTER_PREDICATES from plan_table;看看第二步都做了什么

千问 · 2007-9-26 18:42:10

应该是没必要回表的

千问 · 2007-9-26 18:42:10

是不是9i的问题
我测了一下，10g没问题，9i需要access table
[ 本帖最后由 gclizh 于 2008-8-12 16:37 编辑 ]

千问 · 2007-9-26 18:42:10

SQL> explain plan for select /*+index(c,ind_ph_ton_opt_3)*/ count(TO_CHAR("OPTIME",'YYYY-MM')) ,TO_C
HAR("OPTIME",'YYYY-MM')from t_personallib_zj_10_080811 c where optime is not nullgroup by to_cha
r(optime,'YYYY-MM');
已解释。
SQL> select ACCESS_PREDICATES ,FILTER_PREDICATES from plan_table;
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE
10 TABLE ACCESS (FULL) OF 'PLAN_TABLE'

看来还得用跟踪一下用tkprof看看

sql执行计划请教 谢谢了

sql执行计划请教谢谢了