Category Archives: SQL

ODC Appreciation Day : Pattern Matching in SQL

Oren Nakdimon Leave a Reply

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 […]

Continue reading

Index Hints and Distributed Queries

Oren Nakdimon 4 Replies

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 […]

Continue reading

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 […]

Continue reading

EBR – Part 8: The Last Planned Downtime

Oren Nakdimon 1 Reply

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 […]

Continue reading

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 […]

Continue reading

Excessive Locking when Dropping a Table

Oren Nakdimon Leave a Reply

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 […]

Continue reading