There is often more than one way to write a query that returns the same results, but some methods may perform better than others. In this article, Ben Brumm discusses ten best practice when writing SQL code for Oracle. … Read more
This article was originally published on mikesmithers.wordpress.com. “You can’t have your cake and eat it!” This seems to be a regular refrain from the EU in the ongoing Brexit negotiations. They also seem to be a bit intolerant of “cherry picking”. I’ve never really understood the saying, “You can’t have your cake and eat it” – … Read more
Unit testing and even Test-Driven Development is possible for databases. In this article Samuel Nitsche makes the case for introducing unit tests to your database development, and suggests some lightweight ways to get started.… Read more
Most development teams have agreed upon some sort of naming convention as it typically increases the readability and comprehension of the source code. Defining these rules is one thing, but correctly and consistently applying them is another. Wouldn’t it be great if we were able to actually validate whether our team-specific naming rules have been applied the right way?… Read more
A microservice architecture can provide great benefits to certain types of applications that have pronounced troughs and peaks in demand, such as web-based shopping apps. With containerization, there is the promise of deploying containerized services in a matter of seconds to deal with demand. Has the Docker ecosystem now reached the level of maturity to allow this type of application to become standard practice?… Read more
I accidentally locked myself out of my house this evening, and so I had to climb up the wall like a burglar and clamber in through a bathroom window, while the neighbourhood kids all watched and giggled, probably taking secret photos to share on Snapchat or something. Embarrassing, yes, but what’s it got to do with Oracle? Well, the point… Read more
Oracle database, since its inception, has always used a very specific and unique architecture which served it very well. In earlier forms of this architecture, all components of the database were meant to work in a one-to-one mapping with each other. But from version 12c onwards, a completely new architecture has been introduced – Oracle Multitenant. In other words, from… Read more
In this sixth and final installment in this introductory series on JSON I’d like to revisit the question of generating JSON from your Oracle database. Cos it’s great to be able to consume a format; but if you can’t spit it out easily too you’ll probably want to look elsewhere. Easily, that’s the keyword in that sentence. In my previous… Read more
All databases stand on a tripod of datatypes: strings, numbers and dates. And so I’d imagine that by Day 2 or so of dabbling with Oracle you’d be starting to get reasonably comfortable with the VARCHAR2, NUMBER and TIMESTAMP data types. And that’s a good thing, familiarity with those three data types will take you a long way into a… Read more
PartitionedIndexEfficiency Although it is generally true that rebuilding an index represents a waste of time and resources, there nevertheless exist situations where degenerated indexes might benefit from reorganisation. When you think you are in such a situation, instead of shooting in the dark and rebuilding all indexes, you are better off finding and rebuilding only those indexes occupying more spac… Read more
Oracle RAC One Node, introduced in Oracle Database 11g Release 2 (11.2.0.2), is a single instance of an Oracle RAC-enabled database running on one node in a cluster. It lets you consolidate multiple databases onto a single cluster while providing high availability benefits of failover protection to the single instance databases. Oracle RAC One Node databases can be configured to…
… Read more
In the 3rd part of this series, I introduced you to the concept of the bind variables. We also learned their importance in regard to cursors, i.e. how with the presence of bind variables, cursor reparsing can be reduced significantly. But as is famously said in the world of performance tuning, there is nothing black or white: it’s all grey… Read more
All databases stand on a tripod of datatypes: strings, numbers and dates. And though they might dress them in fancy clothing – varchar2, clob, float, integer – strings are really just strings, and numbers are really just numbers. But dates — dates are interesting. In this article I’ll talk to you about dates, about time, and about how both ar… Read more
Here’s a simple trick you can play on your friends. Ask them to pick a number from 1 to 10, and tell them you’ll read their mind and magically extract from it the number they’ve chosen. Don’t give them too long to choose; then put on your best imitation of a magician and say… 7. I don’t know why, but… Read more
At times, we might need to duplicate a production pluggable database to a Past Point in Time without disturbing the production database itself, in order to: View the data in the Production Database (PDB) as it appeared then Perform testing Generate reports, etc. Duplication of a PDB slightly differs from that of a non-CDB. In order to duplicate the PDB, an… Read more
While looking at the results from a query against the dynamic performance view v$active_session_history during a recent trouble-shooting assignment I noticed a result that was so unusual that I decided I needed to get some information from the v$ash_info view over an interval of about 3 minutes – and it didn’t matter which three minutes in particular, any three (consecutive)… Read more
In the previous post I described a problem with the strategy that the optimizer uses for costing an index fast full scan, and the alternative strategy that Oracle had supplied at some point in the 10g timeline to allow a more rational cost to be calculated in special cases. In an earlier post still I had described the problem wit… Read more
In the first and the second parts of this series, we learned what query parsing is, how hard and soft parsing work, what their impact on the query is, and then moved on to understand the concept of parent and child cursors. In this third article, we will look at the issue of duplicate SQL statements and how Bind Variables… Read more
In my book “Cost Based Oracle – Fundamentals” (Apress 2005) I described a problem with the cost calculation for the index fast full scan: When you generate statistics for a table, one of the results is the number of blocks below the high water mark. When you generate the statistics for an index, you get the number of leaf blocks… Read more
In Oracle, under certain circumstances, an implicit data type conversion precludes the use of indexes. Perhaps you have a vague idea of what an implicit data type conversion hierarchy is, and you might even ignore the subtlety of the implicit data type conversion direction within a query predicate expression. Hopefully this article will shed some light on this concept. 1… Read more
Subscribe for more articles
Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed.
Compliant Database DevOps
