Join elimination rules

comment from extremedb here

I found 7 Rules for Join Elimination in last year

1.Primary Key-Foreign Key – normal join, Starting in 10gR2
2.Primary Key-Foreign Key – ANSI join, Starting in 11gR1
3.Primary Key-Foreign Key – (semi/anti) join, Starting in 11gR1
4.Unique Index – outer join, Starting in 11gR1

Every guru knows above 4 things

5.Primary Key-Primary Key – simple self join, Starting in 11gR2 –> you ‘ve aleady mentioned

There are two more things and one simular thing
6.Primary Key-Primary Key – self join filter subsumption, Starting in 11gR2
7.Join Back Elimination – Using Bit Map Join Index, Starting in 9iR1

Полезное по подсчетам Logical reads

Прочитал у Рендольфа

So here is an important point: If you want to understand the work Oracle has performed in terms of buffer visits you need to consider both, the number of logical I/Os as well as the number of buffers visited without involving logical I/O - this is represented by the “buffer is pinned count” statistics.
Quite often this fact is overlooked and people only focus on the logical I/Os - which is not unreasonable - but misses the point about pinned buffers re-visited without doing logical I/O.
Note that buffer pinning is not possible across fetch calls - if the control is returned to the client the buffers will no longer be kept pinned. This is the explanation why a the “fetchsize” or “arraysize” for bulk fetches can influence the number of logical I/Os required to process a result set.