06:00:02 SQL> set echo on; 06:00:02 SQL> 06:00:02 SQL> -- 06:00:02 SQL> --create table slot 06:00:02 SQL> -- ( 06:00:02 SQL> -- id varchar2 (40) not null, 06:00:02 SQL> -- ship_date date, 06:00:02 SQL> -- type number(1), 06:00:02 SQL> -- start_time varchar2 (20), 06:00:02 SQL> -- end_time varchar2 (20), 06:00:02 SQL> -- store varchar2 (20), 06:00:02 SQL> -- post varchar2 (20), 06:00:02 SQL> -- fragment varchar2 (20), 06:00:02 SQL> -- state varchar2 (20), 06:00:02 SQL> -- slot_group number (10), 06:00:02 SQL> -- constraint slot_pk primary key ( id ) 06:00:02 SQL> --); 06:00:02 SQL> drop index slot_idx1 ; Index dropped. Elapsed: 00:00:00.04 06:00:02 SQL> drop index slot_idx2 ; Index dropped. Elapsed: 00:00:00.03 06:00:02 SQL> drop index slot_idx3 ; Index dropped. Elapsed: 00:00:00.03 06:00:02 SQL> 06:00:02 SQL> explain plan for 06:00:02 2 select s.type,s.slot_group,s.ship_date,s.start_time,s.end_time,count(id) ord_cnt 06:00:02 3 from slot s 06:00:02 4 where 1 = 1 and s.state in ( 'SUBMITTED' ) 06:00:02 5 and s.ship_date >= to_date('20120213', 'yyyymmdd') 06:00:02 6 and s.ship_date <= to_date('20130219', 'yyyymmdd') 06:00:02 7 and s.type = '0' and s.slot_group = '2'and s.fragment='SS117QL' 06:00:02 8 group by s.type, 06:00:02 9 s.slot_group, s.ship_date, 06:00:02 10 s.start_time, s.end_time 06:00:02 11 order by s.ship_date, s.start_time,s.end_time; Explained. Elapsed: 00:00:00.02 06:00:02 SQL> 06:00:02 SQL> set echo off; PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1682551985 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 93 | 18809 (2)| 00:02:47 | | 1 | SORT GROUP BY | | 1 | 93 | 18809 (2)| 00:02:47 | |* 2 | TABLE ACCESS FULL| SLOT | 1 | 93 | 18808 (2)| 00:02:47 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("S"."STATE"='SUBMITTED' AND "S"."TYPE"=0 AND "S"."SLOT_GROUP"=2 AND "S"."FRAGMENT"='SS117QL' AND "S"."SHIP_DATE">=TO_DATE(' 2012-02-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "S"."SHIP_DATE"<=TO_DATE(' 2013-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) Note ----- - dynamic sampling used for this statement 22 rows selected. Elapsed: 00:00:00.01 06:00:02 SQL> 06:00:02 SQL> create index slot_idx1 on slot(fragment); Index created. Elapsed: 00:00:23.58 06:00:26 SQL> 06:00:26 SQL> create index slot_idx2 on slot(ship_date); Index created. Elapsed: 00:00:19.10 06:00:45 SQL> 06:00:45 SQL> create index slot_idx3 on slot(ship_date, fragment); Index created. Elapsed: 00:00:24.87 06:01:10 SQL> 06:01:10 SQL> explain plan for 06:01:10 2 select s.type,s.slot_group,s.ship_date,s.start_time,s.end_time,count(id) ord_cnt 06:01:10 3 from slot s 06:01:10 4 where 1 = 1 and s.state in ( 'SUBMITTED' ) 06:01:10 5 and s.ship_date >= to_date('20120213', 'yyyymmdd') 06:01:10 6 and s.ship_date <= to_date('20130219', 'yyyymmdd') 06:01:10 7 and s.type = '0' and s.slot_group = '2'and s.fragment='SS117QL' 06:01:10 8 group by s.type, 06:01:10 9 s.slot_group, s.ship_date, 06:01:10 10 s.start_time, s.end_time 06:01:10 11 order by s.ship_date, s.start_time,s.end_time; Explained. Elapsed: 00:00:00.02 06:01:10 SQL> 06:01:10 SQL> 06:01:10 SQL> set echo off; PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2722768918 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 93 | 177 (1)| 00:00:02 | | 1 | SORT GROUP BY | | 1 | 93 | 177 (1)| 00:00:02 | |* 2 | TABLE ACCESS BY INDEX ROWID| SLOT | 1 | 93 | 176 (0)| 00:00:02 | |* 3 | INDEX RANGE SCAN | SLOT_IDX1 | 930 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("S"."STATE"='SUBMITTED' AND "S"."TYPE"=0 AND "S"."SLOT_GROUP"=2 AND "S"."SHIP_DATE">=TO_DATE(' 2012-02-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "S"."SHIP_DATE"<=TO_DATE(' 2013-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 3 - access("S"."FRAGMENT"='SS117QL') Note ----- - dynamic sampling used for this statement 22 rows selected. Elapsed: 00:00:00.01 06:01:10 SQL> select (select count(*) From slot) table_rows 06:01:10 2 ,(select count(*) From slot s where s.state in ( 'SUBMITTED' )) state_rows 06:01:10 3 ,(select count(*) From slot s where s.ship_date >= to_date('20120213', 'yyyymmdd')) ship_date_greaterthan_rows 06:01:10 4 ,(select count(*) From slot s where s.ship_date <= to_date('20130219', 'yyyymmdd')) ship_date_lessthan_rows 06:01:10 5 ,(select count(*) From slot s where s.type = '0') type_rows 06:01:10 6 ,(select count(*) From slot s where s.slot_group = '2') slot_group_rows 06:01:10 7 ,(select count(*) From slot s where s.fragment='SS117QL') fragment_rows 06:01:10 8 from dual; TABLE_ROWS STATE_ROWS SHIP_DATE_GREATERTHAN_ROWS SHIP_DATE_LESSTHAN_ROWS TYPE_ROWS SLOT_GROUP_ROWS FRAGMENT_ROWS ---------- ---------- -------------------------- ----------------------- ---------- --------------- ------------- 7550003 18488 1755230 7549988 7548147 7104930 831 Elapsed: 00:00:20.63 06:01:30 SQL> spool off;