--- sql 1:
SELECT *
FROM (SELECT A.*
FROM (
select T1.*,U.User_Name
from(
select T.*,
I.BULLETIN_TITLE,
I.CREATE_DATE CALENDAR,
I.STATUS
from(
SELECT PK_ID,
READ_STATUS,
FK_BULLETION_ID,
FK_USER_ID
FROM T_BULLETIN_EVENT where FK_USER_ID = '1'
)T join T_BULLETIN_INFO I on I.PK_ID = T.FK_BULLETION_ID and I.STATUS = '0'
)T1 join T_SYS_USER U on T1.FK_USER_ID=U.USER_ID
) A ORDER BY A.READ_STATUS ASC, A.CALENDAR DESC
) B
WHERE Rownum >= 1
AND Rownum <= 10;
-- sql2:
SELECT *
FROM (SELECT ROWNUM R, A.*
FROM (SELECT T.PK_ID,
T.READ_STATUS,
T.FK_BULLETION_ID,
U.USER_NAME,
I.BULLETIN_TITLE,
I.CREATE_DATE CALENDAR,
I.STATUS
FROM T_BULLETIN_EVENT T, T_SYS_USER U, T_BULLETIN_INFO I
WHERE U.USER_ID = T.FK_USER_ID
AND I.PK_ID = T.FK_BULLETION_ID
AND I.STATUS = '0'
and T.FK_USER_ID = '1'
ORDER BY T.READ_STATUS ASC, I.CREATE_DATE DESC) A) B
WHERE R >= 1
AND R <= 10;我在执行计划里面看,两者的cost都是7!
问下,这2句sql,那个效率要高些啊?
SELECT *
FROM (SELECT A.*
FROM (
select T1.*,U.User_Name
from(
select T.*,
I.BULLETIN_TITLE,
I.CREATE_DATE CALENDAR,
I.STATUS
from(
SELECT PK_ID,
READ_STATUS,
FK_BULLETION_ID,
FK_USER_ID
FROM T_BULLETIN_EVENT where FK_USER_ID = '1'
)T join T_BULLETIN_INFO I on I.PK_ID = T.FK_BULLETION_ID and I.STATUS = '0'
)T1 join T_SYS_USER U on T1.FK_USER_ID=U.USER_ID
) A ORDER BY A.READ_STATUS ASC, A.CALENDAR DESC
) B
WHERE Rownum >= 1
AND Rownum <= 10;
-- sql2:
SELECT *
FROM (SELECT ROWNUM R, A.*
FROM (SELECT T.PK_ID,
T.READ_STATUS,
T.FK_BULLETION_ID,
U.USER_NAME,
I.BULLETIN_TITLE,
I.CREATE_DATE CALENDAR,
I.STATUS
FROM T_BULLETIN_EVENT T, T_SYS_USER U, T_BULLETIN_INFO I
WHERE U.USER_ID = T.FK_USER_ID
AND I.PK_ID = T.FK_BULLETION_ID
AND I.STATUS = '0'
and T.FK_USER_ID = '1'
ORDER BY T.READ_STATUS ASC, I.CREATE_DATE DESC) A) B
WHERE R >= 1
AND R <= 10;我在执行计划里面看,两者的cost都是7!
问下,这2句sql,那个效率要高些啊?
解决方案 »
- 有关oracle中表分区维护的问题,求高手解答
- oracle语句级触发器执行效率问题(在线等)
- 谁帮忙写个ORACLE的任务定制啊?
- oralce 11g连接
- orcale时间的处理
- 求助,一SQL问题,大侠帮忙,在线等
- 如何在9i中给新加的一个用户定义只有对一个视图只读的权限。我已添加了一个用户,只是不知怎么定义才能只有只读一个视图的权限
- 基础问题,Oracle XE 的 SID 和 TNS 是什么
- 急急急!求大神!排序 同organ_id、同user_id 的数据 只取一条
- oracle9i中不能显示汉字是什么问题???
- x varchar2(10):='abc';
- 谁能告诉我dbms_job的用法?
在plsql里面的执行计划里面看,两者的执行效率是cost值是一样的都是7.
select * from (
select * from (
select * from (
select * from tab)))和select * from tab
是一样的啊。只是从语句的可读性上,第二个更好。
用=的语法简洁
那2者的效率如何算呢?那个比较高,hebo2005平常用哪个啊?