Oracle Collection in SQL part 2

In Part1, We explored Nested Table and saw its usage in SQL statement. In this post, We will check impact of SQLs referencing  Nested table. We will use the same structure that we created in post 1.

Along with that I created additional tables with same number of rows. We checked explain plan of both of queries. One using Nested table second without using Nested table. Before running our SQL(s), I gather Optimizer Statistics using DBMS_STATS package.

  1  begin

  2  dbms_stats.gather_schema_stats(ownname=>'scott',estimate_percent=>100,Method_Opt=>'for all columns size 1',cascade=>true);

  3* end;

SQL> /

1:-  Query without using Nested Table.

SQL> set autotrace traceonly explain stat

1  SELECT SCI2.*,CI.* FROM SCOTT.STU_COURSE_INFO2 SCI2,SCOTT.COURSE_INFO2 CI

 2     WHERE SCI2.STU_ID=CI.STU_ID

 3      AND SCI2.COURSE_ID=CI.COURSE_ID

 4*    ORDER BY SCI2.STU_ID,CI.COURSE_ID

As we can see the Explain Plan, Statistics, Access and filter of the query. Now we check the  same of query using Nested Table.

Query 2:-

  1   SELECT SCI.STU_ID,SCI.STU_NAME,X.COURSE_ID,X.COURSE_NAME FROM SCOTT.STU_COURSE_INFO SCI,TABLE(SCI.COURSE_INFO) X

  2   where x.stu_id=sci.stu_id

  3* ORDER BY 1,3

SQL> /

begin

dbms_stats.set_table_stats(ownname=>'scott',tabname=>'course_info2',numrows=>1000000,numblks=>100000,avgrlen=>350);

end;

/

begin

dbms_stats.set_table_stats(ownname=>'scott',tabname=>'stu_course_info',numrows=>1000000,numblks=>100000,avgrlen=>350);

end;

After STATS setting on table:

Query 1:-

1  SELECT SCI2.*,CI.* FROM SCOTT.STU_COURSE_INFO2 SCI2,SCOTT.COURSE_INFO2 CI

 2     WHERE SCI2.STU_ID=CI.STU_ID

 3      AND SCI2.COURSE_ID=CI.COURSE_ID

 4*    ORDER BY SCI2.STU_ID,CI.COURSE_ID

Query 2:-

  SELECT SCI.STU_ID,SCI.STU_NAME,X.COURSE_ID,X.COURSE_NAME FROM SCOTT.STU_COURSE_INFO SCI,TABLE(SCI.COURSE_INFO) X

 where x.stu_id=sci.stu_id

 ORDER BY 1,3

As  we can see, Although Nested tables helped us by “no Need to have” another table to store Course information but has hindered performance of our query. I have tried to confirm this by setting Statistics of all the participating tables.  

Not only, Query using Nested tables have taken more time to execute, but it has also did more Physical I/O and more sorts in memory.

So while using, Nested tables in query, Developer should consider pros and cons of this approach.

Any comment, suggestion or correction is welcome.