On the eleventh day of Christmas, my true love gave to me Eleven pipers piping. Over a lifespan of four and a half decades, the relational camp made a series of strategic mistakes that made NoSQL and Big Data possible. The mistakes started very early. The biggest mistake is enshrined in the first sentence of […]
On the tenth day of Christmas, my true love gave to me Ten lords a-leaping. The topic of Big Data is often encountered when talking about NoSQL so let’s give it a nod. In 1998, Sergey Brin and Larry Page invented an algorithm for ranking web pages (The Anatomy of a Large-Scale Hypertextual Web Search […]
You may have previously seen a short post I did on a SQL statement to identify which statements are using dynamic sampling.
If not, quick recap:
SELECT p.sql_id, t.val
FROM v$sql_plan p
, xmltable('for $i in /other_xml/info
where $i/@type eq "dynamic_sampling"
columns attr (more...)
In Oracle 11G, Oracle introduced SQL Plan Management (SPM). It is excellent (I love it to bits). It allows you to create Baselines against SQL which lock-down the SQL execution plan. No more plan flips. More consistency. Perfect.
Whenever some Baselined SQL is ran, Oracle still parses it and compares (more...)
Before I joined Cloudera, I hadn't had much formal experience with Big Data. But I had crossed paths with one of its major use cases before, so I found it easy to pick up the mindset.
My previous big project involved a relational database hooked up to a web server. (more...)
Just a quick code snippit. I do a lot of data pumps to move schemas between different databases; for example taking a copy of a schema to an internal database to try to reproduce a problem. Some of these schemas have some very large tables. The large tables aren’t always (more...)
Yesterday I attended John King (@royaltwit) session on Oracle 12c for developers. He gave an overview of all the goodies that are available to us as developers. The whole plugging and unplugging of database, though very cool and exiting, is most likely not very relevant to most developers.
When he (more...)
Since PL/SQL now is closely integrated into SQL, we hence can happily state
firstname.lastname@example.org > with function bestpractice return varchar2
4 return 'Do not use PL/SQL when it can be done with SQL alone !';
5 end bestpractice;
Recursive subquery factoring (AKA Recursive Common Table Expressions) has been available in Oracle since version 11g R2 was launched sometime around 2009. I always thought of it as a replacement for the connect by clause and just another way to write hierarchical queries, so I never really took the time (more...)
The PIVOT and UNPIVOT operators were introduced in Oracle 11g and the other day I helped out a friend using these operators.
He wanted the data in the columns to be "shifted to the left". To explain a little more, take a look at the following data:
ID V1 V2 (more...)
This year is kind of fun, my birthday is on November 12th (next Tuesday, if you want to send gifts). That means it will fall on 11/12/13. Even better perhaps, katezilla's
birthday is December 13th. 12/13/14. What does this have to do with SQL?
Someone mentioned to me last night (more...)
It seems that sometimes you need a non-unique index to enforce a unique constraint even if this constraint is declared as not deferrable.
email@example.com > create table strange(i int not null, j int not null);
firstname.lastname@example.org > alter table strange add constraint unique_i unique(i) not deferrable
Thank you all who attended my sessions at NYOUG Fall Conference this morning. I appreciate spending you most precious commodity - your time
- with me. I sincerely hope you found both the presentations enlightening as well as entertaining.
Please see the details of the sessions below along with the (more...)
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.
I was trying to call function DECOMPOSE while investigating a strange problem I had with some Unicode strings that could not be properly indexed by Oracle Text and, amazingly, I found out that most (more...)
Those who visit SQL.ru often know what the title means. It’s a very simple yet powerful technique to group data which doesn’t seem appropriate for grouping at first sight.
I learned this very nice tip long time ago but often have to re-think before applying it to SQL or (more...)
After my Kscope13 presentation, one of the attendees approached me and asked if I had used regular expressions to try to validate email addresses. He didn’t know about regular expressions before the presentation and he was writing a PL/SQL function (which was getting very complex…) to accomplish this particular task. (more...)
DBMS_LOCK is a slightly obscure built-in package. It provides components which so we build our own locking schemes. Its obscurity stems from the default access on the package, which is restricted to its owner SYS and the other power user accounts. Because implementing your own locking strategy is a good (more...)
Time travel sucks, especially going back in time. Nobody takes a bath, there are no anaesthetics and you can't get a decent wi-fi signal anywhere. As for killing your own grandfather, forget about it.
The same is true for going back in database versions. In 2009 I had gone straight (more...)