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...)
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 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...)
Die 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.
So I asked Kerry about it (more...)
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...)
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...)
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)); Table created. 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...)