During a performance problem I had with a query I wanted to create an index on the table. Since this was a large table I wanted the index to include all the needed columns so I won’t need to access the table at all. The query also used GROUP BY and MAX, so I thought a descending index would be best, as it will easier for Oracle to find the highest value (as it will be the first one).
Since Oracle started supporting ANSI joins in 9i, the Oracle community was divided into two groups: one loved it and tried to convince everyone to use it, and the other hated it and has never switched. As you probably understand from the title, I’m in the second group, and this is why.
Oracle Open World was interesting like every year. This year, Oracle started talking about features that will probably be in Oracle 19c (which is the last 12.2 release). The rumor says that it will be out in the first half of 2019 (somewhere around April-May).
As you might know, I didn’t attend a lot of sessions, but managed to gather some information about expected features (given Oracle’s safe harbor of course).
This topic has been sitting in my backlog for a long time and I finally decided to write it. Analytic functions are not so new anymore (they’ve been around since Oracle 8i), but they are still a very powerful tool.
This is based on a real case I had quite a few years ago. A client came to me with a question regarding a graph they had to generate.
Here is an interesting optimizer case where updated statistics and histograms cannot solve the performance problem. This might be an uncommon case, but it happened for one of my clients and this post is the result of some research on this.