How to calculate Join cost? I want to know the disk operations?

I want some help regarding join processing

1. Nested Loop Join
2. Block Nested loop join
3. Merge join
4. Hash join

I search but did not find some l开发者_StackOverflow社区ink which also provide mathematical examples of calculation?

e.g.

Consider the natural join R & S of relations R and S, with the following information about those relations:

Relation R contains 8,000 records and has 10 records per page

Relation S contains 2,000 records and has 10 records per page

Both relations are stored as sorted files on the join attribute

how many disk operations would it take to process the upper four joins?

---

Do you have a specific dbms in mind?

For Oracle, you'd have to know the block size, the configuration for db_file_multiblock_read_count and the expected nr of blocks already in cache, the high watermark for each table, existing indexes and their clustering factor to mention a few things that will affect the answer.

As a general rule, whenever I fully join two tables, I expect to see two full table scans and a hash join. Whenever I join parts of two tables, I expect to see a nested loop driven from the table with the most selective filter predicate.

Whenever I get surprised, I investigate the statistics and the above mentiond things to validate the optimizer choice.