12 Replies Latest reply on Jan 19, 2015 6:32 AM by Raj Nath

    cartesian product to generate multiple rows taking time

    Raj Nath

      Hi All,

       

      Below are the script to create sample table and data

       

      create table ab(id number, val varchar2(200));

      create table bc(idn number);

       

      insert into ab

      select rownum,'test'

      from dual

      connect by level<10000;

       

      insert into ab

      select rownum,'test'

      from dual

      connect by level<10000;

       

      insert into bc

      select rownum

      from dual

      connect by level<4;

      commit;


      select * from ab;

      select * from bc;

       

      select id,val,id*idn from ab,bc;

       

      Using this select i am trying to generate three rows for each row fetched from table ab using table bc, since this is going for cartesian product,

      overall query cost is going very high. This is sample of the problem which i am facing and in real case table ab represent complex query based on multiple filter returning few row. Is there any way to generate three rows for each rows of table ab or

      to reduce the query cost.