Optimizer cost with 'IN' Clause

[aghosh]

Hi,

Here is the output from 2 different Explain Plans

Situation 1
==============================================================
select * from SOA_DISPATCH_F
where process_date_time = sysdate;


Explain Plan Output :

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Hint=CHOOSE 4 K 245
PARTITION RANGE SINGLE KEY KEY
TABLE ACCESS FULL SOA_DISPATCH_F 4 K 2 M 245 KEY KEY


As you can see the Bytes used = 2M and Cost is 245


Situation 2
============================================================
select * from SOA_DISPATCH_F
where process_date_time in (select sysdate from dual)



Explain Plan Output :


Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Hint=CHOOSE 378 K 10756
HASH JOIN 378 K 197 M 10756
VIEW VW_NSO_1 82 492 6
SORT UNIQUE 82 6
TABLE ACCESS FULL DUAL 82 1
PARTITION RANGE ALL 1 44
TABLE ACCESS FULL SOA_DISPATCH_F 2 M 1G 10736 1 44

As you can see here the Bytes = 1G and Cost = 10736


----------------------------------------------------------------

My SOA_DISPATCH_F table is partitioned on process_date_time.

Can anyone tell me why there is such a significant change (2M to 1G) in the Bytes due to change from "=" to "IN".

Thank you,

Amit

[aghosh]

I forgot to mention one more thing.....

In Situation 1,
It is accessing only one partition (called "KEY").

But in the Situation 2 it is scanning all partitions from 1 to 44.


Can anyone explain this behaviour please.

[sgodbole]

I think in second case the optimizer is not doing partition pruning.

[aghosh]

How do I force the second query to do partition pruning.

[tamilselvan]

If you are using 9i, try this

WITH SD_TBL AS ( select sysdate sys_date from dual)
SELECT a.* from SOA_DISPATCH_F A, SD_TBL B
where a.process_date_time = B.sys_date ;

The above SQL will use single partition.