一个SQL 是否有优化的可能

Question

表：bank_msg_info字段：message_title（标题）,create_date（日期）,message_type_id（类型）
现在想得到：message_type_id（类型）分别为 1,2,3,4,5,6 同时每种类型按create_date（日期）倒序排序后的前6条记录这是自己写的一个SQL:
select message_title,create_date,message_type_id  from (select * from bank_msg_info where  message_type_id='1' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id  from (select * from bank_msg_info where  message_type_id='2' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id  from (select * from bank_msg_info where  message_type_id='3' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id  from (select * from bank_msg_info where  message_type_id='4' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id  from (select * from bank_msg_info where  message_type_id='5' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id  from (select * from bank_msg_info where  message_type_id='6' order by create_date desc) where rownum<=6
 
貌似效率有点低，忘各位坛友赐教 请上SQL

Answer 1

select b.message_title,b.create_date,b.message_type_id
from
(select a.*,
        row_number() over(partition by message_type_id order by create_date desc)rn
 from bank_msg_info a
 where message_type_id in ('1','2','3','4','5','6')
)b
where rn<=6

Answer 2

嗯 3Q10000条记录的时候 测试了两个SQL，貌似效率没有提升使用union all  0.047秒使用火龙果的SQL 0.092秒

Answer 3

  哥们  还测试过来个SQL的速度啊？~

Answer 4

--建索引
create index idx_id on bank_msg_info(message_type_id)select message_title,create_date,message_type_id
from
(select message_title,create_date,message_type_id,
row_number() over(partition by message_type_id order by create_date desc,rowid) rn
from bank_msg_info
where  message_type_id in('1','2','3','4','5','6')
) a
where a.rn<7

Answer 5

message_type_id 有索引的我再加点数据进行测试 

Answer 6

alter table bank_msg_info storage(buffer_pool default)

Answer 7

message_type_id，create_date有沒建索引，沒有就建下索引！樓主原本的方法比火龙果的要快~

Answer 8

测试结果：bank_msg_info 表记录：6万条，message_type_id 有索引：select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='1' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='2' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='3' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='4' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='5' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='6' order by create_date desc) where rownum<=6耗时：
0.172秒-----------------------------------------------------------------------------SELECT x.*
  FROM (SELECT message_title, create_date, message_type_id,
  row_number() OVER(PARTITION BY message_type_id ORDER BY create_date desc) AS rn
  FROM bank_msg_info where message_type_id in('1','2','3','4','5','6') ) x
 WHERE x.rn <= 6;耗时：
0.45秒

Answer 9

运行你的SQL后 测试结果：bank_msg_info 表记录：6万条，message_type_id 有索引：select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='1' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='2' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='3' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='4' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='5' order by create_date desc) where rownum<=6
union all
select message_title,create_date,message_type_id from (select * from bank_msg_info where message_type_id='6' order by create_date desc) where rownum<=6耗时：
0.141秒-----------------------------------------------------------------------------SELECT x.*
  FROM (SELECT message_title, create_date, message_type_id,
  row_number() OVER(PARTITION BY message_type_id ORDER BY create_date desc) AS rn
  FROM bank_msg_info where message_type_id in('1','2','3','4','5','6') ) x
 WHERE x.rn <= 6;耗时：
0.40秒

Answer 10

ORDER BY create_date desc,rowidalter table bank_msg_info storage(buffer_pool default)