I have been programming in PL/SQL since 1993, more or less continuously (I’ve just realized that it’s exactly half of my life until now), so I know the PL/SQL syntax quite well. So when I saw the following piece of code a few days ago, I was willing to bet it would not compile: declare […]
Excessive Locking when Dropping a Table
I tried to drop a table today and failed due to “ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired”. That was weird because I knew that nobody had been using this table for months, and that the table had no enabled foreign keys. A quick investigation revealed the cause – the DROP […]
A Single Query with Many Filter Combinations – Part 3
In a recent post I suggested a way to write a single SQL query that filters a table by one or more of several columns. Here is the query from that post: select * from employees where rowid in ( select rid from ( select rowid rid from employees where department_id = :department_id union all […]
A Single Query with Many Filter Combinations – Part 2
In the previous post I suggested a way to write a single SQL query that filters the EMPLOYEES table (as an example) by one or more of the following columns: DEPARTMENT_ID, JOB_ID, MANAGER_ID and LAST_NAME. Here is the query from the previous post: select * from employees where rowid in ( select rid from ( […]
A Single Query with Many Filter Combinations
Let’s assume the EMPLOYEES table (from the HR schema) contains many records, and we want to write an (efficient) SQL query that filters it by one or more of the following columns: DEPARTMENT_ID, JOB_ID, MANAGER_ID and LAST_NAME. For example: in one execution we may want to get all the employees with DEPARTMENT_ID = 80 in […]
Constraint Optimization Summary
This is the last part of a series about Constraint Optimization. In this post I’ll summarize the conclusions from the previous parts. When we add a constraint to an existing table, there are two aspects that are worth taking into consideration: duration and availability. Duration When the table contains a significant number of rows, adding […]
Adding a Unique Constraint in an Online Way
Note: unlike most of my posts, this one assumes using Enterprise Edition I have a table t and I want to add a unique constraint on one of its columns – c1. The Offline Way The straightforward and most simple way to do it is using a single alter table statement: SQL> alter table t […]
Fast but Offline, or Online but Slow?
The Constraint Optimization series: Part 1: Optimization of Check Constraint Creation Part 2: Optimization that Violates Data Integrity Part 3: Optimization of Foreign Key Constraint Creation Part 4: (Lack of) Optimization of Unique Constraint Creation Part 5: Adding a Column with a Default Value and a Constraint Part 6: Fast but Offline, or Online but […]
EBR – Part 5: Explicit Actualization of Dependent Objects
This is part 5 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 […]
EBR – Part 4: Invalidation and Actualization of Dependent Objects
This is part 4 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 […]