两条SQL语句哪一条效率高一些 , 还有没有其它写法
select count(distinct user_id)
from event_user
where
event_user_confirmation.confirmed_date <= sysdate
select count(1)
from (
select distinct user_id from event_user_confirmation
where
event_user_confirmation.confirmed_date <= sysdate
)
select count(distinct user_id)
from event_user
where
event_user_confirmation.confirmed_date <= sysdate
select count(1)
from (
select distinct user_id from event_user_confirmation
where
event_user_confirmation.confirmed_date <= sysdate
)
select count(distinct user_id)
from event_user
where
event_user_confirmation.confirmed_date <= sysdate 第二条多一个子查询,而且还有distinct关键字,有distinct关键字会慢很多的
from event_user
where event_user_confirmation.confirmed_date <= sysdate
group by user_id
有个奇怪的现象第一条语句会排序。
select count( distinct t.aae1140) from aaa t where t.aae1140=1;----------------------------------------------------------
Plan hash value: 1129785135---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
| 1 | SORT GROUP BY | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| AAA | 1 | 3 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter("T"."AAE1140"=1)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
424 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed