Query rewrite an one of Query Transformations which makes possible to use materialized view to get results instead of querying underlying tables.
Basic prerequisites for query rewrite described in corresponding chapter of a Database Data Warehousing Guide. Let’s start with a simple example to show how it works.
In this I’ll do a quick performance comparison of built-in Oracle Text index versus the one created using Extensible indexing interface (described in this post).
First of all, Oracle Text option must be installed to use built-in text indexes. This functionality is getting installed under CTXSYS schema.
Speaking about DIY text indexes, it’s mandatory to have privileges to create index types and operators or simply RESOURCE role.
I assume it would be more correct to say “using scala functions in Oracle” but Oracle documentation uses terms like External Procedures or External Subprograms.
There are no specific details about scala with regard to external procedures in Oracle but given that scala code is translated into Java byte code there should be no difficulties in using scala as a language for stored code as well.
Of course, there is no scala compiler in Oracle so we cannot run statement like
create or replace and compile java source but we can compile scala code in advance and upload classes using
All java methods which are mapped to PL/SQL routines must be static and since there are no static methods in scala the typical approach to mimic them is by declaring functions in an object rather than in a class.
Extensible indexing interface
Domain indexes are used to speed up data retrieval for specific domains like text, spatial or image data. Oracle has built in indexes for above mentioned domains but developers can create their own using extensible indexing interface (
Let’s consider specific task when this can be useful and discover process end-to-end.
Frankly speaking “evoulution” is not quite proper word to title this post.
I’m going to show some extensions in regular expressions in different languages in compare to their implementation in Oracle.
Oracle supports regular expressions according to POSIX standard draft 1003.2/D11.2 and two extensions
- matching capabilities for multilingual data
- some commonly used PERL regular expression operators (for example, character class shortcuts and the nongreedy modifier “?”)
Let’s consider quizzes which cannot be easily resolved using built-in capabilities in Oracle.
There are quite a few tools and ways to build and manage cubes in Oracle so in this post I’ll try to clarify some concepts.
Good top level introduction could be found in article by Arup Nanda – Data Warehousing and OLAP.
Detailed step by step tutorial – Building OLAP Cubes.
The easiest way to start is to explore existing sample schema.
Let’s consider “hypothetical” case when it’s required to check if row is blocked without attempting to lock it.
And maybe we’ll find a way to see uncommitted data in Oracle. 🙂
New syntax for so called “Top N” queries was introduced in Oracle 12c.
It can be used to return either fixed number of rows or specified percent of rows in a table.
Obviously latter requires to know the total number of rows to return the result so let’s check what is happening under the hood in this case.
I’ve described how to measure context switches some time ago in this post.
Given that with_clause allows to declare PL/SQL functions in 12c it’s interesting to analyze overhead for context switches in this case.
Bloom filter may help to dramatically reduce amount of data to be read from partitioned table by eliminating unneeded partitions.
Here is a nice introduction into bloom filters.
Unfortunately bloom filter doesn’t work with DML which causes a lot of challenges in 11gR2.
This bug is documented on metalink Bug 13801198 : BLOOM PRUNING/FILTER NOT USED IN DML STATEMENTS however that note doesn’t suggest any workarounds.