4 Replies Latest reply: Dec 29, 2013 1:32 AM by ramoradba RSS

    Self join Interview question query ?

    usman_noshahi

      hi guys!

      i was asked this question in an interview for the post of Database developer.

      I was given a SELECT statement based on sigle table and asked to drow the output at paper and then explain how that statement works step by step.

       

      here is the create & insert statements

      /**********************************************/

      create table tbl (x number , y varchar2(50));

       

      INSERT INTO tbl (x,y) VALUES (0 , 'A');

      INSERT INTO tbl (x,y) VALUES (1 , 'B');

      COMMIT;

      /***********************************************/

      here below is the query (select statement)

       

      SELECT tbl1.x

      FROM tbl tbl1 , tbl tbl2

      WHERE tbl1.x = 0 OR tbl2.x = 1;

       

      please explain me the output and step by step working of this statement..

       

      thanks....

        • 1. Re: Self join Interview question query ?
          ramoradba

          Before expecting any reply from any oracle forum , you have  to let us know what you have tried so far!  or at least what your reply to the interviewer ...

           

          --SS

          • 2. Re: Self join Interview question query ?
            sb92075

            >i was asked this question in an interview for the post of Database developer.

            It appears that you are/were not qualified for this position.

            • 3. Re: Self join Interview question query ?
              ORA-00007

              The question which they asked will create

              CROSS JOIN ON THE SAME TABLE RESULTING THE O/P WILL BE 4 ROWS AND 4 COLUMNS for this query

               

              SELECT *

              FROM tbl tbl1 , tbl tbl2.

               

              o/p

              0x0x
              0x1y
              1y0x
              1y1y

               

              and when adding the filtering condition it will result to by selecting 3 rows by eliminating one row according to the condition .

               

              0x0x
              0x1y
              1y1y

               

              so your query result will be

              0
              0
              1
              • 4. Re: Self join Interview question query ?
                ramoradba

                Hello

                 

                If you omit the where clause query will result as below

                SELECT tbl1.x,tbl2.x

                FROM tbl tbl1 , tbl tbl2

                /

                 

                    X          X

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

                    0          0

                    0          1

                    1          0

                    1          1

                 

                Query given will result as below  based on the where condition on each and every row from above 4 rows...  Or Condition will returs true if either of the condition is true with the data set.

                 

                SQL> SELECT tbl1.x,tbl2.x

                  2  FROM tbl tbl1 , tbl tbl2

                  3  WHERE tbl1.x = 0 OR tbl2.x = 1

                  4  /

                        X          X

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

                        0          0

                        0          1

                        1          1

                SQL>

                 

                You can get the information in another way as below  ...

                 

                SQL> explain plan set statement_id='ORACLEFORUMS' for

                  2  SELECT tbl1.x

                  3  FROM tbl tbl1 , tbl tbl2

                  4  WHERE tbl1.x = 0 OR tbl2.x = 1;

                 

                 

                Explained.

                 

                 

                SQL>  select LPad(' ', 2*(Level-1)) || Level || '.' || Nvl(Position,0)|| ' ' ||

                  2   Operation || ' ' || Options || ' ' || Object_Name || ' ' || Object_Type

                  3   || ' ' || Decode(id, 0, Statement_Id ||' Cost = ' || Position) || cost

                  4   || ' ' || Object_Node "Query Plan"

                  5   from plan_table

                  6   start with id = 0 And statement_id='ORACLEFORUMS'

                  7   connect by prior id = parent_id

                  8   and statement_id='ORAFAQ'

                  9   /

                 

                 

                Query Plan

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

                1.7 SELECT STATEMENT    ORACLEFORUMS Cost = 77

                  2.1 NESTED LOOPS    7

                    3.1 TABLE ACCESS FULL TBL TABLE 3

                    3.2 TABLE ACCESS FULL TBL TABLE 2

                 

                 

                SQL>  SELECT *

                  2   FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','ORACLEFORUMS','BASIC'));

                 

                 

                PLAN_TABLE_OUTPUT

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

                 

                 

                Plan hash value: 1718268327

                 

                 

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

                | Id  | Operation          | Name |

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

                |   0 | SELECT STATEMENT   |      |

                |   1 |  NESTED LOOPS      |      |

                |   2 |   TABLE ACCESS FULL| TBL  |

                |   3 |   TABLE ACCESS FULL| TBL  |

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

                 

                 

                10 rows selected.

                 

                 

                SQL>

                 

                 

                 

                 

                SQL>   select LPAD(' ',2*(LEVEL-1))||operation "OPERATION", options "OPTIONS",

                  2   DECODE(TO_CHAR(id),'0','COST = ' || NVL(TO_CHAR(position),'n/a'),

                  3   object_name) "OBJECTNAME", id ||'-'|| NVL(parent_id, 0)||'-'||

                  4   NVL(position, 0) "ORDER", SUBSTR(optimizer,1,6) "OPT"

                  5   from plan_table

                  6   start with id = 0

                  7   and statement_id='ORACLEFORUMS'

                  8   connect by prior id = parent_id

                  9   and statement_id='ORACLEFORUMS'

                10  /

                 

                 

                OPERATION                 OPTIO OBJECTNAME                                      ORDER      OPT

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

                SELECT STATEMENT                COST = 7                                        0-0-7      ALL_RO

                  NESTED LOOPS                                                                  1-0-1

                    TABLE ACCESS          FULL  TBL                                             2-1-1

                    TABLE ACCESS          FULL  TBL                                             3-1-2

                 

                 

                SQL>

                 

                 

                ID-PARENT ID as follows ..

                00

                10

                21

                31

                 

                 

                here Level 1 has 2 childs(2,3)

                So the execution steps sequential order will be 2,3,1...

                 

                 

                For More reference I recommend you to read below of course it is Oracle V8  but it gives more information.

                 

                Happy reading ! 

                 

                Optimization of Joins