Oracle Performance issues on using subquery in an "In" orperator

Question

I have two query that looks close to the same but Oracle have very different performance.

Query A

Create Table T1 as Select * from FinalView1 where CustomerID in ('A0000001','A000002')

Query B

Create Table T1 as Select * from FinalView1 where CustomerID in (select distinct CustomerID from CriteriaTable)

The CriteriaTable have 800 rows but all belongs to Customer ID 'A0000001' and 'A000002'. This means the subquery: "select distinct CustomerID from CriteriaTable" also only returns the same two elements('A0000001','A000002') as manually entered in query A

Following is the query under the FinalView1

create or replace view FinalView1_20200716 as
select
    Customer_ID,
    
    <Some columns>
from
    Table1_20200716 T1 
    INNER join Table2_20200716 T2 on 
            T1.Invoice_number = T2.Invoice_number
        and
            T1.line_id = T2.line_id
    left join Table3_20200716 T3 on
        T3.id = T1.Customer_ID

    left join Table4_20200716 T4 on
        T4.Shipping_ID = T1.Shipping_ID
    left join Table5_20200716 Table5 on
        Table5.Invoice_ID = T1.Invoice_ID
    left join Table6_20200716 T6 on
        T6.Shipping_ID = T4.Shipping_ID
    left join First_Order first on
        first.Shipping_ID = T1.Shipping_ID
;

Table1_20200716,Table2_20200716,Table3_20200716,Table4_20200716,Table5_20200716,Table6_20200716 are views to the corresponding table with temporal validity feature. For example

The query under Table1_20200716 Create or replace view Table1_20200716 as

select
*
from Table1 as for period of to_date('20200716,'yyyymmdd')

However table "First_Order" is just a normal table as

Following is the performance for both queries (According to explain plan):

Query A:

Cardinality: 102 Cost : 204

Total Runtime: 5 secs max

Query B:

Cardinality:27921981

Cost: 14846

Total Runtime:20 mins until user cancelled

All tables are indexed using those columns that used to join against other tables in the FinalView1. According to the explain plan, they have all been used except for the FirstOrder table.

Query A used uniquue index on the FirstOrder Table while Query B performed a full scan.

For query B, I was expecting the Oracle will firstly query the sub-query get the result into the in operator, before executing the main query and therefore should only have minor impact to the performance.

Thanks in advance!

Answer 1

As mentioned from my comment 2 days ago. Someone have actually posted the solution and then have it removed while the answer actually work. After waiting for 2 days the So I designed to post that solution.

That solution suggested that the performance was slow down by the "in" operator. and suggested me to replace it with an inner join

Create Table T1 as 
Select 
    FV.* 
from 
    FinalView1 FV 
    inner join (
        select distinct 
            CustomerID 
        from 
            CriteriaTable
    ) CT on CT.customerid = FV.customerID;

Result from explain plan was worse then before: Cardinality:28364465 (from 27921981) Cost: 15060 (from 14846)

However, it only takes 17 secs. Which is very good!