select a.node_id,a.dev_name,a.channel_id,a.during_time,a.pmean,a.omean
from cfmc_3mon a,(select node_id,max(during_time) mt from cfmc_3mon b group by node_id) b
where a.node_id=b.node_id and a.during_time=b.mt建几个?
from cfmc_3mon a,(select node_id,max(during_time) mt from cfmc_3mon b group by node_id) b
where a.node_id=b.node_id and a.during_time=b.mt建几个?
create index a_index2 on cfmc_3mon(during_time)或这样:
create index a_index on cfmc_3mon(node_id,during_time)用分析语句,测试那种方式可取。
在sqlplus中set autotrace on看看哪种好一点就用哪种
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 VIEW
3 2 SORT (GROUP BY)
4 3 TABLE ACCESS (FULL) OF 'CFMC_TEST_SNDX'
5 1 TABLE ACCESS (BY INDEX ROWID) OF 'CFMC_TEST_SNDX'
6 5 AND-EQUAL
7 6 INDEX (RANGE SCAN) OF 'CFMC_TEST_INDEX_NODE' (NON-UN
IQUE) 8 6 INDEX (RANGE SCAN) OF 'CFMC_TEST_INDEX_DTIME' (NON-U
NIQUE)Statistics
----------------------------------------------------------
0 recursive calls
15 db block gets
997 consistent gets
0 physical reads
0 redo size
8858 bytes sent via SQL*Net to client
734 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
207 rows processed
建一个联合索引的情况:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 VIEW
3 2 SORT (GROUP BY)
4 3 TABLE ACCESS (FULL) OF 'CFMC_TEST_NDX'
5 1 TABLE ACCESS (BY INDEX ROWID) OF 'CFMC_TEST_NDX'
6 5 INDEX (RANGE SCAN) OF 'CFMC_TEST_INDEX' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
15 db block gets
832 consistent gets
23 physical reads
0 redo size
8962 bytes sent via SQL*Net to client
734 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
207 rows processed帮忙看看选哪个?
当我对其中4个字段建联合索引后,开始的时候比较慢,多查几次后就非常快了。比单独建索引和联合两个的都要快10%左右。