With reference to one of the question in forum about couple of days where it was asked regarding the Hash join index is favoured or Nested loops. The part of the story deals with big and small tables of size 550 MB and 26 GB. You can go through the below link for your reference.
Charles Hooper had provided very interesting test case and it was very informative. Please find the link below for your reference and with couple of questions across..!!
I had performed same test case, took the same scripts and perhaps got some interesting points too. There exists some slight change as just went for 10 byte instead of 200 byte fetch and changed the number of rows with respect to tables. T1 – as 1000000 records and T2 – 1000 records. Looking into the number volume of data and criteria or records which fetched across Optimizer things works in different way. It’s depends on the order of records, indexes availability and required columns which used against the tables to fetch the appropriate records.
If you check the explain plans of the queries, you find some interesting case where it went for merge join – instead of nested loops and hash join.
Please find the plan below reference
As per my knowledge it depends on the percentage of the data which we fetch from out of total volume that drives the things. when we go for the cases where some tables have less data compared across higher volumes of data. Let’s me test for other sorts of test cases further how optimizer goes…