It is a very common requirement. You need to be able to log the start and end of procedure and function calls. You also need to log error and exception events and sometimes some informational messages as well. This is typically done by writing a simple logging package that uses autonomous transactions so that any transaction rollback does not rollback the diagnostic logging information as well. Apart from logging text (and usually other things as (more...)
When a table is accessed by multiple members of a parallel query server set, the execution plan may show the use of block range granules (PX BLOCK ITERATOR) or partition range granules (PX PARTITION [RANGE|LIST|HASH] ITERATOR or PX PARTITION [RANGE|LIST|HASH] ALL).
The basic ideas surrounding these concepts are discussed in numerous blogs and books, including my own, but discussion of partition range granules is normally restricted to either partition wise joins or the creation (more...)
I am currently working in a team alongside a gentleman known as Roberto Rigliaco. You are unlikely to have heard of Roberto as he is not (yet) a blogger and not (yet) known on the conference circuit.
Nevertheless, Roberto has done some excellent work recently analyzing the use of object statistics in partitioned tables. I haven’t seen his findings published anywhere else and so I am glad to say that Roberto has accepted my offer (more...)
This is my first blog for a very very long time. I stopped blogging a couple of years ago when I started working on my book and after I finally finished the book I felt I needed a break to recuperate.
However, I have finally decided to put my toe back in the water. This isn’t a very serious post but I hope you find it thought provoking and a bit of fun.
The questions in the quiz were, of course, all trick questions.
Question 1: Answer D
SELECT * FROM T1 LEFT NATURAL JOIN T2;
SELECT * FROM T1 NATURAL LEFT JOIN T2;
The second example shows the correct syntax for a natural outer join. In the first example, LEFT is interpreted as a table alias for T1 and so an inner join is used.
QUESTION 2: Answer E
SELECT * FROM T1 INNER JOIN T2 ON (more...)
If you are an attendee of UKOUG Tech14 you have a chance to win a free copy of Troubleshooting Oracle Performance (2nd edition) by Christian Antognini
Troubleshooting Oracle Performance, 2nd Edition provides a systematic approach to addressing the underlying causes of poor database application performance.
AND a you can also win a free copy of Expert Oracle SQL by Tony Hasler
Expert Oracle SQL provides a systematic approach for tuning individual SQL (more...)
This note describes a couple of real-life use cases for the SQL MODEL (or SPREADSHEET) clause that appeared in Oracle 10g. This is mainly targeted at people attending my UKOUG presentation on the model clause (materials TBS) but anybody who knows the syntax of the model clause may find this of some interest.We already have SQL (non-procedural) and PL/SQL (procedural) languages and it is easy to call SQL from PL/SQL and vice-versa. So you might be asking yourself why on earth we need the model clause. Doesn’t it seem like just a procedural extension to SQL? If so why (more...)
About six months ago I came across the need to use analytic functions with a moving one year window. This is supposedly supported in Oracle 10g and beyond but the behaviour was not what I expected. Consider this test query:
WITH q1 AS ( SELECT DATE '2012-01-01' + ROWNUM mydate FROM DUAL CONNECT BY LEVEL <= 400) ,q2 AS (SELECT mydate ,COUNT ( *) OVER ( ORDER BY mydate RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND CURRENT ROW) cnt ,MIN ( mydate) OVER ( ORDER BY mydate RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND CURRENT ROW) mindate ,MAX ( mydate) (more...)
Aggregate functions fall into two categories. The first is what I call “Sorting aggregate functions” that need their data sorted in some way to operate. Examples of sorting aggregate functions are MEDIAN and DENSE_RANK. The second category is non-sorting aggregate functions that do not require a sort to operate. These include COUNT, AVG, and RANK.
In Oracle 10g the concept of HASH AGGREGATION was introduced as an optimisation of the GROUP BY operation. The idea is that non-sorting aggregate functions (let me call them NSAFs from now on) do not need to be kept in a sorted list; you just (more...)
It is a very common and oft discussed issue. You have a complex view definition, possibly containing an analytic function, and when you SELECT from the view you can’t get any advantage from a WHERE clause. For an old, and lengthy, discussion of why Oracle “just can’t do it” you can see this article on Ask Tom. Well it turns out that there is something you can do after all. First, let us set the scene. We need a table and associated index with some values:
CREATE TABLE t1 AS WITH q1 AS ( SELECT ROWNUM rn FROM DUAL CONNECT (more...)