把你的 select min(start_date),max(end_date) from datatable where intodatabase_date>=start_indate and intodatabase_date<end_indate; 执行计划贴出来看看,看看它用没用到你加的索引!
前几天我在datatable的dataid,pid,cid,intodatabase_date上建了组合索引,查询速度很慢, 每个查询要三分钟左右,跟踪显示优化器选用了组合索引,昨天我分析了表datatable后又在intodatabase_date上建了单独索引datatable_indate ,速度显著加快,执行计划如下:-----第一种查询--------- SELECT MIN(START_DATE),MAX(END_DATE),COUNT(*) FROM datatable PARTITION(Y2005M03) WHERE dataID=1 AND intodatabase_date>='2005-03-10 18:05:00' AND intodatabase_date<'2005-03-11 00:00:00'MIN(START_DATE) MAX(END_DATE) COUNT(*) ------------------- ------------------- ---------- 2005-03-10 16:11:00 2005-03-10 23:58:00 2280已用时间: 00: 00: 00.35Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=24) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'DATATABLE' (Cost=12 Card=6 Bytes=1 44) 3 2 INDEX (RANGE SCAN) OF 'DATATABLE_INDATE' (NON-UNIQUE) (Cost=4 Card=6) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 815 consistent gets 0 physical reads 0 redo size 497 bytes sent via SQL*Net to client 425 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed-----第二种查询--------- SELECT MIN(start_date),MAX(end_date),COUNT(*) FROM datatable PARTITION(Y2005M03) WHERE dataID=1 AND PID='01' AND intodatabase_date>='2005-03-10 18:05:00' AND intodatabase_date<'2005-03-11 00:00:00'MIN(START_DATE) MAX(END_DATE) COUNT(*) ------------------- ------------------- ---------- 2005-03-10 16:11:00 2005-03-10 23:58:00 1782已用时间: 00: 00: 00.03Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=26) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'DATATABLE' (Cost=12 Card=2 Bytes=5 2) 3 2 INDEX (RANGE SCAN) OF 'DATATABLE_INDATE' (NON-UNIQUE) (Cost=4 Card=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 815 consistent gets 0 physical reads 0 redo size 497 bytes sent via SQL*Net to client 425 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed-----第三种查询--------- SELECT MIN(start_date),MAX(end_date),COUNT(*) FROM datatable PARTITION(Y2005M03) WHERE dataID=1 AND PID='01' AND intodatabase_date>='2005-03-10 18:05:00' AND intodatabase_date<'2005-03-11 00:00:00'MIN(START_DATE) MAX(END_DATE) COUNT(*) ------------------- ------------------- ---------- 2005-03-10 17:12:00 2005-03-10 23:58:00 175已用时间: 00: 00: 00.81Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=28) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'DATATABLE_STATE' (Cost=12 Card=1 Bytes=2 8) 3 2 INDEX (RANGE SCAN) OF 'DATATABLE_INDATE' (NON-UNIQUE) (Cost=4 Card=1) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 815 consistent gets 0 physical reads 0 redo size 497 bytes sent via SQL*Net to client 425 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed看来在这样的查询种组合索引性能不如单独索引,各位老大,不知我这样理解对么?
select min(start_date),max(end_date)
from datatable
where intodatabase_date>=start_indate
and intodatabase_date<end_indate;
执行计划贴出来看看,看看它用没用到你加的索引!
每个查询要三分钟左右,跟踪显示优化器选用了组合索引,昨天我分析了表datatable后又在intodatabase_date上建了单独索引datatable_indate
,速度显著加快,执行计划如下:-----第一种查询---------
SELECT MIN(START_DATE),MAX(END_DATE),COUNT(*)
FROM datatable PARTITION(Y2005M03)
WHERE dataID=1
AND intodatabase_date>='2005-03-10 18:05:00'
AND intodatabase_date<'2005-03-11 00:00:00'MIN(START_DATE) MAX(END_DATE) COUNT(*)
------------------- ------------------- ----------
2005-03-10 16:11:00 2005-03-10 23:58:00 2280已用时间: 00: 00: 00.35Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=24)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'DATATABLE' (Cost=12 Card=6 Bytes=1
44) 3 2 INDEX (RANGE SCAN) OF 'DATATABLE_INDATE' (NON-UNIQUE) (Cost=4 Card=6)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
815 consistent gets
0 physical reads
0 redo size
497 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed-----第二种查询---------
SELECT MIN(start_date),MAX(end_date),COUNT(*)
FROM datatable PARTITION(Y2005M03)
WHERE dataID=1
AND PID='01'
AND intodatabase_date>='2005-03-10 18:05:00'
AND intodatabase_date<'2005-03-11 00:00:00'MIN(START_DATE) MAX(END_DATE) COUNT(*)
------------------- ------------------- ----------
2005-03-10 16:11:00 2005-03-10 23:58:00 1782已用时间: 00: 00: 00.03Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=26)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'DATATABLE' (Cost=12 Card=2 Bytes=5
2) 3 2 INDEX (RANGE SCAN) OF 'DATATABLE_INDATE' (NON-UNIQUE) (Cost=4 Card=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
815 consistent gets
0 physical reads
0 redo size
497 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed-----第三种查询---------
SELECT MIN(start_date),MAX(end_date),COUNT(*)
FROM datatable PARTITION(Y2005M03)
WHERE dataID=1
AND PID='01'
AND intodatabase_date>='2005-03-10 18:05:00'
AND intodatabase_date<'2005-03-11 00:00:00'MIN(START_DATE) MAX(END_DATE) COUNT(*)
------------------- ------------------- ----------
2005-03-10 17:12:00 2005-03-10 23:58:00 175已用时间: 00: 00: 00.81Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=28)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'DATATABLE_STATE' (Cost=12 Card=1 Bytes=2
8) 3 2 INDEX (RANGE SCAN) OF 'DATATABLE_INDATE' (NON-UNIQUE) (Cost=4 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
815 consistent gets
0 physical reads
0 redo size
497 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed看来在这样的查询种组合索引性能不如单独索引,各位老大,不知我这样理解对么?