Here’s my contribution to the ODC Appreciation Day. Pattern Matching in SQL, using the MATCH_RECOGNIZE clause, is one of my favorite features, but only recently I’ve used it “for real”. MATCH_RECOGNIZE allows us to perform enhanced analysis of row sequences, and to detect sequences that match complex patterns. This feature gave a significant boost to […]
Index Hints and Distributed Queries
There are two ways to specify indexes in optimizer hints: by the index name, or by the index’s (leading) columns: The latter is usually preferred, as the writer’s intention is clearer, and it’s immune to changes of the index name. Note: one can present the opposite argument, that specifying the index name is immune to […]
Dropping Virtual Columns Causes Unnecessary Invalidation
When you drop a column from a table (or set a column unused), any view that references this column becomes invalid. As of Oracle 11gR1, where Fine Grained Dependency Tracking was introduced, views that reference other columns of the table, but do not reference the dropped column, should not become invalid. And this is usually […]
EBR – Part 10: Data Dictionary Views for Editioning Views
This is part 10 of a post series about Oracle Edition-Based Redefinition. Visit the index page for all the parts of the series Introduction In a previous post I wrote about Editioning Views and their significant role in online application upgrades using EBR. In this post we’ll see how editioning views are represented in the […]
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 […]
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 […]