I have a table t containing three fields accountno, tran_date, amount and amount is always > 0.
.
There are many records for each accountno. I want to select all the accountnos where the sum(amount) > 100.
The simple query is like this
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
that as soon as the sum of some of the records is > 100, it should not process further that accountno, is it possible ?
A group by with a having clause is the best way to write this query.
But there is a trick available:
Materialized views!
These store the results of a query. Provided each account has "many" rows in the table this can significantly reduce the number of rows you process. And, everything else being equal, less rows => faster query.
The best part is:
You don't need to change your SQL :)
Provided you have query rewrite enabled, Oracle Database can use this automagically. Notice how the plan for the query has "MAT_VIEW REWRITE ACCESS FULL" and only processes 43 rows (A-rows column):
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)Further, if you expect the number of accounts having a sum > 100 to be "small" you can create an index on this column in the MV. And the database can use that.
But to do this the MV has to be "fresh". That is, the data stored in it must exactly match the data in the base table.
Add one row and there's a mismatch. So it accesses the table again:
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)Notice how the full scan of T in line 3 accesses 73,096 rows? That's going to take a wee bit more effort to process than 43...
So you need to keep it up-to-date. Ideally by defining materialized view logs. And making it "fast refresh on commit".
Or, if you're lucky enough to be on 12.2 create a real time materialized view with the "enable on query computation" clause:
https://blogs.oracle.com/sql/12-things-developers-will-love-about-oracle-database-12c-release-2#real-time-mv