Oracle 如何通过有

我有一个表t包含三个字段accountno，tran_date，金额和金额始终> 0。
。
There are many records for each accountno。 I want to select all the accountnos where the sum(amount) > 100。

简单的查询是这样的

select accountno from t group by accountno having sum(amount) > 100;

This query is taking time , as there are many records for each account。 I want to optimise this query , in such a way
一旦某些记录的总和> 100，就不应进一步处理该帐户，是否可能？

带有whaving子句的group by是编写此查询的最佳方法。

但是有一个技巧可用:

物化视图!

这些存储查询的结果。如果每个帐户在表中有 “许多” 行，这可以大大减少您处理的行数。而且，其他一切都相等，更少的行 => 更快的查询。

最棒的是:

您不需要更改您的SQL :)

如果您启用了查询重写，Oracle数据库可以自动使用它。请注意查询的计划如何具有 “MAT_VIEW REWRITE ACCESS FULL” 并且仅处理43行 (A行列):

create table t as
  select owner accountno,
         trunc( created ) as tran_date,
         object_id as amount
  from   all_objects;

create materialized view mv 
  enable query rewrite as 
  select accountno,
         count(*) ct,
         sum( amount ) sm
  from   t
  group by accountno;
  
set serveroutput off
select /*+ gather_plan_statistics */accountno
from   t
group by accountno 
having sum( amount ) > 1000;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST'));

PLAN_TABLE_OUTPUT                                                          
SQL_ID  74naf2w6wvqh1, child number 0                                      
-------------------------------------                                      
select /*+ gather_plan_statistics */accountno from   t group by            
accountno  having sum( amount ) > 1000                                     
                                                                           
Plan hash value: 572630632                                                 
                                                                           
------------------------------------------------------------------------   
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   
------------------------------------------------------------------------   
|   0 | SELECT STATEMENT             |      |      1 |        |     43 |   
|*  1 |  MAT_VIEW REWRITE ACCESS FULL| MV   |      1 |     43 |     43 |   
------------------------------------------------------------------------   
                                                                           
Predicate Information (identified by operation id):                        
---------------------------------------------------                        
                                                                           
   1 - filter("MV"."SM">1000)

此外，如果您期望具有总和> 100的帐户数量为 “小”，则可以在MV中的此列上创建索引。数据库可以使用它。

但是要做到这一点，MV必须 “新鲜”。也就是说，存储在其中的数据必须与基表中的数据完全匹配。

添加一行，就会出现不匹配的情况。所以它再次访问该表:

insert into t values ('CHRIS', sysdate, 1);
commit;

select /*+ gather_plan_statistics */accountno
from   t
group by accountno 
having sum( amount ) > 1000;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST'));

PLAN_TABLE_OUTPUT                                                  
SQL_ID  74naf2w6wvqh1, child number 1                              
-------------------------------------                              
select /*+ gather_plan_statistics */accountno from   t group by    
accountno  having sum( amount ) > 1000                             
                                                                   
Plan hash value: 1381620754                                        
                                                                   
---------------------------------------------------------------    
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |    
---------------------------------------------------------------    
|   0 | SELECT STATEMENT    |      |      1 |        |     43 |    
|*  1 |  FILTER             |      |      1 |        |     43 |    
|   2 |   HASH GROUP BY     |      |      1 |      1 |     43 |    
|   3 |    TABLE ACCESS FULL| T    |      1 |  73095 |  73096 |    
---------------------------------------------------------------    
                                                                   
Predicate Information (identified by operation id):                
---------------------------------------------------                
                                                                   
   1 - filter(SUM("AMOUNT")>1000)

注意第3行中T的全扫描如何访问73,096行？这将需要比43更多的精力来处理...

所以你需要保持最新。理想情况下，通过定义物化视图日志。并使其 “提交时快速刷新”。

或者，如果您有幸12.2使用 “启用查询计算” 子句创建实时具体化视图:

https://blogs.oracle.com/sql/12-things-developers-will-love-about-oracle-database-12c-release-2#real-time-mv