Oracle Magazine, March/April 2019
Database Developer and DBA ETL ORACLE MAGAZINE MARCH APRIL 2019 104 Id Operation Name 0 SELECT STATEMENT 1 INDEX FAST FULL SCAN CUSTOMER_ TRANS_ IX This decreased the report response time but I stressed to the client that this was only a temporary solution because as the transaction volume continued to increase even an index being used as a thin version of the transaction table would ultimately mean that the performance problems would recur Other solutions partitioning compression and materialized views were considered as well the details of which Ill omit for brevitys sake but in each case the performance benefits also came with side effects that were undesirable for this client This is not to dismiss any of these or the above techniques out of hand All of the solutions are potentially valid in other use cases but were just not for this particular client I convinced the development team to ask the business users what the motivation for this report was After all a report that shows the most recent of any high volume activity is always out of date the moment it has been run So I was curious to discover what value the report gave the business The response was that the report was used to identify those customers who had not visited the website recently so that they could be offered incentives via promotional offers to return to the site and become active again Understanding the business requirement made the solution to tuning the SQL trivial All it took was a look at the columns in the CUSTOMERS table as shown in Listing 4
You must have JavaScript enabled to view digital editions.