The KEY means its value would be determined at execution time and is unknown at parse time. The optimizer does not know yet what partition range would correspond to the join condition.
Also you condition
>
WHERE TAB1.TIME_D=TAB2.TIME_D
...
AND TAB1.TIME_D=10001;
>
has been rewritten as

WHERE TAB2.TIME_D=10001
...

based on the plan. It makes sense since TAB2 seems to be a much smaller table.

>
Is my partition is functioning correctly? because the 'KEY' should represent number '2', how can I know that.
>
Yes - it is.

Why do you think 'KEY' should represent number '2'? Key will represent the partition number that is actually used at runtime. That might be '2' or it might be '37'. It all depends on how many partitions there are and what partition '201101' represents.

And why did you post a plan that uses tables DWH_TIME_D and DWH_SALES_FACTS_F but a query that uses TAB1, TAB2 and different column names? All that does is confuse everything and make it harder to understand what is going on.

@rp0428 : I did change the table names as original tables have more columns and thought it will add some complexity, sorry if it creates confusion. The other thing about number '2' is, its shows this values in the plan instead of KEY when you give 'yw=201230' in the join condition. Now I can understand that this happened due to optimizer know the partition during the compile time itself.

Is there anyway to know the KEY value during the run time? Just to check how many partitions get read during the run-time.

in 11g and with the diagnostic and tuning pack licenced you can get some processing information in the sql monitor or using row source execution statistics (Maria Colgan explains both strateqies in her blog: https://blogs.oracle.com/optimizer/entry/how_do_i_know_if). But the plan only contains the partition number if the value is known when the query is optimized. If the FTS operations take some time you could take a look at v$session_longops to see some progress information.

Regards

Martin

P.S.: for interval partitioned tables the key pstart und pstop are without much information even if the range is known at the time of optimization (showing always 1 and 1048575).