Monday, June 23, 2014

MEMBER OF comparison of PL/SQL and SQL

In the Kscope14 sunday symposium today, Steven Feuerstein explained that MEMBER OF syntax was slow in SQL and fast in PL/SQL. I challenged him that perhaps it was missing indexes on the nested table? My mistake - I got the task of testing it and see if that was the case... So I tested and was surprised at the answer.

I'm creating a nested table type and a table with a column of that type and populate it:

create type nested_varchar as table of varchar2(100)
/

create table father_table (
   id       integer primary key
 , name     varchar2(100)
 , children nested_varchar
)
   nested table children store as child_table
/

declare
   nested_variable   nested_varchar := nested_varchar();
begin
   nested_variable.extend(999999);
   for i in 1..999999 loop
      nested_variable(i) := 'Member number '||to_char(i,'FM099999');
   end loop;
   insert into father_table values (
      1
    , 'The father of 999999 children'
    , nested_variable
   );
   commit;
end;
/

begin
   dbms_stats.gather_table_stats(user,'FATHER_TABLE');
   dbms_stats.gather_table_stats(user,'CHILD_TABLE');
end;
/

Then I test MEMBER OF syntax in PL/SQL and SQL and compare to using WHERE on the child table in SQL:

declare
   nested_variable   nested_varchar := nested_varchar();
   time1             number;
   time2             number;
   time3             number;
   time4             number;
   foundid           father_table.id%type;
begin
   select children
     into nested_variable
     from father_table
    where id = 1;
   time1 := dbms_utility.get_time;
   if 'Member number 007500' member of nested_variable then
      dbms_output.put_line('PLSQL member found');
   end if;
   time2 := dbms_utility.get_time;
   begin
      select /* before index */ id
        into foundid
        from father_table
       where 'Member number 007500' member of children;
      dbms_output.put_line('SQL member found');
   exception
      when no_data_found then
         null;
   end;
   time3 := dbms_utility.get_time;
   begin
      select /* before index */ distinct f.id
        into foundid
        from father_table f
           , table ( f.children ) c
       where c.column_value = 'Member number 007500';
      dbms_output.put_line('SQL table found');
   exception
      when no_data_found then
         null;
   end;
   time4 := dbms_utility.get_time;
   dbms_output.put_line('PLSQL hsecs: '||(time2-time1));
   dbms_output.put_line('SQL 1 hsecs: '||(time3-time2));
   dbms_output.put_line('SQL 2 hsecs: '||(time4-time3));
end;
/

DBMS_OUTPUT of the above looks like this:

PLSQL member found
SQL member found
SQL table found
PLSQL hsecs: 0
SQL 1 hsecs: 54
SQL 2 hsecs: 7

Definitely the SQL MEMBER OF is the slowest implementation.
So then I try adding an index that should help:

create index child_table_value_ix on child_table (
   column_value, nested_table_id
)
/

begin
   dbms_stats.gather_table_stats(user,'FATHER_TABLE');
   dbms_stats.gather_table_stats(user,'CHILD_TABLE');
end;
/

And then I try my test again:

declare
   nested_variable   nested_varchar := nested_varchar();
   time1             number;
   time2             number;
   time3             number;
   time4             number;
   foundid           father_table.id%type;
begin
   select children
     into nested_variable
     from father_table
    where id = 1;
   time1 := dbms_utility.get_time;
   if 'Member number 007500' member of nested_variable then
      dbms_output.put_line('PLSQL member found');
   end if;
   time2 := dbms_utility.get_time;
   begin
      select /* after index */ id
        into foundid
        from father_table
       where 'Member number 007500' member of children;
      dbms_output.put_line('SQL member found');
   exception
      when no_data_found then
         null;
   end;
   time3 := dbms_utility.get_time;
   begin
      select /* after index */ distinct f.id
        into foundid
        from father_table f
           , table ( f.children ) c
       where c.column_value = 'Member number 007500';
      dbms_output.put_line('SQL table found');
   exception
      when no_data_found then
         null;
   end;
   time4 := dbms_utility.get_time;
   dbms_output.put_line('PLSQL hsecs: '||(time2-time1));
   dbms_output.put_line('SQL 1 hsecs: '||(time3-time2));
   dbms_output.put_line('SQL 2 hsecs: '||(time4-time3));
end;
/

Which gives me this output:

PLSQL member found
SQL member found
SQL table found
PLSQL hsecs: 0
SQL 1 hsecs: 50
SQL 2 hsecs: 0

The SQL that uses TABLE operator on children and a WHERE clause now uses the index to become fast. The MEMBER OF in SQL does not - it has not improved.

What surprised me in this quick little test is, that even without index the SQL MEMBER OF is still a lot slower than TABLE operator. My guess is that there has been no focus on optimizing the MEMBER OF operation?

The findings above were on a version 11.2.0.2.0. I might try it on a 12 and see if it has improved.

No comments:

Post a Comment