Use the code in Listing 1 to create the Get Nums functions the sample database. This meant that if you wanted indexes in your table variables, your only option was to define those indirectly via inline PRIMARY KEY and UNIQUE constraint definitions.
You couldn’t, for example, define non-unique indexes.
The prudent use of temporary tables can dramatically improve Oracle SQL performance.
To illustrate the concept, consider the following example from the DBA world.
Some of the new features target specifically temporary objects, whereas others are more general and just happen to effect temporary objects as well.
The examples in this articles demonstrating the new features use a sample database called Performance V3.
A collection has only one dimension, but you can model a multidimensional collection by creating a collection whose elements are also collections.Again, it is not easy to quantify the reason for this speed increase, since the DBA views do not map directly to Oracle tables, but it is clear that temporary table show promise for improving the execution speed of certain types of Oracle SQL queries.If the amount of data to be processed or utilized from your PL/SQL procedure is too large to fit comfortably in a PL/SQL table, use a GLOBAL TEMPORARY table rather than a normal table.SQL Server 2014 introduces a number of gems that can make your solutions faster: support for inline index definitions, memory optimized table types and table valued parameters (TVPs), parallel SELECT INTO, relaxed eager writes and improved cardinality estimates for table variables.The last two improvements were also "backported" to SQL Server 2012.