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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
COLLECT INTO inside a Cursor Definition?
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 ( […]