B2B works with advanced queueing with the queue-table ip_qtab based on the IP_MESSAGE_TYPE Oracle Type wich is defined like:
create or replace type IP_MESSAGE_TYPE as OBJECT (In the queuetable you (more...)
Here is the SQL-Version, we compute this for all integers up to 100
with bound as ( select 100 as bound from dual ), n_until_bound as ( select level+1 n from dual connect by level <= (select bound.bound from bound) ), primes_under_bound (more...)
Zeckendorfs theorem every positive integer is uniquely the sum of distinct nonconsecutive Fibonaccis http://en.wikipedia.org/wiki/Zeckendorf%27s_theorem#FibonacciFriday
— Matthias Rogel (@MatthiasRogel) 22. Mai 2015
Don’t be afraid of having a look at the wikipedia-site, the math is not complicated at all ( you don’t need more than adding natural numbers smaller than hundred ), nevertheless the theorem is nice from a (more...)
The Fourth International NoCOUG SQL Challenge has been published in the May issue of the NoCOUG Journal available at http://www.nocoug.org/Journal/NoCOUG_Journal_201505.pdf. Shakespeare’s story of Romeo and Juliet plays out in an American setting. In the NoCOUG version, Romeo sings American songs to Juliet and gives her gourmet dipped swizzled strawberries for her birthday. The bard of Avon must be rolling in his grave.
Entries should be attached as a comment to this announcement. (more...)
Since I have written about my pet project about a month ago, I have made some major changes to the functionality of it.
If you haven't read that blog about my pet project, here's the synopsis:
I love Logger to instrument my code, I just don't like to type in all the bits and pieces to register all the input arguments when I write a new procedure or function. To solve this problem I have (more...)
Execution Plan Instability – What is the problem?
The Oracle Optimizer is a complex piece of software and with every release it becomes more complex.
In the beginning, the Optimizer was rule-based.
The Optimizer had a ranked list of heuristics used to optimize a query, picking the lowest ranked rule available.
This rule-based mode, whilst still in use with some internal Oracle dictionary queries, has been unsupported since version 10.1.
This means that (more...)
Developing your Decision Tree models using the ODMr allows you to explore the decision tree produced, to drill in on each of the nodes of the tree and to see all the statistics etc that relate to each node and branch of the (more...)
How many times have you guessed the right answer?
select * from dual order by -1; select * from dual order by 0;
select * from dual order by -(0.1+0/1) desc; select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by -(0.1+0/1) desc;
select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by 0; select 1 n,0 n,2 (more...)
with dates as ( select trunc(sysdate, 'year') + level - 1 as day from dual connect by extract(year from trunc(sysdate, 'year') + level - 1) = extract(year from trunc(sysdate, 'year')) ), timezones as ( select vtn.TZNAME, listagg(vtn.TZABBREV, ', ') within group(order by vtn.tzabbrev) tzabbrevs from v$timezone_names vtn group by vtn.TZNAME ), offsets as ( select v.*, d. (more...)
Since some time I have been adding
WHERE 1=1 to all my queries.
I get queries like this:
SELECT * FROM emp e WHERE 1=1 AND e.ename LIKE 'A%' AND e.deptno = 20
Lots of people ask me what’s the use of this
You know I like to type as little as possible but here I am typing a lot of extra characters. And yet, it makes my development life a (more...)
My use case is integrating Oracle Apex with the public Mendeley REST API for Mendeley Catalog Search.
The idea was to build an (more...)
The Hitchhiker’s Guide to the EXPLAIN PLAN Part 36: The sisters of the mother of all SQL antipatterns
Moving through some observations of an ongoing investigation tackling the easiest ones first.
Previously I observed the slow execution of a recursive delete of statistics history but coming not from MMON but from a number of application processes which should have only been calculating stats for their own partitions.
Statement was sql id 9v9n97qj8z1dg:
delete /*+ dynamic_sampling(4) */ from sys.wri$_optstat_histhead_history where savtime < :1 and rownum <= NVL(:2, rownum);
Yesterday I mentioned issues with a recursive delete operation on statistics history.
This is a quick illustration of the last points I made on that post regarding the lack of a COUNT STOPKEY optimisation because of the use of NVL.
COUNT STOPKEY is an optimisation which allows processing to stop once the target number of rows has been reached.
create table t1 as select * from dba_objects; alter session set statistics_level = all; (more...)