To test out different physical table lay-outs, I had to repeatedly generate some test-data. After a few runs, I wanted to speed it up a bit. This is how I did it.
Generating data: union all vs insert all
Category: SQL
A small unexpected to_date oddity
I was about to do some date/time calculations as I got an unexpected result with the to_date-function.
Read More »
Reviewing statements with hints, using PL/Scope
In my previous post, I showed how you can add a fake hint to tag the origin of a duplicated statement and as a side-effect make it unique. What if you regret and can’t remember where it was. Or you want to review old hinted statements after an upgrade to a newer version of the database/optimizer.
Differentiating between duplicated statements
Finding duplicate SQL statements using PL/Scope is easy. If you cannot merge them, how can you differentiate between which source is being run?
Find bad performing queries or code with PL/Scope
On our production system we’ve enabled the collection of PL/Scope metadata. Since this is a SmartDB/PinkDB-application (business logic and queries in the database), this makes it really easy to find, inspect and modify the source code of queries that doesn’t run efficiently. Now it’s even easier using reports in Oracle SQL Developer.
Matching strings with a combination of tools
I had a case where I had to match some names and needed to find a set of ways to clean and match strings. The solution I ended up with was a combination of regular expressions, the NLSSORT-function and the UTL_MATCH-package with the Jaro-Winkler algorithm.
TRIM: More than meets the eye
I’ve always just used the TRIM-function to remove leading and trailing spaces. While going through Markus Winands presentation “Four* Major Database Release of 2017 in Review” on SlideShare.net, I realized that the TRIM function can do more than just remove spaces.
- nice theory... 