Infrastructure at your Service

All Posts By

Daniel Westermann

Daniel Westermann

Indexing for like/similarity operations

By | Database Administration & Monitoring | One Comment

Indexing queries for like/similarity conditions is not that easy with the usual index types. The only option you have with btree indexes (especially if the wild-card is at the beginning of the filter) is to create a partial index on that columns for a very specific query. Let’s do a simple example with a btree index. The test data: drop table if exists t1; create table t1 ( a varchar(50) ); insert into t1 (…

Read More
Daniel Westermann

Quickly create a hundred databases and users

By | Database Administration & Monitoring | One Comment

Do you need a hundred databases and users for training etc. in PostgreSQL? Just a few lines of code: [[email protected] data]$ cat create_databases.sh #!/bin/bash NUMOFDBS=100 for i in `seq ${NUMOFDBS}`; do psql -q -c “create user u${i} UNENCRYPTED password ‘u${i}’ NOCREATEDB NOCREATEROLE NOCREATEUSER” postgres createdb -O u${i} db${i} done Execute it and you are ready: [[email protected] data]$ time ./create_databases.sh real 1m37.825s user 0m0.143s sys 0m0.109s Have a look at the databases and its owners: postgres=#…

Read More
Daniel Westermann

Swiss Postgres Conference 2015

By | Database Administration & Monitoring | No Comments

At the 26th of June I had the chance to attend the second Swiss Postgres Conference at the HSR Rapperswil. It was packed with interesting sessions. Magnus Hagander, a well known community member, started by giving an overview of PostgreSQL Europe. Then I joined Hans Schönig for learning how to write aggregate functions in PostgreSQL. Honestly I did not understand all of it but at least I am now aware of what you can do…

Read More
Daniel Westermann

another way to list invalid objects

By | Database Administration & Monitoring | One Comment

How often did I type a query like this to list the invalid objects in a database? select count(*) from dba_objects where status ‘VALID’; — and user in/not in Today I learned another way to do the same. There is a view called dba_invalid_objects. Surprisingly the view is not listed in the official 12c database reference. It is however mentioned in the upgrade guide. Let’s do some tests. Currently I do not have any invalid…

Read More
Daniel Westermann

When a function returns a collection: can you directly reference the index of the returned collection?

By | Database Administration & Monitoring | One Comment

As I am still interested in PLSQL I browsed the documentation some days ago and learned something new which is about functions returning collections. Here is a simple test case: Let’s create a simple package, specifying on collection and one function: create or replace package pkg1 as type t1 is table of number index by pls_integer; function f1 ( a number ) return t1; end pkg1; / create or replace package body pkg1 as function…

Read More
Daniel Westermann

ODA workshop at Arrow ECS

By | Database Administration & Monitoring | One Comment

On the 16th and 17th of June David Hueber, Georges Grey and myself had the chance to attend the ODA hands on workshop at Arrow ECS. Lead Trainer Ruggero Citton (Oracle ODA Product Development) did the first day with plenty of theory and examples. On the second day we had the opportunity to play on a brand new ODA X5-2: [[email protected] ~]# oakcli show server Power State : On Open Problems : 0 Model :…

Read More
Daniel Westermann

What is more efficient: arrays or single columns values? – oracle

By | Database Administration & Monitoring | One Comment

In the last post on this topic it turned out that using an array as a column type needs more space than using a column per value in PostgreSQL. Now I’ll do the same test case in Oracle. As with the PostgreSQL test case I’ll create two test tables in Oracle: One using 10 columns and one just using one column of type varray: SQL> create table single_values ( a number, b number, c number,…

Read More
Daniel Westermann

What is more efficient: arrays or single column values?

By | Database Administration & Monitoring | One Comment

In PostgreSQL ( as well as in other rdbms ) you can define columns as arrays. What I wondered is: What is more efficient when it comes to space: Creating several columns or just creating once column as array? The result, at least for me, is rather surprising. First, let’s create a table with columns of type int: postgres=# create table single_values ( a int, b int, c int, d int, e int, f int,…

Read More
Daniel Westermann

SQL Interpolation with psql

By | Database Administration & Monitoring | One Comment

The PostgreSQL psql utility provides some really nice features. One of these features is SQL interpolation which allows us to do interesting things, e.g. reading files and analyze the results directly in the database. This post will show how to use this by reading and analyzing sar files of a linux server. Usually linux hosts store sar statistics in the /var/log/sa directory: ls /var/log/sa sa02 sa03 sa04 sa05 sa06 sa07 sa08 sa09 sa10 sa11 sa12…

Read More
Daniel Westermann

A free PostgreSQL cloud database?

By | Database Administration & Monitoring | One Comment

Recently I was looking for a free PostgreSQL cloud database service for testing. Why? Because I’d like to use such a cloud instance for testing no matter on which workstation or OS I am currently on. Another reason is, that I could could prepare some demos at home and use the same demos at work without needing to worry about taking the database with me each time. There are probable many more services than the…

Read More