信息如下:
SQL> select * from V$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
SQL> select count(*) from logbook;
COUNT(*)
----------
12057711
SQL> desc logbook;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
LB_ID NOT NULL NUMBER(20)
US_ID NOT NULL NUMBER(20)
TL_ID NUMBER(20)
UT_ID NUMBER(20)
ACCESSIP VARCHAR2(20)
EVENT NOT NULL NUMBER(3)
EVENT_TIME1 NOT NULL DATE
EVENT_TIME2 NOT NULL DATE
SQL> create table logbookpt
partition by range (EVENT_TIME1)
(
partition lb0 values less than (TO_DATE('2005-01-01','yyyy-mm-dd'))
tablespace users,
partition lb1 values less than (TO_DATE('2005-02-01','yyyy-mm-dd'))
tablespace users,
partition lb2 values less than (TO_DATE('2005-03-01','yyyy-mm-dd'))
tablespace users,
partition lb3 values less than (TO_DATE('2005-04-01','yyyy-mm-dd'))
tablespace users,
partition lb4 values less than (TO_DATE('2005-05-01','yyyy-mm-dd'))
tablespace users,
partition lb5 values less than (TO_DATE('2005-06-01','yyyy-mm-dd'))
tablespace users,
partition lb6 values less than (TO_DATE('2005-07-01','yyyy-mm-dd'))
tablespace users,
partition lb7 values less than (TO_DATE('2005-08-01','yyyy-mm-dd'))
tablespace users,
partition lb8 values less than (TO_DATE('2005-09-01','yyyy-mm-dd'))
tablespace users,
partition lb9 values less than (to_date('2006-01-01','yyyy-mm-dd'))
tablespace users
)
as
select * from logbook;
SQL> create index logbookpt_event1_idx on logbookpt(event_time1) local
tablespace indx;
SQL> create index logpt_usid_idx on logbookpt(us_id)
tablespace indx;
SQL> create index logpt_event_idx on logbookpt(event)
tablespace indx;
SQL> select count(*) sum1, count(distinct tg.user_id) sum2, sum(lb.event_time2-
lb.event_time1)*24 sum3, tg.group_id
2 from logbookpt partition(lb8) lb, tuser tu ,tuser_group tg
3 where lb.event = 3 and lb.us_id = tu.id and tu.id = tg.user_id
4 and tu.groupids like ',,9001,,9002,,19350,,19625,,%'
5 and event_time1 >= (to_date('2005-08-01','YYYY-MM-DD'))
6 and event_time1 <= (to_date('2005-08-02','YYYY-MM-DD')+1)
7 group by tg.group_id;
已选择6行。
已用时间: 00: 00: 02.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=190 Card=35 Bytes=33
95)
1 0 SORT (GROUP BY) (Cost=190 Card=35 Bytes=3395)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TUSER_GROUP' (Cost=2 C
ard=280689 Bytes=2245512)
3 2 NESTED LOOPS (Cost=184 Card=35 Bytes=3395)
4 3 NESTED LOOPS (Cost=114 Card=35 Bytes=3115)
5 4 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'LOGBOOKPT'
(Cost=79 Card=35 Bytes=1540)
6 5 INDEX (RANGE SCAN) OF 'LOGBOOKPT_EVENT1_IDX' (NO
N-UNIQUE) (Cost=2 Card=6260)
7 4 TABLE ACCESS (BY INDEX ROWID) OF 'TUSER' (Cost=1 C
ard=1 Bytes=45)
8 7 INDEX (UNIQUE SCAN) OF 'PK_TUSER' (UNIQUE)
9 3 INDEX (RANGE SCAN) OF 'TUSER_GROUP_USER_ID_IDX' (NON
-UNIQUE) (Cost=1 Card=1)
Statistics
----------------------------------------------------------
56 recursive calls
0 db block gets
262475 consistent gets
0 physical reads
0 redo size
770 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
6 rows processed
SQL> select count(*) sum1, count(distinct tg.user_id) sum2, sum(lb.event_time2
-lb.event_time1)*24 sum3, tg.group_id
2 from logbook lb, tuser tu ,tuser_group tg
3 where lb.event = 3 and lb.us_id = tu.id and tu.id = tg.user_id
4 and tu.groupids like ',,9001,,9002,,19350,,19625,,%'
5 and event_time1 >= (to_date('2005-08-01','YYYY-MM-DD'))
6 and event_time1 <= (to_date('2005-08-02','YYYY-MM-DD')+1)
7 group by tg.group_id;
已选择6行。
已用时间: 00: 00: 02.07
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=1 Bytes=74)
1 0 SORT (GROUP BY) (Cost=14 Card=1 Bytes=74)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TUSER_GROUP' (Cost=2 C
ard=280689 Bytes=2245512)
3 2 NESTED LOOPS (Cost=8 Card=1 Bytes=74)
4 3 NESTED LOOPS (Cost=6 Card=1 Bytes=66)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'LOGBOOK' (Cost=5
Card=1 Bytes=21)
6 5 INDEX (RANGE SCAN) OF 'LOGBOOK_EVENT_TIME1_2_IDX
' (NON-UNIQUE) (Cost=3 Card=2)
7 4 TABLE ACCESS (BY INDEX ROWID) OF 'TUSER' (Cost=1 C
ard=1 Bytes=45)
8 7 INDEX (UNIQUE SCAN) OF 'PK_TUSER' (UNIQUE)
9 3 INDEX (RANGE SCAN) OF 'TUSER_GROUP_USER_ID_IDX' (NON
-UNIQUE) (Cost=1 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
265100 consistent gets
0 physical reads
0 redo size
770 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed
大家可以看到性能上基本上没有什么差别???
请各位指点迷津,多谢!