Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Aliaksandr.

Asked: April 11, 2017 - 7:59 am UTC

Last updated: April 18, 2017 - 2:15 pm UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 1000+ times

You Asked

Hi Tom,

there is a table with all libraries and books.
there is other table with book shells. the book can be included or explicit excluded to/from shell.

I have to populate lib_shell table with connections between libraries and shells

create table lib_book (
  lib_name VARCHAR2(7 CHAR) not null,
  book_name VARCHAR2(7 CHAR) not null  
);

create table shell_book (
  shell_id number not null,
  book_name VARCHAR2(7 CHAR) not null,
  is_excluded number not null -- 0 - included, 1 - excluded
);

insert into lib_book values ('lib1', 'book1');
insert into lib_book values ('lib1', 'book2');
insert into lib_book values ('lib2', 'book1');
insert into lib_book values ('lib2', 'book2');
insert into lib_book values ('lib2', 'book3');
--- ca 300 000 000 rows (6 000 000 libraries with 1-100 books on each)

insert into shell_book values (1, 'book2', 0);
insert into shell_book values (2, 'book1', 0);
insert into shell_book values (2, 'book2', 0);
insert into shell_book values (2, 'book4', 1);
insert into shell_book values (3, 'book1', 0);
insert into shell_book values (3, 'book3', 1);
-- ca 60 000 rows (1000 shells with 1-60 books on each)

create table lib_shell (
  lib_name VARCHAR2(7 CHAR) not null,
  shell_id number not null
)

-- pupulate lib_shell here

-- results in this case:
--'lib1',1
--'lib1',2
--'lib1',3
--'lib2',1
--'lib2',2


thanks a lot

and Chris said...

Sounds to me like you just need to join, filtering out the rows where is_excluded = 1.

You can then insert the query into the table:

create table lib_book (
  lib_name VARCHAR2(7 CHAR) not null,
  book_name VARCHAR2(7 CHAR) not null  
);

create table shell_book (
  shell_id number not null,
  book_name VARCHAR2(7 CHAR) not null,
  is_excluded number not null -- 0 - included, 1 - excluded
);

insert into lib_book values ('lib1', 'book1');
insert into lib_book values ('lib1', 'book2');
insert into lib_book values ('lib2', 'book1');
insert into lib_book values ('lib2', 'book2');
insert into lib_book values ('lib2', 'book3');

insert into shell_book values (1, 'book2', 0);
insert into shell_book values (2, 'book1', 0);
insert into shell_book values (2, 'book2', 0);
insert into shell_book values (2, 'book4', 1);
insert into shell_book values (3, 'book1', 0);
insert into shell_book values (3, 'book3', 1);

create table lib_shell (
  lib_name VARCHAR2(7 CHAR) not null,
  shell_id number not null
);

insert into lib_shell
select lib_name, shell_id 
from   lib_book l
join   shell_book s
on     s.book_name = l.book_name
where  is_excluded = 0;

select * from lib_shell;

LIB_NAME  SHELL_ID  
lib1      3         
lib1      2         
lib1      2         
lib1      1         
lib2      3         
lib2      2         
lib2      2         
lib2      1


If you want to exclude duplicates, use distinct after the select:

select distinct lib_name, shell_id

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

not exactly, what I have asked

Aliaksandr Sebiashuk, April 11, 2017 - 3:19 pm UTC

actually not exactly, what I have asked.

the relation between libs and shells should be created only if all books with excluded=0 are in lib and there are no books with excluded=1 in the same lib.

because of amount of rows, it will be better to create materialised view for this.

something like this:
select lib_name, shell_id from lib_book lb inner join
 shell_book sb on (lb.book_name=sb.book_name)
group by lib_name, shell_id 
having sum(excluded) = 0 and count(lb.book_name)= (select count(*) from shell_book sb2 where sb2.shell_id=sb.shell_id and sb2.excluded=0);

-- or the same, but with minus

 select lib_name, shell_id from (
  select lib_name, shell_id from lib_book lb inner join
 shell_book sb on (lb.book_name=sb.book_name)
where sb.excluded=0
group by lib_name, shell_id having count(lb.book_name)= (select count(*) from shell_book sb2 where sb2.shell_id=sb.shell_id and sb2.excluded=0)
minus
select lib_name, shell_id from lib_book lb inner join
 shell_book sb on (lb.book_name=sb.book_name)
where sb.excluded=1);


also I have created MV with REFRESH COMPLETE and I would like to have one with "REFRESH FAST ON COMMIT"

CREATE MATERIALIZED VIEW MV_LIB_SHELL
PARALLEL BUILD IMMEDIATE
REFRESH FORCE start with (sysdate) next  (sysdate+30/1440)
AS
select lib_name, shell_id from (
  select lib_name, shell_id from lib_book lb inner join
 shell_book sb on (lb.book_name=sb.book_name)
where sb.excluded=0
group by lib_name, shell_id having count(lb.book_name)= (select count(*) from shell_book sb2 where sb2.shell_id=sb.shell_id and sb2.excluded=0)
minus
select lib_name, shell_id from lib_book lb inner join
 shell_book sb on (lb.book_name=sb.book_name)
where sb.excluded=1);

Chris Saxon
April 11, 2017 - 4:04 pm UTC

I'm not sure what you mean by "all books with excluded=0 are in lib and there are no books with excluded=1 in the same lib.".

Could you clarify with further examples?

Why do you want an MV? If you're loading these into a table this is a one-off operation, no?

more explanation

Aliaksandr Sebiashuk, April 11, 2017 - 5:55 pm UTC

I will try with one more example:

the books -> products
the library -> supermarket with products
the shell -> shop list with products

it is necessary to find in which supermarkets we can find all products from each shop list.
excluded -> some products we do not want to see (for example tobacco.)
so only if in supermarket A we can find all included products from shop list B (with is_excluded=0), and in supermarket A there is no products from list B marked for exclude (is_excluded=1), the A und B should be connected.

the lib_book and shell_book tables will be populated from external applications and we would like to have materialised view in oracle, not to actualise lib_shell table by our self.
also we have a problem with performance, because current select takes about 18 minutes (we have 300 000 000 books in libraries)

thanks a lot
Chris Saxon
April 18, 2017 - 2:15 pm UTC

What is the issue you're actually trying to solve? Find a query to get the correct result? Or make the queries you have run faster?

If it's the latter, what are those queries and what are their execution plans?

If you're not sure how to create an execution plan, read:

https://blogs.oracle.com/sql/how-to-create-an-execution-plan

and post the results here. Make sure this includes estimated and actual rows!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.