Oracle Magazine, March/April 2019
Database Developer and DBA ETL This improved the performance of the report but to the detriment of other functions of the application which subsequently struggled to obtain sufficient I O resources from the server because it was being hammered by parallel I O slaves Also the response time of the report became less predictable because it was dependent on the number of concurrent executions of the report and how many parallel slaves were available to a given report request Variability of response time is often more frustrating to application users than slow but consistent performance hence parallelism was dismissed as a permanent solution The next alternative was to make the transaction table thinner This is a common technique in which the fields contained in either the SELECT clause or the predicates are added to an index so that only the index rather than the full table needs to be scanned The index becomes a thinner version of the table Listing 3 shows this strategy and the resultant query execution plan Listing 3 Using an index as a thin table SQL create index CUSTOMER_ TRANS_ IX 2 on CUSTOMER_ TRANSACTIONS CUSTOMER_ NUM TRANS_ TIMESTAMP ORACLE MAGAZINE MARCH APRIL 2019 103 SQL select CUSTOMER_ NUM 2 max TRANS_ TIMESTAMP LAST_ TS 3 from CUSTOMER_ TRANSACTIONS 4 group by CUSTOMER_ NUM 5 order by 1
You must have JavaScript enabled to view digital editions.