(OLAP reporting embedded in SQL)

Much of the OLAP reporting feature embedded in Oracle SQL is ignored. People turn to expensive OLAP reporting tools in the market - even for simple reporting needs. This article outlines some of the common OLAP reporting needs and shows how to meet them by using the enhanced aggregation features of Oracle SQL.

The article is divided in two sections. The first introduces the GROUP BY extensions of SQL, and the second uses them to generate some typical reports. A section at the end introduces the common OLAP terminologies.

The AWR tables contain super-useful information about the time-series execution plans for SQL statements and this repository can be used to display details about the frequency of usage for table and indexes. This article will expore these AWR tables and expose their secrets.

This article provides a clear, thorough concept of analytic functions and its various options by a series of simple yet concept building examples. The article is intended for SQL coders, who for might be not be using analytic functions due to unfamiliarity with its cryptic syntax or uncertainty about its logic of operation. Often I see that people tend to reinvent the feature provided by analytic functions by native join and sub-query SQL. This article assumes familiarity with basic Oracle SQL, sub-query, join and group function from the reader. Based on that familiarity, it builds the concept of analytic functions through a series of examples.

As the discipline of software development evolves, software products grow in size and the code grows in complexity, the need for a set of guidelines and standards becomes increasingly evident. In this article I will enumerate some of the industry best practices that the experts have proposed and emphasized, in the context of PL/SQL programming.

Many Oracle professional do not realize the important of having good statistics for the Oracle cost-based SQL optimizer (CBO). The CBO has received a "bum-rap" from some Oracle professionals who wonder why the CBO is generating sub-optimal execution plans for their SQL statements.

While all SQL tuning professionals advocate tuning each individual SQL statement to reduce logical I/O, there are many cases where you do not have the luxury of tuning each-and-every SQL statement in an application. In these cases, the best you can hope to do is adjust the global optimizer parameters to optimizer as many SQL statements as possible.

Below is an example of how to setup a REF CURSOR and how to call it from a PL/SQL block.

Why would you want to use REF CURSOR's? REF CURSORs can often provide greater performance when working with data. Much of these comes from the fact that packages are stored into the SHARED POOL of the Oracle database or otherwise known as PINNED to memory.

What I did find interesting was that the process that executes this procedure never needs to know what tables the view touches or even be able to see the view. The procedure simply returns the requested columns.

Also: It would be entirely feasible, using REF CURSOR design, to have queries stored in a LONG column in a table. Remembering that the length of the data cannot exceed 32K. These queries can then be retrieved at package execution time. This could often reduce the number of production moves regarding packages. You could further enhance it by versioning your queries, so that only the most recent would be retrieved.