Hi

I am new to oracle and not sure how to provide the liveSQL link.

I have 2 tables to join
huge_table contains about 1 billion rows
big_table contains about 100 million rows
and small tables contains 999 rows providing the condition to filter

The the small_table.num_id to match huge_table.num_id column

The problem I noticed is if I explicitly provide the values in where clauses it will be much faster compared using sub query .

Specifically when looking at the explain plan result
This query is much faster with cardinality of 100 and cost of 6

SELECT h.col_required, b.col_required, h.num_id
FROM 
huge_table h,
big_table b

WHERE h.row_id = b.xx_id
      AND b.status = 'up'
      AND h.num_id in ('num1', 'num2',... 'num100')  -- Explicitly providing the values that in 
                                                     -- small_table

This query with sub query is much slower with cardinality of 1445704 and cost of 660293

SELECT h.col_required, b.col_required, h.num_id
FROM 
huge_table h,
big_table b

WHERE h.row_id = b.xx_id
      AND b.status = 'up'
      AND h.num_id in ('SELECT num_id FROM small_table)  -- Using sub query 
                                                         -- provide the values

I also tried using WHERE exits or INNER JOIN the small_table both providing the similar result with sub query.


The question I have is if it is possible to have good performance without explicitly providing the values in where clause?


Thanks