Hello,
Tuning of join of large tables.
TABLE ROW COUNT
PEOPLE_MASTER => 67059
PROCESSED_TRXNS => 128302246
Query :
SELECT A.PEOPLE_NAME ,A.ADDRESS1 ,A.MOBILE_NO, A.FAX1 ,A.EMAIL ,A.PINCODE ,
max(B.TRXN_DATE) LAST_TRXN_DATE, cast(a.TIME_STAMP as date) DT_UPDATE
from PEOPLE_MASTER a , PROCESSED_TRXNS B
where a.PEOPLE_CODE=B.PEOPLE_CODE
GROUP BY A.PEOPLE_NAME ,A.ADDRESS1 ,A.MOBILE_NO, A.FAX1 ,A.EMAIL ,A.PINCODE ,a.TIME_STAMP
I have tried creating a materialized view but the refresh is also taking a lot of time , we need to refresh the data on daily basis as its a transaction table and it takes a lot of load on these tables.
Materialized view refresh script:
CREATE MATERIALIZED VIEW MVW_PEOPLE_INFO"
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PEOPLE_PORTAL"
BUILD IMMEDIATE USING INDEX REFRESH FORCE ON DEMAND
START WITH sysdate+0
NEXT TRUNC(sysdate) + 1
USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
Execution Plan :
Plan hash value: 1879885679
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 37612 | 5325K| | 1341K (1)| 04:28:15 |
| 1 | HASH GROUP BY | | 37612 | 5325K| 6552K| 1341K (1)| 04:28:15 |
|* 2 | HASH JOIN | | 37612 | 5325K| | 1340K (1)| 04:28:01 |
| 3 | VIEW | VW_GBC_5 | 37612 | 697K| | 1339K (1)| 04:27:53 |
| 4 | HASH GROUP BY | | 37612 | 661K| 3393M| 1339K (1)| 04:27:53 |
| 5 | TABLE ACCESS FULL| PROCESSED_TRXNS | 126M| 2169M| | 1080K (1)| 03:36:02 |
| 6 | TABLE ACCESS FULL | PEOPLE_MASTER | 66993 | 8243K| | 629 (1)| 00:00:08 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."PEOPLE_CODE"="ITEM_1")