视图 left join 一个千万级别的表查询非常慢已建立索引

select *
from (select sso.pk_org,
sso.pk_dept,
sso.pk_customer,
sso.vbillcode,
sso.payterm,
sso.ntotalmoney,
sso.origdepositmny,
sso.depositmny,
sso.deductrule,
sso.deviverymny,
sso.verifydeliverymny,
sso.depositbalance,
recbill.gatheringmny,
'1001A6100000000JYKK4' exchgtype
from v_depositloan_hkzh sso

      left outer join (select /*+ index ( flow I_SO_ACCOUNTMNY_FLOW_REPORT ) */
                       sum(nvl(flow.nmny, 0)) +
                       sum(nvl(flow.nestarmny, 0)) +
                       sum(nvl(flow.narmny, 0)) -
                       sum(nvl(flow.nnoverifymny, 0)) gatheringmny,
                       flow.csaleorgid pk_org,
                       flow.cdeptid,
                       flow.ccustomerid,
                       flow.pk_org fir_dept
                        from so_accountmny_flow flow
                       where flow.dr = 0
                         and flow.csaleorgid is not null
                         and flow.cdeptid is not null
                         and flow.ccustomerid is not null
                         and flow.pk_org is not null
                       group by flow.csaleorgid,
                                flow.cdeptid,
                                flow.ccustomerid,
                                flow.pk_org) recbill
        on recbill.fir_dept = sso.vdef94
       and recbill.pk_org = sso.pk_org
       and recbill.cdeptid = sso.pk_dept
       and recbill.ccustomerid = sso.pk_customer) aa

where 1 = 1

上千万的数据进行左连接，这怎么也快不了吧......

对于硬件配置较好的oracle数据库来说,千万行真不是什么问题。
一段段拆开来看吧。

1. 首先是v_depositloan_hkzh 这个视图,里面的记录数和逻辑复杂度是怎样的？
2. 关联的这个子查询查询后得到的记录数是多少？单独执行效率怎么样？
3. 中间加的这个hint索引没有什么意义,还不如直接加并行hint

   /*+parallel (flow,12) */
   

4. 实际应用中的查询条件是什么？应用是否可以修改成不使用视图,而是直接用这个sql,然后把查询条件写进子查询里？

以上是单从逻辑上来分析,另外,oracle21c支持一些新特性,比如sql宏,可以让视图外的查询条件写到视图内的子查询中,但NC系统估计是不会用这个oracle版本的

【ORACLE】21版本新特性之SQL宏(SQL MACROS)的分析_DarkAthena的博客-CSDN博客 前言在21c发布后，很多文章中都提到了SQL宏，但看到的人们大多都有个疑问，这个SQL宏看上去和一般的函数没什么区别，为什么还要重点拿出来说？我们先看看ORACLE官方是怎么说的https://docs.oracle.com/en/database/oracle/oracle-database/21/nfcon/sql-macros-282902288.htmlYou can create SQL Macros (SQM) to factor out common SQL expressions https://darkathena.blog.csdn.net/article/details/121412432

我有两点疑问，从你的select * 来看，你的左连空值并没有任何处理，所以不知道你左连的意义是什么。如果可能的话，改成内连。

select 
sso.pk_org,
sso.pk_dept,
sso.pk_customer,
sso.vbillcode,
sso.payterm,
sso.ntotalmoney,
sso.origdepositmny,
sso.depositmny,
sso.deductrule,
sso.deviverymny,
sso.verifydeliverymny,
sso.depositbalance,
sum(nvl(flow.nmny, 0)) + sum(nvl(flow.nestarmny, 0)) + sum(nvl(flow.narmny, 0)) - sum(nvl(flow.nnoverifymny, 0)) gatheringmny,
'1001A6100000000JYKK4' exchgtype
from 
v_depositloan_hkzh sso,
so_accountmny_flow flow
where
flow.pk_org = sso.vdef94
and flow.csaleorgid = sso.pk_org
and flow.cdeptid = sso.pk_dept
and flow.ccustomerid = sso.pk_customer
group by 
sso.pk_org,
sso.pk_dept,
sso.pk_customer,
sso.vbillcode,
sso.payterm,
sso.ntotalmoney,
sso.origdepositmny,
sso.depositmny,
sso.deductrule,
sso.deviverymny,
sso.verifydeliverymny,
sso.depositbalance
;