Databases Reference
In-Depth Information
When you look at the execution plan in the preceding screenshot, notice that
INDEX
UNIQUE SCAN
is done on
SYS_IOT_TOP_81574
. This is the system-generated
name for the index created for the index-organized table. The number of
consistent
gets
for this statement is only
2
—slightly better than the heap table, but slightly
worse than the single-table hash cluster.
Depending on the nature of the lookup tables in your application, the frequency with
which the data changes, and the amount of storage that may be needed, you might
want to choose single-table hash clusters or index-organized tables.
Analytic functions
Analytic functions were introduced quite a long time ago—Oracle 8.1.6 Enterprise
Edition in around 1999—yet they are still quite unknown to a lot of developers.
With analytic functions you can retrieve data from more than one row at the same
time without the need for a self join. You can create a ranking based on a value
within a group of values. They are not easy to use, but once mastered, analytic
functions can make your life a lot easier. With analytic functions, you can create the
overviews that the customer may want within a few lines of code.
Syntax overview
The processing order of a query with analytic functions happens in three stages.
First of all the Joins,
WHERE
conditions,
GROUP BY
, and
HAVING
clauses are applied.
Next, the analytic functions are applied to the resulting result set. Finally, the
ORDER
BY
clause is processed. This order of processing is important to know, because after
getting comfortable with analytic functions, it is quite easy to get carried away. Use
traditional aggregates before you decide on analytic functions.
Processing order of a query
The irst part of the analytic function that you need to identify is the "what". There
are a lot of regular aggregate functions, which also have an analytic counterpart.
Some examples of these are
SUM
,
AVG
, and
COUNT
. The easiest way to know that you
are dealing with the analytic counterpart is by checking that the reserved word
OVER
is present after the function name:
avg(sal) over ()
