Oracle11g执行分页查询语句,其中Rownum<=10分页查询缓慢没有返回结果。

CapJes 发布于 2018/11/30 11:34
阅读 114
收藏 1

在维护老系统中,分页查询语句,当rownum<=10时,出现执行缓慢,长时间无法返回结果集,rownum<=1,2,3,4,5,6,7,8,9,10都尝试了,都会出现查询缓慢,没有返回结果集,并且没有报错。rownum<=11没问题,rownum<=100,1000这些都没问题。

语句如下,不知道是数据库问题,还是SQL语句写法有问题:

select
        *
    from
        ( select
            ua.id,
            max(ua.projectname) as projectname,
            max(ua.applyType) as applyType,
            max(ua.applytime) as applytime,
            max(ua.startTime) as startTime,
            max(ua.endTime) as endTime,
            max(su.username) as namess,
            max(di.serialnumberx) as serialnumberx,
            max(ua.contructionaddres) as contructionaddres,
            max(ua.workState) as workState ,
            max(su.id) as userid
        from
            unlocking_Apply ua
        left join
            sys_user su
                on ua.applyuserId = su.id
        left join
            unlock_facilities uf
                on uf.applyid = ua.id
        left join
            device_info di
                on di.facilitiesid = uf.facilitiesid
        left join
            openLockRecord ord
                on ord.facilitiesid = uf.facilitiesid
        where
            1=1
            AND su.id in (
                select
                    userId
                from
                    user_organizationa uoa
                where
                    uoa.organizationaid in (
                        select
                            soa.id
                        from
                            sys_organizationa soa  start with  soa.organizationalcode= (select
                                so.organizationalcode
                            from
                                sys_user su
                            inner join
                                user_organizationa uo
                                    on su.id=uo.userid
                            inner join
                                sys_organizationa so
                                    on so.id=uo.organizationaid
                            where
                                su.id='22cf1b12-5642-40b4-9906-de6bd532d215')  connect
                        by
                            prior soa.organizationalcode =soa.parentorganizationalcoded )
                    )
                    and workState = 1
                group by
                    ua.id
                order by
                    applyTime desc )
                where
                    rownum <= ?

 

加载中
0
CapJes
CapJes

现在都没人研究Oracle数据库了?

返回顶部
顶部