why a decode function with or without a default value,leading to diffrent execution plan?
********************************************************************************** step1、prepare data
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table t_object as select * from dba_objects;
SQL> create index ind1 on t_object(object_id,object_type);
SQL> create table temp as select * from t_object where rownum<=50;
********************************************************************************** step2、sql text1 and Execution Plan
SELECT
*
FROM
t_object A
WHERE
( a.OBJECT_ID, a.OBJECT_TYPE, DECODE( a.SUBOBJECT_NAME, '', '-1', a.SUBOBJECT_NAME ) ) IN ( SELECT t2.OBJECT_ID, t2.OBJECT_TYPE, DECODE( t2.SUBOBJECT_NAME, '', '-1', t2.SUBOBJECT_NAME ) FROM temp t2 );