MEMBER OF Vs TABLE OF CAST in Oracle

This articles is to create a Dynamic IN-List and to compare the performance between the options MEMBER OF and TABLE OF CAST.

             The member we say dynamic IN-List, the data to be compared using IN clause is going to be dynamic and would require a temporary storage as a place holder. This temporary storage can be a Global Temporary Table or Nested Array. Let's proceed with the later option, 

Dynamic IN - List with TABLE OF CAST - Creating the required tables.

 

Gathering Stats on the table, with estimate as 100 leading to a stats of actual data set

Creating the SQL Type Array, that is a place holder to hold the run time IN-List data

SQL> select count(*) from tbl_member;

  COUNT(*)                                                                      

----------                                                                      

     33513                           

Array "l_object_tbl" would hold the entire IN-List values and which can be passed as parameters also.

The time taken by TABLE OF CAST is is 58 milli seconds. Lets repeat the same steps using MEMBER OF condition

Dynamic IN - List with MEMBER OF Condition

The time taken by TABLE OF CAST is relatively faster than MEMBER OF condition.  So as to identify the root cause of the issue, have re-executed above set of code with trace enabled. The   execution plan provides all the necessary info we need, 

Execution Plan of the Query with Table Of Cast - From Trace

FETCH #9:c=0,e=169163,p=101,cr=31,cu=0,mis=0,r=9,dep=1,og=1,plh=2623979658,tim=33047507231

STAT #9 id=1 cnt=9 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=31 pr=101 pw=0 time=78573 us)'

STAT #9 id=2 cnt=9 pid=1 pos=1 obj=0 op='NESTED LOOPS  (cr=22 pr=61 pw=0 time=42121 us cost=49 size=640 card=20)'

STAT #9 id=3 cnt=20 pid=2 pos=1 obj=0 op='COLLECTION ITERATOR PICKLER FETCH (cr=0 pr=0 pw=0 time=18 us cost=29 size=40 card=20)'

STAT #9 id=4 cnt=9 pid=2 pos=2 obj=75755 op='INDEX UNIQUE SCAN PK_TBL_MEMBER (cr=22 pr=61 pw=0 time=0 us cost=0 size=0 card=1)'

STAT #9 id=5 cnt=9 pid=1 pos=2 obj=75754 op='TABLE ACCESS BY INDEX ROWID TBL_MEMBER (cr=9 pr=40 pw=0 time=0 us cost=1 size=30 card=1)'

CLOSE #9:c=0,e=2,dep=1,type=3,tim=33047507701

Execution Plan of the Query with MEMBER OF - From Trace

FETCH #8:c=62400,e=136205,p=196,cr=199,cu=0,mis=0,r=9,dep=1,og=1,plh=1345468690,tim=33201283248

STAT #8 id=1 cnt=9 pid=0 pos=1 obj=75754 op='TABLE ACCESS FULL TBL_MEMBER (cr=199 pr=196 pw=0 time=115332 us cost=59 size=50280 card=1676)'

CLOSE #8:c=0,e=1,dep=1,type=3,tim=33201283479

Now this makes things very clear that Query with MEMBER OF is not using the index. Does this mean that MEMBER OF function is hiding the real info from the Optimizer, we see in the coming post.