What is the use of band join in oracle

52 views Asked by At

I am having below table.

create table tbl1 ( col1 varchar2(20));
create table tbl2 ( col1 varchar2(20));

Insert into tbl1 (COL1) values ('1');
Insert into tbl1 (COL1) values ('1');
Insert into tbl1 (COL1) values ('1');
Insert into tbl1 (COL1) values ('2');
Insert into tbl1 (COL1) values ('3');
Insert into tbl1 (COL1) values (null);

Insert into tbl2 (COL1) values ('1');
Insert into tbl2 (COL1) values ('1');
Insert into tbl2 (COL1) values ('1');
Insert into tbl2 (COL1) values ('2');
Insert into tbl2 (COL1) values ('2');
Insert into tbl2 (COL1) values ('5');
Insert into tbl2 (COL1) values ('5');

i am running below query

select a.col1 /*+ use_band(a b) */
from tbl1 a , tbl b
where b.col1 between 4 and 6;

and i am getting 12 rows. But even without using /*+ use_band(a b) */ i am getting same 12 rows. and also we can get this same result by cross join .

so i am having these questions:

A) Why using band join?

B) Is there any performance improvement compared to using inner/outer/left/right joins ?

C) Is there any thing going on in underlying mechanism compared to rest of the joins [inner/outer/left/right/cross]

Link for oracle docs: band_join

0

There are 0 answers