Oracle Magazine, March/April 2019
Database Developer and DBA ETL The SQL statement to produce the data for the report was a simple aggregation query as shown in Listing 1 ORACLE MAGAZINE MARCH APRIL 2019 102 Listing 1 Customer transaction query SQL select CUSTOMER_ NUM 2 max TRANS_ TIMESTAMP LAST_ TS 3 from CUSTOMER_ TRANSACTIONS 4 group by CUSTOMER_ NUM 5 order by 1 This returns one row per customer which is then joined back to the CUSTOMERS table to get the current account balance in dollars I made the mistake which in part motivated this article of focusing solely on how to improve the performance of the SQL statement rather than stepping back and investigating the business requirement First I tackled the problem by using the query shown in Listing 2 colloquially referred to as the KIWI kill it with iron approach where more server resources are thrown at the query via parallelism Listing 2 More hands making less work SQL select PARALLEL t CUSTOMER_ NUM 2 max TRANS_ TIMESTAMP LAST_ TS 3 from CUSTOMER_ TRANSACTIONS t 4 group by CUSTOMER_ NUM
You must have JavaScript enabled to view digital editions.
