John Watson's blog
The old Oracle join syntax really should be consigned to history.
Why do you sometimes not get partitionwise joins? Because the optimizer isn't clever enough. Reference partitioning has many benefits, one of which is that the optimizer understands it. You will always get a partitionwise join if your tables are reference partitioned.
Several times I have had to deal with people who do not want to define constraints. I have never understood why they don't, because my experience is that the more constraints you can define, the better Oracle will perform. If anyone knows where the idea that not defining constraints is a Good Thing comes from, I would be interested to know.
Following are two very simple examples of constraints allowing the optimizer to develop better plans.
First, foreign key constraints. These give the optimizer a lot of information about the data that may mean it can cut out whole tables from a query.
Part of my job is teaching for Oracle University, and I'm often asked about OCP exam technique. Here are a few hints. The OCM exam is very different, and the confidentiality rules forbid me from discussing it, so please don't ask.
This note is an attempt to summarize some of the 11gR2 facilities that allow for greater virtualization of resources, reducing reliance on networking facilities supplied by the operating system. All corrections welcome.