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.

Oracle Collections in SQL Part1

Well, We have heard a lot about advanced PL/SQL and particularly about Collection(s) of Oracle. Collection(s) comprise Nested Table, Varray and Index By Tables. Each has its own way of working. Most of time, these Collections are used in PL/SQL program. Eg Function, Procedure, Package.

It is not very often when we see any developer using these collections in SQL statement.  Main reason could be because they themselves have not seen actually someone using it. Using Collections in SQL statements add a little more complexity in statement structure so many developer(s) try to skip this and use their old and proven methodology.

In this post , we will discuss exact method to use Nested Table in SQL statement and performance impact on queries using such tables. In first part of this post we will discuss actual working of this and in second post we will discuss performance impact.

Not all three collections could be used in SQL. We can use only Nested Table and Varray in SQL statement.  For to be used in SQL statement these Collection / Types should be stored as an object in Database.

SQL> select banner from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

Step:- 1

Define a Object type and Map method. Following, I have created a type of Object Kind and defined a MAP method for comparison purpose.

SQL> CREATE TYPE SCOTT.COURSE_OBJ AS OBJECT

  2  (

  3  STU_ID NUMBER,

  4  COURSE_ID NUMBER,

  5  COURSE_NAME VARCHAR2(40),

  6  MAP MEMBER FUNCTION MAP1 RETURN NUMBER);

  7  /

1  CREATE TYPE BODY SCOTT.COURSE_OBJ AS

 2  MAP MEMBER FUNCTION MAP1 RETURN NUMBER IS

 3  BEGIN

 4  RETURN STU_ID||COURSE_ID;

 5  END MAP1;

 6* END;

Step 2:- Created a TYPE of Nested Table type and used Course_Obj Object Type that we created in Step 1.

SQL> CREATE TYPE SCOTT.COURSE_NST_TB AS TABLE OF SCOTT.COURSE_OBJ;

  2  /

Type created.

Step:- 3 Created a table and reference Nested Table (Scott.Course_Nst_Tb)  as a column in table. See the STORE AS clause of the Table definition. We can use USER_TYPES dictionary table to check information

Of TYPES created by user.

SQL> CREATE TABLE SCOTT.STU_COURSE_INFO(STU_ID NUMBER,STU_NAME VARCHAR2(40),COURSE_INFO SCOTT.COURSE_NST_TB)

  NESTED TABLE COURSE_INFO STORE AS COURSE_INFO;

Table created.

Elapsed: 00:00:00.29

Insert into Scott.Stu_Course_info

SQL> Insert Into Scott.Stu_Course_info Values (1,'ABC',scott.course_nst_tb(scott.course_OBJ(1,'Eelementry English')));

Insert into scott.Stu_course_info values (103,'SBX',scott.COURSE_NST_TB(scott.COURSE_OBJ(103,1001,'Elementry Physics')))

Insert into scott.Stu_course_info values (103,'SBX',scott.COURSE_NST_TB(scott.COURSE_OBJ(103,1002,'Elementry Science')))

Insert into scott.Stu_course_info values (103,'SBX',scott.COURSE_NST_TB(scott.COURSE_OBJ(103,1003,'Elementry Math')))

Insert into scott.Stu_course_info values (103,'SBX',scott.COURSE_NST_TB(scott.COURSE_OBJ(103,1004,'Elementry Chemistry')))

Insert into scott.Stu_course_info values (103,'SBX',scott.COURSE_NST_TB(scott.COURSE_OBJ(103,1005,'Elementry Pyscology')))

Insert into scott.Stu_course_info values (102,'ABC',scott.COURSE_NST_TB(scott.COURSE_OBJ(102,1001,'Elementry Physics')))

Insert into scott.Stu_course_info values (102,'ABC',scott.COURSE_NST_TB(scott.COURSE_OBJ(102,1002,'Elementry Science')))

Insert into scott.Stu_course_info values (102,'ABC’,scott.COURSE_NST_TB(scott.COURSE_OBJ(102,1003,'Elementry Math')))

Insert into scott.Stu_course_info values (102,'ABC',scott.COURSE_NST_TB(scott.COURSE_OBJ(102,1004,'Elementry Chemistry')))

Insert into scott.Stu_course_info values (102,'ABC',scott.COURSE_NST_TB(scott.COURSE_OBJ(102,1005,'Elementry Pyscology')))

(101,'XYZ',scott.COURSE_NST_TB(scott.COURSE_OBJ(101,1001,'Elementry Physics')))

Insert into scott.Stu_course_info values (101,'XYZ',scott.COURSE_NST_TB(scott.COURSE_OBJ(101,1002,'Elementry Science')))

Insert into scott.Stu_course_info values (101,'XYZ’,scott.COURSE_NST_TB(scott.COURSE_OBJ(101,1003,'Elementry Math')))

Insert into scott.Stu_course_info values (101,'XYZ',scott.COURSE_NST_TB(scott.COURSE_OBJ(101,1004,'Elementry Chemistry')))

Insert into scott.Stu_course_info values (101,'XYZ',scott.COURSE_NST_TB(scott.COURSE_OBJ(101,1005,'Elementry Pyscology')))

Elapsed: 00:00:00.01

SQL> Commit ;

Commit complete.

Elapsed: 00:00:00.00

SELECT * FROM SCOTT.STU_COURSE_INFO where stu_id='101'

STU_ID              STU_NAME                     COURSE_INFO

101                          XYZ                                      SCOTT.COURSE_NST_TB('SCOTT.COURSE_OBJ(101,1001,'Elementry Physics')')                                   

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

101                          XYZ                                      SCOTT.COURSE_NST_TB('SCOTT.COURSE_OBJ(101,1002,'Elementry Science')')                                        

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

101                                  XYZ                                      SCOTT.COURSE_NST_TB('SCOTT.COURSE_OBJ(101,1003,'Elementry Math')')                                       

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       

101                         XYZ                                      SCOTT.COURSE_NST_TB('SCOTT.COURSE_OBJ(101,1004,'Elementry Chemistry')')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          

101                                 XYZ                                      SCOTT.COURSE_NST_TB('SCOTT.COURSE_OBJ(101,1005,'Elementry Pyscology')')                                                        

Now! Here lies the little complexity. You cannot see the actual value of a NESTED type column by merely referencing column  in SELECT clause.

If  we fire any select statement having a column of Nested Type , The result set will be shown just like above.

The correct way to select any column defined as NESTED type follows:

Select sci.stu_id,sci.stu_name,x.* from scott.stu_course_info sci,table(sci.course_info)x

STU_ID                 STU_NAME                                 STU_ID                 COURSE_ID              COURSE_NAME  

---------------------- ---------------------------------------- ---------------------- ---------------------- -------------

103                           SBX                                      103                    1001                   Elementry Physics                       

103                           SBX                                      103                    1002                   Elementry Science                       

103                           SBX                                      103                    1003                   Elementry Math                          

103                           SBX                                      103                    1004                   Elementry Chemistry                     

103                           SBX                                      103                    1005                   Elementry Pyscology                      

101                           XYZ                                      101                    1001                   Elementry Physics                       

101                           XYZ                                      101                    1002                   Elementry Science                       

101                           XYZ                                      101                    1003                   Elementry Math                          

101                           XYZ                                      101                    1004                   Elementry Chemistry                     

101                           XYZ                                      101                    1005                   Elementry Pyscology                     

102                           ABC                                      102                    1001                   Elementry Physics                       

102                         ABC                                        102                    1002                   Elementry Science                       

102                          ABC                                      102                    1003                   Elementry Math                          

102                          ABC                                      102                    1004                   Elementry Chemistry                     

102                          ABC                                      102                    1005                   Elementry Pyscology                

To select any particular value:

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

STU_ID                 STU_NAME                                     COURSE_ID              COURSE_NAME  

---------------------- ---------------------------------------- ---------------------- ---------------------- -------------

101                              XYZ                                                            1001                   Elementry Physics                       

101                              XYZ                                        1002                   Elementry Science                       

101                             XYZ                                                       1003                   Elementry Math                          

101                             XYZ                                                         1004                   Elementry Chemistry                     

101                             XYZ                                                         1005                   Elementry Pyscology                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      

To select any particular value

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

AND X.STU_ID=101         

update

To update a table having a column defined as NESTED type, we need to define a MAP method of Object.

  1   Update scott.stu_course_info sci

  2   set sci.course_info=Scott.Course_Nst_tb(Scott.Course_obj(101,1003,'elementry math'))

  3*  where  course_info=scott.course_nst_tb(scott.course_obj(101,1003,'Elementry Math'))

SQL> /

1 row updated.

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

AND X.STU_ID=101

and x.course_name like '%ath%'

STU_ID                 STU_NAME                                     COURSE_ID              COURSE_NAME  

---------------------- ---------------------------------------- ---------------------- ---------------------- -------------

101                             XYZ                                                       1003                   elementry math                          

Delete

delete from scott.stu_course_info sci

 where course_info=scott.course_nst_tb(scott.course_obj(101,1003,'Elementry Math'))

commit

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

AND X.STU_ID=101

order by 1,3

STU_ID                 STU_NAME                                     COURSE_ID              COURSE_NAME  

---------------------- ---------------------------------------- ---------------------- ---------------------- -------------

101                         RAJ                                                         1002                       Elementry Science

101                         RAJ                                                         1004                       Elementry Chemistry

101                         RAJ                                                         1005                       Elementry Pyscology

As we can see, that it is really not that complicated or difficult to use NESTED type or Varry Type using in SQL Statement.  We can use is pretty easily once we start using it. In part 2 we will discuss the performance impact of using NESTED types as columns..

Any suggestion, correction, comments will be appreciated.