In a recent article about dropping columns from a table I left hanging the question of what you might do after you have dropped the column, because dropping a column is really nothing more than a special case of the “massive delete”. In this article, then, I’m going to lay the groundwork for how we ought to think around the… Continue Reading →

12c, All Things Oracle Full Articles

In the previous article about the Oracle 12c TOP-Frequency histogram, we saw that Oracle carries out a preliminary determination step to see how dominant the TOP-N values are with regards to the total number of rows in the data set. I demonstrated that a TOP-Frequency is possible provided a threshold condition is satisfied. This second article explores the Hybrid histogram type… Continue Reading →

12c, All Things Oracle Full Articles

As of Oracle version 12.1, if a number of buckets N in a histogram is less than the number of distinct values in the data set, Oracle will not generate the instable and imprecise legacy Height Balanced histogram. Instead, two new types of histogram are possible: TOP-Frequency and Hybrid. In order to select between the later and the former type… Continue Reading →

12c, All Things Oracle Full Articles

I have my Oracle Support account configured to send me a daily email of “hot topics”, and I’ve set it up so that the email lists the 100 bugs that have been updated most recently by Oracle support. One of the bugs that came up on 9th Jan was described as: “Bug 18700681 : DROP COLUMNS USING ALTER TABLE VERY… Continue Reading →

12c

In an Oracle cluster prior to version 12.1.0.2c, when a split brain problem occurs, the node with lowest node number survives. However, starting from Oracle Database 12.1.0.2c, the node with higher weight will survive during split brain resolution. In this article I will explore this new feature for one of the possible factors contributing to the node weight, i.e. the… Continue Reading →

12c

Oracle Data Pump was introduced in Oracle Database 10g to enable very high-speed transfer of data and metadata between databases. A salient feature of Data Pump is that it can parallelize the export and import jobs for maximum performance. However, prior to Oracle Database 11g release 2 (11.2), if a Data Pump job is executed in parallel on an instance… Continue Reading →

11g, 12c, All Things Oracle Full Articles

I’m that grumpy old guy who comes to your party and sits in a corner with a face like a dog’s backside. You know, that guy who hates everything new: Adele? She’s no Whitney Houston! Bluray? It’s not as good as Betamax! TOAD? It’s not as good as SQL Plus! I’m exaggerating – but only a little. Unless your work… Continue Reading →

All Things Oracle Full Articles, Database Development

This article is about functions and how to get more out of them if you combine them with record structures. Functions are the instrument in the Oracle Database to execute code and return a value. Nothing new here, everybody who develops in PL/SQL on the database knows that. In case you’re just starting with Oracle and want to know more about… Continue Reading →

All Things Oracle Full Articles, Database Development, Oracle Database, PL/SQL

We all know this: if a long-bearded prophet came down from a mountaintop bearing the ten commandments of Oracle programming, one of them might read thus: Thou canst select from many tables, but thou may only update, delete from or insert into one table at a time. Right? Well, not exactly. Because you can actually add data to multiple tables… Continue Reading →

All Things Oracle Full Articles

In part 5 of this series I started working through a list of queries designed to demonstrate ways in which the optimizer can produce bad cardinality estimates. In this installment I’m going work through the remainder of the list. The outstanding queries are as follows: select count(*) from t1 where trunc_5 > ( select max(trunc_5) from t1 where mod_200 =… Continue Reading →

12c, All Things Oracle Full Articles, Performance Tuning