SQL Archives - Page 4 of 6 - Philipp Salvisberg's Blog

2020-04-13

2020-04-13

Categories

Oracle

Formatting Code With SQL Developer

Introduction I started using SQL Developer in 2013. Back then version 4.0 was the latest and greatest. But the capabilities of the formatter were disappointing. In 2017 Oracle released version 4.2 with a new formatter and has been improving it ever since. Version 19.2 brought us dynamic JavaScript actions within the parse-tree […]

2019-12-13

Categories

Oracle

Constants vs. Parameterless Functions

Do you use parameterless PL/SQL functions in your queries? Did you know that this may cause performance issues? In this blog post, I explain why parameterless functions can be the reason for bad execution plans in any Oracle Database. I recently had to analyze this problem in a production system and thought […]

2019-10-24

Categories

Oracle

Integrate SQL*Plus Scripts in SQL Developer

I envy my DBA colleagues when they work with the Oracle Database from the command line in an incredibly efficient way. They just call a series of scripts with some parameters to get the desired information. Everything looks so easy, so smooth, so natural. I’m a developer. Basically a mouse pusher. I […]

2019-02-17

Categories

Oracle

MemOptimized RowStore in Oracle Database 19c

Since February 13 2019 Oracle Database 19c has been available. I blogged about this feature here and here. Time for an update. So, what’s new in 19c regarding the MemOptimized Rowstore? Fast Lookup Works with JDBC Thin Driver I listed 16 prerequisites for the MemOptimized Rowstore in this blog post. The last one – […]

2018-09-28

Categories

Oracle

Regular Expressions in SQL by Examples

Are you reluctant to use regular expressions in SQL? Then continue reading. Examples helped me to understand regular expressions years ago. Thus I hope this collection of simple examples and the tooling tips will encourage you to use regular expressions. It’s not as complicated as it looks at first glance. Once you […]

2018-08-25

Categories

Oracle

Use the Database as Persistence Layer Only

Using the database as a persistence layer only is an anti-pattern. Praful Todkar applies this anti-pattern in How to extract a data-rich service from a monolith. Martin Fowler reviewed this article and published it on his website. Hence it is highly visible. I generally agree with the approach. However, I cannot agree […]

2018-08-05

Categories

Oracle

View-API for JOOQ Application

In this blog post, I show how to build a read-only view-API for Oracle’s HR sample schema. And I will use this view-API in a JOOQ application. This application will fully comply with the Pink Database Paradigm (PinkDB). This means the application executes set-based SQL and retrieves data with as few network roundtrips as possible. […]

2018-07-18

Categories

Oracle

The Pink Database Paradigm (PinkDB)

1. Introduction The Pink Database paradigm (PinkDB) is an application architecture for database-centric applications. It focuses on relational database systems and is vendor-neutral. The principles are based on the ideas of SmartDB, with some adaptions that make PinkDB easier to apply in existing development environments. An important feature of a PinkDB application […]

2018-07-18

Categories

Oracle

Is Your Application SmartDB?

I recently had a few discussions regarding the Smart Database Paradigm (SmartDB) with long-standing customers, new customers, partners, competitors and colleagues. Some people think that using APEX and PL/SQL in their database application is SmartDB. But it is not that simple. Bryn Llewelyn defined the term “Smart Database Paradigm” (SmartDB) in his talk Guarding […]

2018-06-19

Categories

Oracle

MemOptimized RowStore in Oracle Database 18c with OCI

On June 10 2018 I blogged about the MemOptimized RowStore in Oracle Database 18c. If you haven’t read this post, it is a good idea to catch up now. I showed that accessing a memoptimized table t4 via the MemOptimized RowStore was around 60% slower than accessing a heap-organized table t1. I […]