As I discussed previously in Part I, the space occupied by orphaned row entries associated with asynchronously maintained global indexes is not automatically reclaimed by subsequent DML operations within the index. Hence the need to clean out these orphaned index entries via the various options discussed in Part II. However, a good question by Jason […]
In Part I, I discussed how global indexes can now be asynchronously maintained in Oracle 12c when a table partition is dropped or truncated. Basically, when a table partition is dropped/truncated with the UPDATE GLOBAL INDEXES clause, Oracle simply keeps track of the object numbers of those table partitions and ignores any corresponding rowids within the […]
I’ve written a few notes about anomalies in subquery factoring (with subquery) in the past, principally making a fuss about the fact that moving an inline view into a “with subquery” can cause a plan to change even when the internal code moves the subquery back in line. With the arrival of 12c one of my first sets of tests was to rerun all the examples to see how many of them had been addressed. I hadn’t written about as many examples as I had thought, and some of them had been fixed before 12c, but here are few references to a couple of outstanding items that I thought worth a mention:
I’ve posted this note as a quick way of passing on an example prompted by a twitter conversation with Timur and Maria about Bloom filters:
— Jonathan Lewis (@JLOracle) August 5, 2013
The Bloom filter (capital B because it’s named after a person) is not supposed to appear in Oracle plans unless the query is executing in parallel but here’s an example which seems to use a serial Bloom filter. Running in 22.214.171.124 and 126.96.36.199 (the results shown are the latter – the numbers are slightly different between versions):
I posted a couple of examples some time ago of oddities and boundary cases for frequency histograms on character columns. Part of the process of playing around with the 12c Beta was to re-run such cases to see if newer code made any difference. Looking at these examples, one was fixed (or improved, at least) the other wasn’t, so I’ve added a footnote to each and produced this little note to highlight the changes:
I previously looked at how global index maintenance was performed when dropping a table partition prior to Oracle Database 12c. Let’s see how things have now changed since the introduction of 12c. Let’s start by creating the same partitioned table and global indexes as previously: If we look at the current state of affairs, all […]
In part 2 of this mini-series I’ll be describing the new mechanism for the simple frequency histogram and the logic of the Top-N frequency histogram. In part 3 I’ll be looking at the new hybrid histogram. You need to know about the approximate NDV before you start – but there’s a thumbnail sketch at the end of the posting if you need a quick reminder.
To allow for collection of simple frequency histogram – record the first rowid for each hash value generated and count the number of times the hash value is generated. If, by the end of the table you have no more than the requested (default 254, max 2,000) distinct hash values you can look up the actual values with a query by rowid.
Oracle 12c includes a really neat feature I first mentioned after Oracle OpenWorld 2012. You can now access your DBFS file systems over HTTP/HTTPS, FTP or WebDAV. I’ve been having a play with it over the last couple of days, so I thought I would write it up here.
This should be quite a welcome addition for those people on platforms other than Linux, who don’t get to use FUSE.
As mentioned in the article, WebDAV support under Windows 7 broken. Even the Oracle manual has a screen shot from Windows XP. I can’t believe Windows 7 has been out all this time and Microsoft have not bothered to fix it…
I’ve taken my first tentative steps into upgrading to Oracle 12c.
This article is targeted at the type of information you are likely to need for the 12c OCP DBA exam. In reality, upgrades are too important to rely on a generic article like this. Every time I do a real upgrade I go back to the upgrade docs and work my way through them. That’s the only way to make sure you’ve not missed out an important step, specific to the features you are using.
My first impressions are: