select *
from (select OBJUID,
XMMC,
dt_broadcast,
ggtype,
Row_Number() over(order by dt_broadcast desc) rownum_
from (select OBJUID, na_item_name XMMC, dt_broadcast, 1 ggtype
from db_perambulate_item_broadcast t
union all
select OBJUID, na_mine_name XMMC, dt_broadcast, 2 ggtype
from db_mining_apply_broadcast t))
where rownum <7这个数据正常执行应该是0.3秒这样的,但是系统执行都要2秒多,要怎么优化呢
dt_broadcast desc 这个字段是时间类型的,没有NULL值,用了位图索引
db_perambulate_item_broadcast 67840条数据
db_mining_apply_broadcast 168428条数据
from (select OBJUID,
XMMC,
dt_broadcast,
ggtype,
Row_Number() over(order by dt_broadcast desc) rownum_
from (select OBJUID, na_item_name XMMC, dt_broadcast, 1 ggtype
from db_perambulate_item_broadcast t
union all
select OBJUID, na_mine_name XMMC, dt_broadcast, 2 ggtype
from db_mining_apply_broadcast t))
where rownum <7这个数据正常执行应该是0.3秒这样的,但是系统执行都要2秒多,要怎么优化呢
dt_broadcast desc 这个字段是时间类型的,没有NULL值,用了位图索引
db_perambulate_item_broadcast 67840条数据
db_mining_apply_broadcast 168428条数据
CREATE TABLE test AS
SELECT *
FROM (SELECT OBJUID,
XMMC,
DT_BROADCAST,
GGTYPE,
ROW_NUMBER() OVER(ORDER BY DT_BROADCAST DESC) ROWNUM_
FROM (SELECT OBJUID, NA_ITEM_NAME XMMC, DT_BROADCAST, 1 GGTYPE
FROM DB_PERAMBULATE_ITEM_BROADCAST T
UNION ALL
SELECT OBJUID, NA_MINE_NAME XMMC, DT_BROADCAST, 2 GGTYPE
FROM DB_MINING_APPLY_BROADCAST T))
WHERE ROWNUM < 7;
SELECT * FROM test;
直接查询结果会快些。
如果你的应用程序没有传递参数,或者更改where部分的话。
我觉得不应该比在sqlplus里面慢。慢的原因是否与,应用程序读取数据有关呢?
--不知道按照ggtype分组,再按照dt_broadcast降序排列能不能提高你是执行速度!
select *
from (select OBJUID,
XMMC,
dt_broadcast,
ggtype,
Row_Number() over( partition by ggtype order by dt_broadcast desc) rownum_
from (select OBJUID,
na_item_name XMMC,
dt_broadcast,
1 ggtype
from db_perambulate_item_broadcast t
union all
select OBJUID,
na_mine_name XMMC,
dt_broadcast,
2 ggtype
from db_mining_apply_broadcast t))
where rownum <7;