Disable cache in Jasper Analysis

As known JasperServer uses Pentaho Mondrian as base of Jasper Analysis component. Mondrian has elaborate cache which truly increases performance during using analysis views.

But  when we have not large database and need realtime OLAP the cache becomes a wall. And we want to turn off it.

I wanted to achive it using some properties in configuration and not using any Java code.

NO_DATA_FOUND is not raised from a function in Oracle SQL

It's not a bug, it's an Oracle feature. It happens with NO_DATA_FOUND exception only. And such behaviour is typical for all version of database. It is not documented anywhere in Oracle documentation. Just remeber and keep it in mind.

Now I show what I am talking about.

Using DBMS_PARALLEL_EXECUTE package

In cases when you need to make any changes in data of huge table routines of DBMS_PARALLEL_EXECUTE package can help you to increase performance of your update statement.

The package was introduced in Oracle 11g.

In this article won't be illuminated all features of DBMS_PARALLEL_EXECUTE package, just basic moments. You can find more information in Oracle documentation.

Sequence's NEXTVAL in MERGE operator

I have used MERGE operator many times before. But recently I was surprised by fact that in case when in MERGE there is call of sequence's NEXTVAL it will be executed even the another branch of MERGE operator will be used.

I found remark about this here, section "How to Use Sequence Values"

The reference to NEXTVAL can appear in the merge_insert_clause or the merge_update_clause or both. The NEXTVALUE value is incremented for each row updated and for each row inserted, even if the sequence number is not actually used in the update or insert operation.

Let's illustrate this.

How to get uninterrupted array of integers or dates using Oracle hierarchical queries

Often in different tasks I need array of integers which does not have any "holes" (for example 1, 2, 3, 4, etc.). Or part of calendar for some period as array of dates.

Usually tables with such arrays are created in database and are used in queries.

But Oracle provides an opportunity to get uninterrupted arrays "on the fly" using hierarchical queries.

Oracle NVL vs COALESCE

Now I try to accumulate all my knowledge about these similar functions.
NVL is native Oracle function whereas COALESCE is ANSI SQL one.