set linesize 180 spool slot_plan_out.lst set echo on; create table slot ( id varchar2 (40) not null, ship_date date, type number(1), start_time varchar2 (20), end_time varchar2 (20), store varchar2 (20), post varchar2 (20), fragment varchar2 (20), state varchar2 (20), slot_group number (10), constraint slot_pk primary key ( id ) ); explain plan for select s.type,s.slot_group,s.ship_date,s.start_time,s.end_time,count(id) ord_cnt from slot s where 1 = 1 and s.state in ( 'submitted' ) and s.ship_date >= to_date('20130213', 'yyyymmdd') and s.ship_date <= to_date('20130219', 'yyyymmdd') and s.type = '0' and s.slot_group = '2'and s.fragment='st180l' group by s.type, s.slot_group, s.ship_date, s.start_time, s.end_time order by s.ship_date, s.start_time,s.end_time; set echo off; @?/rdbms/admin/utlxpls.sql set echo on; create index slot_idx1 on slot(fragment); --create index slot_idx2 on slot(fragment, ship_date); --create index slot_idx3 on slot(ship_date, fragment); explain plan for select s.type,s.slot_group,s.ship_date,s.start_time,s.end_time,count(id) ord_cnt from slot s where 1 = 1 and s.state in ( 'submitted' ) and s.ship_date >= to_date('20130213', 'yyyymmdd') and s.ship_date <= to_date('20130219', 'yyyymmdd') and s.type = '0' and s.slot_group = '2'and s.fragment='st180l' group by s.type, s.slot_group, s.ship_date, s.start_time, s.end_time order by s.ship_date, s.start_time,s.end_time; set echo off; @?/rdbms/admin/utlxpls.sql spool off;