Deleting Rows With Merge
The merge statement allows you to insert, update and delete rows in the target table in one go. This is great. However, the delete part does not work as expected. Is it a bug? No, it works exactly as documented. Nonetheless, I was not aware…
Testing With utPLSQL – Made Easy With SQL Developer
Nowadays, everything is about automation. Software testing is no exception. After an introduction, we will create and run utPLSQL tests with Oracle SQL Developer for a fictitious user story. Our tests will cover a PL/SQL package, a virtual column, two database triggers and a view….
plscope-utils for SQL Developer 1.0 – What’s New?
Introduction PL/Scope is an SDK for source code analysis. It is available since Oracle Database 11g Release 1 and has been significantly improved in 12c Release 2. plscope-utils for SQL Developer is a SQL Developer extension that simplifies the compilation with PL/Scope, visualizes PL/Scope information…
Housekeeping in Oracle: How to Get Rid of Old Data
Have you ever tried to delete a few million rows from a table with several hundred milllions of rows with a DELETE statement? If yes, then you know that this is a very bad idea. With Oracle Partitioning you can do this more elegant and…
Performance Tips: PL/SQL Functions in SQL Queries
PL/SQL functions in SQL statements may cause major performance problems. If it is not possible to avoid these calls, there are several solutions to improve the performance. But not for all situations. In my last blog post, I wrote about the impact of function calls in…
Performance Tips: Function Calls in WHERE Conditions
A typical reason of many performance issues with SQL are function calls in the WHERE condition. They make it difficult for the query optimizer to determine the cardinality. Fortunately, there are several ways to solve this problem. Function calls – or any kind of SQL expressions…
Finding Wrong Hints
Introduction I use the Oracle Database since many years. And I use hints. For experiments, but also in production code. There are cases when you know more than the Oracle Database. For example about the cardinality of a data source or the number of result…
GraalVM Native Image – First Impressions
Introduction A native image is an operating system specific executable file. You can build such an image for basically every application running on a Java virtual machine. This approach promises faster start-up times and lower resource consumptions. This makes it appealing for serverless computing, auto-scaling…
Lightweight Formatter for PL/SQL & SQL
TL;DR Bye bye heavyweight formatter. Long live the lightweight formatter. Are you using Oracle’s SQL Developer or SQLcl? Then install these settings and press Ctrl-F7 to produce code that conforms to the Trivadis PL/SQL & SQL Coding Style. A compromise between conformity and individuality. Heavyweight…
SQL Tuning Golf, anyone?
Golf is one of the very few sports where the goal is the lowest possible score. Code golf is about solving a given algorithm using the fewest possible characters. It can be very fun indeed to play code golf, also in SQL. But for real…