The basic formula for calculating the costs of a Nested Loop Join is pretty straightforward and has been described and published several times.<\/p>\n
In principle it is the cost of acquiring the driving row source plus the cost of acquiring the inner row source of the Nested Loop as many times as the driving row source dictates via the cardinality of the driving row source.<\/p>\n
Cost (outer rowsource) + Cost (inner rowsource) * Card (outer rowsource)<\/p>\n
Obviously there are cases where Oracle has introduced refinements to the above formula where this is no longer true. Here is one of these cases that is probably not uncommon.<\/p>\n
Let’s start with a simple two table join that shows above formula in action. It represents a parent-child relationship where the parent table has 10,000 rows with a unique identifier, and a child table with 100 rows each that map to a single parent row, having 1,000,000 rows in total.<\/p>\n
\r\nset echo \r\n\r\non create table t \r\nas \r\nselect \r\n rownum as id \r\n , rownum as attr1 \r\n , rpad('x', 100) as filler \r\nfrom \r\n dual \r\nconnect by \r\n level <= 10000 \r\n; \r\n\r\nexec dbms_stats.gather_table_stats(null, 't') \r\n\r\ncreate table t2 \r\nas \r\nselect \r\n rownum as id \r\n , mod(rownum, 10000) + 1 as fk \r\n , mod(rownum, 20) + 1 as attr1 \r\n , rpad('x', 100) as filler \r\nfrom \r\n dual \r\nconnect by \r\n level <= 1000000 \r\n; \r\n\r\nexec dbms_stats.gather_table_stats(null, 't2') \r\n\r\ncreate index t2_idx on t2 (fk); \r\n\r\nexplain plan for \r\nselect \/*+ use_nl(t t2) leading(t) index(t2) *\/ \r\n * \r\nfrom \r\n t \r\n , t2 \r\nwhere \r\n t.attr1 <= 500 \r\nand \r\n t2.fk = t.id; \r\n\r\nset pagesize 0 linesize 200 tab off \r\n\r\nselect * from table(dbms_xplan.display);\r\n<\/pre>\n