数据库:oralce9i
查询语句:select * from System_Log a where
a.log_Date >= to_date('2010-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.log_Date <= to_date('2010-07-29 09:37:00','yyyy-mm-dd hh24:mi:ss')
and a.member_Code='00000000' order by a.log_Date asc 数据库中建了2个用户test1,test2,2个用户的表结构完全一致。System_Log的记录分别为19w,13w.
查询速度test1:30秒,test2:1s,为什么?
查询语句:select * from System_Log a where
a.log_Date >= to_date('2010-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.log_Date <= to_date('2010-07-29 09:37:00','yyyy-mm-dd hh24:mi:ss')
and a.member_Code='00000000' order by a.log_Date asc 数据库中建了2个用户test1,test2,2个用户的表结构完全一致。System_Log的记录分别为19w,13w.
查询速度test1:30秒,test2:1s,为什么?
set timing on你要执行的sql,贴出执行任务,让大家帮你看看。
ORA-12560: TNS: 协议适配器错误
这个改成
sqlplus 用户名@sid/密码 as sysdba
2 a.log_Date >= to_date('2010-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
3 and a.log_Date <= to_date('2010-07-29 09:37:00','yyyy-mm-dd hh24:mi:ss')
4 and a.member_Code='00000000' order by a.log_Date asc; COUNT(*)
----------
34974
test1
已用时间: 00: 00: 14.73执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'SYSTEM_LOG'
3 2 INDEX (RANGE SCAN) OF 'IX_SYSTEM_LOG_1' (NON-UNIQUE)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
23471 consistent gets
11025 physical reads
0 redo size
378 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedtest2
已用时间: 00: 00: 00.68执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=357 Card=1 Bytes=17)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'SYSTEM_LOG' (Cost=357 Card=1 Byt
es=17)统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4949 consistent gets
4917 physical reads
0 redo size
378 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
分析索引
analyze index t_idx validate structure;
select name,lf_rows from index_stats;
A如下
NAME LF_ROWS
------------------------------ ----------
IX_SYSTEM_LOG_1 184299
select count(*) from system_log;
COUNT(*)
----------
184242
B如下
NAME LF_ROWS
------------------------------ ----------
IX_SYSTEM_LOG_1 140882
select count(*) from system_log;
COUNT(*)
----------
140882