Extract CSV from CLOB with JSON arrays

Marc Bleron blogged about CSV CLOBs and JSON_TABLE two years ago. Here's my contribution to improve on a great idea.

Read CLOBs fast with less memory

Reading a big CLOB is like trying to eat a burger all at once: it will take you forever if you don't choke. Why not cut that CLOB into bite-size chunks? It's faster, uses less memory - and it's good table manners...

Generate JOINs among related tables

After finding the shortest "foreign key" path between two tables, I'll try to generate a SELECT statement that joins all the related tables.

Split a string again and again

I recently needed to gather multiple columns from multiple rows into one string, then split them out again. JSON_TABLE did both splits at the same time!

Wrong Results with IOT, Added Column and Secondary Index

I found a “wrong results” bug yesterday, easily reproduced in 11g, 12c and 18c.

In short, we may get wrong results under the following circumstances:

  • We have an Index-Organized Table (IOT) with multi-column primary key, populated with rows
  • The table has a secondary index on part of the primary key columns
  • We add another column to the existing IOT
  • We select from the IOT while accessing it via the secondary index

Following is a simple (more...)

Long-running delete of synopses during table stats gathering

Oracle 11.2.0.4

I recently encountered one long-running session and several blocked sessions, all performing the same delete operation.

--SQL_ID dsf21kcbjqfyk
DELETE FROM SYS.WRI$_OPTSTAT_SYNOPSIS$ WHERE BO# = :B2 AND INTCOL# = :B1;

One session had been running for several hours, and the other sessions were all blocked by a row lock from the first session. Unfortunately, each of these sessions was trying to gather stats on new partitions of tables as part (more...)

EBR – Part 9: Adding a New Column

This is part 9 of a post series about Oracle Edition-Based Redefinition.

Visit the index page for all the parts of the series

Introduction

In part 7 (“Editioning Views”) I introduced our third development use case – adding a new column that represents a new business logic.

I emphasize the fact it’s a new business logic, because sometimes we add new columns that come instead of existing columns, for replacing an existing business logic. (more...)

EBR – Part 8: The Last Planned Downtime

This is part 8 of a post series about Oracle Edition-Based Redefinition.

Visit the index page for all the parts of the series

Introduction

I concluded the previous post by stating that the application code should never reference tables directly; instead, every table should be covered by an editioning view and the application code should reference the views.

Starting from Scratch?

If you start developing a new system from scratch, I strongly recommend to follow (more...)

EBR – Part 7: Editioning Views

This is part 7 of a post series about EBR.

Visit the index page for all the parts of the series

Introduction

Our next use case is adding a column that represents a new logic to the PEOPLE table, and making the corresponding changes in the PEOPLE_DL and APP_MGR packages. Of course, as we speak about EBR, the upgrade from the previous version to the new one should be online.
An online upgrade means that (more...)

Filtering outliers from Oracle APEX activity logs

Last year I described a simple test case that described how to remove outliers from a fictional dataset using the STDDEV() analytical function .
http://www.grassroots-oracle.com/2017/06/removing-outliers-using-stddev.html

I want to follow this up with a practical case using one of my favourite data sets - the apex_workspace_activity_logs that record who opened what page, in what context, and how long it took to generate.

I've been keeping an eye on the performance of a particular page, (more...)

PostgreSQL Calling File

Somebody asked: How do you run a script file from PostgreSQL’s psql prompt? I created two files to answer the question. Here are the two files:

Static File

SELECT 'Hello World!';

Dynamic File

SELECT 'Hello ['||current_user||']!';

It’s a simple solution, you put a \i or \include before the script file name, like:

\i helloworld.sql

or

\include hellowhom.sql

I hope this helps those trying to call SQL script files from an interactive psql session.

The Performance of the FIRST and LAST Functions

Overview

One of the first posts I wrote in this blog (almost five years ago) was about the FIRST and LAST aggregate functions.
These functions are, in a way, extended versions of the much more popular aggregate functions MIN and MAX.
MIN and MAX allow you (conceptually) to sort a group of rows by some column and return the value of that column from the first or last row in the sorted group.
The FIRST (more...)

Beware (Sort-Of) Ambiguous Column Names In Sub-Selects

This morning I received an UPDATE statement from a developer that I was testing. It ran without errors but then I saw that it updated 5 rows when it should have only updated 3. The reason gave me a little shock so I whipped up a simple test-case to reproduce the problem.

First we create two tables:

CREATE TABLE foo (
    id int
    , name varchar(30)
);
CREATE TABLE
CREATE TABLE (more...)

EBR – Part 6: Editionable and Non-Editionable, Editioned and Non-Editioned

This is part 6 of a post series about EBR.
In part 1 we created the baseline model and code – a table (PEOPLE) and two packages (PEOPLE_DL and APP_MGR).
In part 2 we saw that even a simple change – a package body compilation – can be dangerous in a busy system.
In part 3 we learned about editions and how they can be used for solving the problems described in part 2, so (more...)

Adding a Datafile to Temp Tablespace

When monitoring Tablespace Usage (see my Tablespace Usage blog post for more info), there comes a point when you need to add a datafile to the temp tablespace to allow for growth and more importantly get below a monitoring threshold for example in OEM or OpsView.

Query to see Current Temp Datafiles State

To see the current state of the temp datafiles:

set pages 999
set lines 400
col FILE_NAME format a75
select d.TABLESPACE_NAME,  (more...)

Friday Philosophy – Explaining How Performance Tuning Is Not Magic?

Solving performance issues is not magic. Oh, I’m not saying it is always easy and I am not saying that you do not need both a lot of knowledge and also creativity. But it is not a dark art, at least not on Oracle systems where we have a wealth of tools and instrumentation to help us. But it can feel like that, especially when you lack experience of systematically solving performance issues.

SQL statement (more...)

SQL with Apache Spark, easy!

Reading about cluster computing developments like Apache Spark and SQL I decided to find out.

What I was after was to see how easy is to write SQL in Spark-SQL. In this micro-post I will show you how easy is to SQL a JSON file.

For my experiment I will use my chrome_history.json file which you can download from your chrome browser using the extension www.JSON-XLS.com. To run the SQL query on (more...)

SQL Magic Squares – or Why the Optimizer does not like Magic

A long-time player at the Oracle Dev Gym tried his hand at generating Magic Squares using SQL.



When he attempted to tune his statement a bit, he was surprised that it didn't go a little faster as expected, rather it went from 2 minutes to 45 minutes? At that point he contacted me to see if I could explain the mystery...



So thank you, Hamid Talebian, for the interesting case. It was fun to play (more...)

Ergänzungen zu coalesce und NVL

Vor längerer Zeit hatte ich hier gelegentlich auf Artikel verwiesen, die sich mit dem unterschiedlichen Verhalten von NVL und coalesce beschäftigten und einerseits auf die short-circuit evaluation mit coalesce und andererseits auf deren Ausklammerung im Fall von Sequencen hinwiesen. Jetzt haben die Herren Lewis und McDonald dazu ergänzende Beobachtungen geliefert.
  • Jonathan Lewis weist darauf hin, dass coalesce beim costing schlechter abschneidet als NVL, weil es mit dem Standardwert von 1% für Gleichheit operiert, der für (more...)

Dynamic LOV with Pipeline function

A new year brought me some new tasks. I had to take over a generic Excel import and the customer wanted some extension by checking if the join on the master tables were successful.

Unfortunate we were talking about a generic solution which meant that all the configuration was saved inside tables including the LOV-tables which were saved as simple select statements.

Goal:
Show all import rows/values which were not fitting towards the master data.

(more...)