...
This query has a very broad set of responsibilities which cannot be easily verified in isolation. On closer inspection it appears that it is in fact formed of at least 7 distinct queries.
To effectively unit test the process that this query represents an approach must be applied that separates and encapsulates , VIEWs can be used to separate and encapsulate each of the subqueries so that they can be tested independently. Possible approaches to this include: VIEWs, This approach seems to be more effective than using sequential execution of components with intermediate tables (possibly both TEMPORARY and not) tables, and even variable substitution of query fragments. Furthermore, the availability of HPL/SQL may provide additional opportunities to modularize. The potential performance implications of such techniques should be considered. For example, using intermediate tables may generate , due to the performance implications of using intermediate tables; not only do they take longer to run, but more I/O and splitting large queries may limit is generated, and in using this approach the query optimisation optimization opportunities available to the query planner .
...
| title | TODO |
|---|
become limited.
Through limited testing it was also shown that VIEWs do not in fact suffer from performance issues when compared with both a single large query, and chaining tables together, as often prophesied; in fact the execution plans and times for VIEWs and monolithic queries were comparable.
Other approaches, such as using variable substitution, or HPL/SQL, were suggested, but on inspection, it seems these approaches cannot be used for achieving the goal of query modularization, as a variable cannot be substituted for an entire query fragment, and HPL/SQL lacks pipelined functions
...
.
Tools and frameworks
When constructing tests it is helpful to have a framework that simplifies the declaration and execution of tests. Typically these tools allow the specification of many of the following:
...