SQL Performance (MOSC)

MOSC Banner

why a decode function with or without a default value,leading to diffrent execution plan?

edited Apr 8, 2022 1:01AM in SQL Performance (MOSC) 3 commentsAnswered

********************************************************************************** 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 );

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center