A recent post on the OTN database forum reminded me how easy it is to forget to keep repeating a piece of information after the first couple of hundred times you’ve explained it. No matter how “intuitively obvious” it is for one person, it’s new to someone else.
Here’s an execution plan that raised the question that prompted this note – it comes from calling dbms_xplan.display_cursor() with the ‘allstats last’ format option after enabling rowsource execution statisics (using hint gather_plan_statistics, or setting parameter statistics_level to all, or setting hidden parameter “_rowsource_execution_statistics” to true):
The OTN database forum supplied a little puzzle a few days ago – starting with the old, old, question: “Why is the plan with the higher cost taking less time to run?”
The standard (usually correct) answer to this question is that the optimizer doesn’t know all it needs to know to predict what’s going to happen, and even if it had perfect information about your data the model used isn’t perfect anyway. This was the correct answer in this case, but with a little twist in the tail that made it a little more entertaining. Here’s the query, with the two execution plans and the execution statistics from autotrace:
A SQLT report has all kinds of pertinent information including—to name just a few—optimizer settings, indexes, statistics, plan history, and view definitions. However, sometimes a SQLT report is not enough to solve a SQL performance problem.(read more)
SQL supports three types of join operation. Most developers learn the inner join first. But there are two other join operations you should know about. These are the outer join, and the full outer join. These additional join types allow you to write in essence could be termed as optional joins.
The so-called inner-join is the default. It's the happy path from a theory perspective, and it's the join type most SQL developers learn first. Use it to combine related rows from two or more tables.
For example, perhaps you want to report on all the customers in the AdventureWorks database. You might begin working that business problem by writing the following query:
Joins are fundamental in SQL, and are used in most every production query.
There are three types in particular that every developer should fully
Read the full post at www.gennick.com/database.
The important thing to understand is the data does not change just because it is managed differently. If the data does not change, then the entities and the relationships contained in the data cannot change either. The entities and the relationships between them have not changed since the dawn of time. They were the same in the days of network database management systems which came before relational database management systems, they stayed the same when object-oriented database management systems came along, and they are the same now that we have NoSQL databases.(read more)
Oracle Corporation openly admits that NoSQL database management systems have the performance advantage over relational database management systems “when data access is ‘simple’ in nature and application demands exceed the volume or latency capability of traditional data management solutions.” Database professionals should therefore look seriously at NoSQL technology.(read more)
Brian Hitchcock’s secret is simple. For twelve years, he has been reading books on Oracle Database. And taking extensive notes. And publishing them in the NoCOUG Journal. My secret is simple. For twelve years, I have been reading the extensive book notes that Brian has been publishing in the NoCOUG Journal. I’m the editor of the NoCOUG Journal but that’s not the point. It’s that simple.
I’m going to be at the OUG Scotland conference on 22nd June, and one of my sessions is a panel session on Optimisation where I’ll be joined by Joze Senegacnik and Card Dudley.
The panel is NOT restricted to questions about how the cost based optimizer works (or not), we’re prepared to tackle any questions about making Oracle work faster (or more efficiently – which is not always the same thing). This might be configuration, indexing, other infrastructure etc.; and if we haven’t got a clue we can always ask the audience.