2011-05-06

many to many access path -view

Having a many to many relationship. It is the access path from one table to another. Lets call them cust, ord and prod tables.


cust 1-* ord *-1 prod


There is a need to query prod that has an access path to a certain cust. And we need a database view to implement this. So we want prod out and give a cust to the query as a parameter. The first impression to a developer is to do joins to ord table. That would lead to duplicates in the result. Something that is not desired. It is possible to implement a fine performing view without those duplicates. Here is an example of such.



create table cust (cust_id number constraint cust_pk primary key, cname varchar2(20) not null);

create table prod (prod_id number constraint prod_pk primary key, pname varchar2(20) not null);

create table ord ( ord_id number constraint ord_pk primary key
, cust_id constraint ord_cust_fk references cust not null
, prod_id constraint ord_prod_pk references prod not null
, amount number not null);

create index ord_cust_fk_idx on ord(cust_id,prod_id);

create or replace view custprod as
select cust.cust_id
, cust.cname
, prod.prod_id
, prod.pname
from cust,prod
where exists (select 1 from ord where ord.prod_id=prod.prod_id and ord.cust_id=cust.cust_id)
;

select prod_id,pname from custprod where cust_id = :cust;



So there it is. Take only those rows to the from clause you want results from. Use exists on another parts of the access path.
Some data and a query plan.



insert all into cust(cust_id,cname) values (le,le||le)
into prod(prod_id,pname) values (le,le||le)
select level le from dual connect by level < 1000;

begin
dbms_stats.gather_table_stats(user,'PROD');
dbms_stats.gather_table_stats(user,'CUST');
end;
/

insert into ord select rownum,cust_id,prod_id,mod(cust_id,prod_id) from cust, prod where cust_id <= prod_id;

begin
dbms_stats.gather_table_stats(user,'ORD');
end;
/


select prod_id,pname from custprod where cust_id = :cust;

--------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | |
| 4 | INDEX UNIQUE SCAN | CUST_PK |
| 5 | SORT UNIQUE | |
| 6 | INDEX RANGE SCAN | ORD_CUST_FK_IDX |
| 7 | INDEX UNIQUE SCAN | PROD_PK |
| 8 | TABLE ACCESS BY INDEX ROWID| PROD |
--------------------------------------------------------

drop table ord;

drop table prod;

drop table cust;

drop view custprod;

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.