When working with the Clustering algorithms, and particularly k-Means, in the Oracle Data Miner tool there is no way of seeing how compact or dispersed the data is within a cluster.
There are a number of measures typically used in various tools and algorithms, but with Oracle Data Miner we are not presented with any of this information.
But if we flip from using the Oracle Data Miner tool to using SQL we can get (more...)
Most of you probably know the "Quick Outline" function you have inside the SQL Developer.
It helps you to easily jump between different functions/procedures inside a package.
My colleague Holger told me about a bug in SQL Developer 3.x where you could use the "Outline" view with normal SQL files, too. Unfortunately in version 4 it didn't work anymore. So he stayed with version 3 for a long while. Otherwise he would had to (more...)
I'm a big fan of generating data with dual, using a perk of the connect by syntax.
I think Tom Kyte was the originator of this technique. It's not necessarily the fastest method, but it's super convenient - no table required.
Today I wanted to know how many weekdays a year, so I defined 365 rows on the fly using sysdate to turn these into each day of the year. Then I ran a simple (more...)
InfoDie Select-Statements in diesem Blogpost habe ich von anderen Webseiten kopiert. Daher ist dieser Beitrag eher als Zusammenfassung unterschiedlicher Lösungsversuche zu sehen und dient mir als schnelle Hilfe bei der Verkleinerung eines zu großen Tablespaces. Schaut euch die Quellen an, die sehr viel detaillierter auf die jeweiligen Probleme eingehen.
Wer kennt nicht die Situation? Der DBA ruft an und meint der TEMP Tablespace verbraucht mehrere hundert Gigabyte an Speicher.
Was ist in solch einer (more...)
This week I was in an excellent presentation by Kerry Osborne about Outlines, SQL profiles, SQL patches and SQL Baselines. I’ve used three of those features in anger but when I looked at SQL Patches I just could not understand why you would use them – they looked to me like a very limited version of SQL Profiles.
There is a prize for spotting Kerry without a baseball cap
So I asked Kerry about it (more...)
Today I did my second presentation at DOAG2016. It was at 9:00 so I got to sleep in ;) The room was huge but there were enough people that I didn’t feel too lonely. The room and the technical help were top notch, and again there were questions at just the right time to remind me […]
DOAG2016 started today at 8:30, and I did too. There were so many great presentations at the same time as mine, I was surprised and pleased to get a nice audience.
If you are a user of the Oracle Data Miner tool (the workflow data mining tool that is part of SQL Developer), then you will have noticed that for many of the algorithms you can specify a Case Id attribute along with, say, the target attribute.
The idea is that you have one attribute that is a unique identifier for each case record. This may or may not be the case in your data model (more...)
A little while ago a member of the Danish Oracle User Group DOUG
asked if anyone had a bit of experience with use of R together with Oracle Database. I remembered an article I wrote for OTech Magazine on forecasting with R and Oracle and thought it would be a good primer for him. OTech Magazine is no longer active, sadly, but I thought I could send him a link anyway to my article. That (more...)
This year I get to speak about advanced SQL twice at two different conferences. My first presentation is about row pattern matching with MATCH_RECOGNIZE and my second deals with ranges - including but not limited to Temporal Validity ranges.
We have gone through a merger at our company where we are trying to merge the databases. The problem now is that we have duplicate records in our tables. We can of course go through all the records by hand and check if they exist twice. Another option is to build an application to do this. But using the Oracle Database there must be a better way to do this. Any ideas?
This is my contribution to the OTN Appreciation Day, which was initiated by Tim Hall.
One of my favorite features of the Oracle Database are Analytic Functions. They were introduced with Oracle Database 8.1.6 Enterprise Edition, and have been in the Standard Edition since version 9.
With analytic functions you can add inter-row calculations, aggegrate over multiple dimensions, rank assignments based on values. All this without a GROUP BY clause.
The syntax might (more...)
Recently I heard someone talk about overloading in Java. What is it, is it possible in PL/SQL and if so, how would I use it?
Overloading is a technique of creating multiple programs with the same name that can be called with different sets of parameters. It is definitely possible to apply this technique in PL/SQL, in fact, Oracle does this a lot of times in their own built-in (more...)
Because today is Batman Day
, it calls for a celebration of the SQL type:
SQL> select listagg (b)
2 within group (order by null) ||' Batman'
3 from (select 0f/0 b
4 from dual
5 connect by level <= 14
Last year I did a presentation on table functions at KScope. One of the questions I got was: ‘If pipelined table functions provide their results faster, why would you want to use a normal table function?’ I couldn’t come up with the answer then, maybe you can help?
Erik van Roon
Let’s start with explaining a bit what table functions are. Table Functions are functions that return a collection of (more...)
What is an ANTI-JOIN? And what is the difference between the SEMI-JOIN and the ANTI-JOIN?
First of all, both SEMI-JOIN and ANTI-JOIN are not in the SQL syntax but they are more a pattern. You might expect to be able to write something like:
[PATRICK]SQL>SELECT d.deptno, d.dname, d.loc
FROM dept d
SEMI JOIN emp e ON (e.deptno = d.deptno)
to get all the (more...)
email@example.com > create table t( pk int primary key check(pk > 0));
firstname.lastname@example.org > insert /*+ignore_row_on_dupkey_index(t(pk)) */ into t
email@example.com > select trunc(dbms_random.value(1, 1e5)) from dual
firstname.lastname@example.org > connect by level <= 1e5
email@example.com > /
63187 rows created.
Finding the first n gaps
firstname.lastname@example.org > variable n number
email@example.com > exec :n := 1000
PL/SQL procedure successfully completed.
firstname.lastname@example.org > set autotr (more...)
Could you tell me what the difference is between NVL and COALESCE?
NVL returns the value of the first argument if it doesn’t evaluate to NULL, otherwise it will return the value of the second argument.
COALESCE returns the first argument that doesn’t evaluate to NULL. That can be any one of the arguments.
So they are definitely similar – but there are significant differences.
First of (more...)
I have got a website with a search form. I want to display a limited number of results to the user and have him/her navigate through different pages. Is this possible using plain SQL?
Of course this is possible. It might take some thinking, but that has never hurt anyone (yet). First we need a table with some randomly sorted data in it. In this example I (more...)