How is the fastest way to join 3 level table :

I want to have a query that will display list of company with ADD_MAIN=1
and CLB_MAIN=1 (detail table at the end of this email)

COM_ID, COM_NAME, ADD_ADDRESS, CIT_NAME, PRO_NAME, COU_NAME, LOB_NAME,
LB1_NAME, LB2_NAME

I was do this but it very slow (two natural plan on VIMAINLOB and
VIMAINADDR) , or may some one has other suggestion or comment, I don't
know other way to do this .. Or may be it's not good practice at all or
bad design ...

1. Create View for address (VIADDRESS)

CREATE VIEW VIADDRESS( ADD_ID, ADD_ADDRESS, CIT_ID, COU_ID, PRO_ID,
COU_NAME, CIT_NAME, PRO_NAME)
AS
SELECT TBADDRESS.ADD_ID, TBADDRESS.ADD_ADDRESS, TBADDRESS.CIT_ID,
TBADDRESS.COU_ID, TBADDRESS.PRO_ID, TBCOUNTRY.COU_NAME, TBCITY.CIT_NAME,
TBPROVINCE.PRO_NAME
FROM TBADDRESS
LEFT OUTER JOIN TBPROVINCE ON (TBADDRESS.PRO_ID = TBPROVINCE.PRO_ID)
LEFT OUTER JOIN TBCITY ON (TBADDRESS.CIT_ID = TBCITY.CIT_ID)
LEFT OUTER JOIN TBCOUNTRY ON (TBADDRESS.COU_ID = TBCOUNTRY.COU_ID);

2. Create View for company address where main address=1 (VIMAINADDR)

CREATE VIEW VIMAINADDR( CAD_ID, COM_ID, ADD_ID, CAD_MAIN, ADD_ADDRESS,
CIT_ID, COU_ID, PRO_ID, COU_NAME, CIT_NAME, PRO_NAME)
AS
SELECT TBCOADDR.CAD_ID, TBCOADDR.COM_ID, TBCOADDR.ADD_ID,
TBCOADDR.CAD_MAIN, VIADDRESS.ADD_ADDRESS, VIADDRESS.CIT_ID,
VIADDRESS.COU_ID, VIADDRESS.PRO_ID, VIADDRESS.COU_NAME,
VIADDRESS.CIT_NAME, VIADDRESS.PRO_NAME
FROM VIADDRESS
RIGHT OUTER JOIN TBCOADDR ON (VIADDRESS.ADD_ID = TBCOADDR.ADD_ID)
where
TBCOADDR.CAD_MAIN=1;

3. Create View for Company LOB where main LOB =1 (VIMAINLOB)

CREATE VIEW VIMAINLOB( CLB_ID, LOB_ID, LB1_ID, LB2_ID, CLB_MAIN, COM_ID,
LOB_NAME, LB1_NAME, LB2_NAME)
AS
SELECT TBCOLOB.CLB_ID, TBCOLOB.LOB_ID, TBCOLOB.LB1_ID, TBCOLOB.LB2_ID,
TBCOLOB.CLB_MAIN, TBCOLOB.COM_ID, TBLOB.LOB_NAME, TBSUBLOB1.LB1_NAME,
TBSUBLOB2.LB2_NAME
FROM TBSUBLOB2
RIGHT OUTER JOIN TBCOLOB ON (TBSUBLOB2.LB2_ID = TBCOLOB.LB2_ID)
LEFT OUTER JOIN TBLOB ON (TBCOLOB.LOB_ID = TBLOB.LOB_ID)
LEFT OUTER JOIN TBSUBLOB1 ON (TBCOLOB.LB1_ID = TBSUBLOB1.LB1_ID)
where
TBCOLOB.CLB_MAIN=1;

4. Create View for Company (VICOMPANY)

CREATE VIEW VICOMPANY( COM_ID, COM_NAME, ADD_ID, CAD_MAIN, ADD_ADDRESS,
CIT_ID, COU_ID, PRO_ID, COU_NAME, CIT_NAME,
PRO_NAME, CAD_ID, CLB_ID, LOB_ID, LB1_ID, LB2_ID, CLB_MAIN,
LOB_NAME, LB1_NAME, LB2_NAME)
AS
SELECT TBCOMPANY.COM_ID, TBCOMPANY.COM_NAME, VIMAINADDR.ADD_ID,
VIMAINADDR.CAD_MAIN, VIMAINADDR.ADD_ADDRESS, VIMAINADDR.CIT_ID,
VIMAINADDR.COU_ID, VIMAINADDR.PRO_ID, VIMAINADDR.COU_NAME,
VIMAINADDR.CIT_NAME, VIMAINADDR.PRO_NAME, VIMAINADDR.CAD_ID,
VIMAINLOB.CLB_ID, VIMAINLOB.LOB_ID, VIMAINLOB.LB1_ID, VIMAINLOB.LB2_ID,
VIMAINLOB.CLB_MAIN, VIMAINLOB.LOB_NAME, VIMAINLOB.LB1_NAME,
VIMAINLOB.LB2_NAME
FROM VIMAINLOB
RIGHT OUTER JOIN TBCOMPANY ON (VIMAINLOB.COM_ID = TBCOMPANY.COM_ID)
LEFT OUTER JOIN VIMAINADDR ON (TBCOMPANY.COM_ID = VIMAINADDR.COM_ID);


Thanks,
Wasith


--------------------- Detail Table, PK and FK
--------------------------------------------


CREATE TABLE TBADDRESS ( ADD_ID INTEGER NOT NULL, ADD_ADDRESS
VARCHAR(60), CIT_ID INTEGER, COU_ID INTEGER, PRO_ID INTEGER);

CREATE TABLE TBCITY ( CIT_ID INTEGER NOT NULL, CIT_NAME VARCHAR(60),
PRO_ID INTEGER);

CREATE TABLE TBCOADDR ( CAD_ID INTEGER NOT NULL, COM_ID INTEGER, ADD_ID
INTEGER, CAD_MAIN SMALLINT);

CREATE TABLE TBCOLOB ( CLB_ID INTEGER NOT NULL, COM_ID INTEGER, LOB_ID
INTEGER, LB1_ID INTEGER, LB2_ID INTEGER, CLB_MAIN SMALLINT);

CREATE TABLE TBCOMPANY ( COM_ID INTEGER NOT NULL, COM_NAME
VARCHAR(60));

CREATE TABLE TBCOUNTRY ( COU_ID INTEGER NOT NULL, COU_NAME
VARCHAR(60));

CREATE TABLE TBLOB ( LOB_ID INTEGER NOT NULL, LOB_NAME VARCHAR(60));

CREATE TABLE TBPROVINCE ( PRO_ID INTEGER NOT NULL, COU_ID INTEGER,
PRO_NAME VARCHAR(60));

CREATE TABLE TBSUBLOB1 ( LB1_ID INTEGER NOT NULL, LOB_ID INTEGER,
LB1_NAME VARCHAR(60));

CREATE TABLE TBSUBLOB2 ( LB2_ID INTEGER NOT NULL, LB2_NAME VARCHAR(60),
LB1_ID INTEGER);

/***********************************************************************
*******/
/* Primary Keys
*/
/***********************************************************************
*******/

ALTER TABLE TBADDRESS ADD CONSTRAINT PK_TBADDRESS PRIMARY KEY (ADD_ID);
ALTER TABLE TBCITY ADD CONSTRAINT PK_TBCITY PRIMARY KEY (CIT_ID);
ALTER TABLE TBCOADDR ADD CONSTRAINT PK_TBCOADDR PRIMARY KEY (CAD_ID);
ALTER TABLE TBCOLOB ADD CONSTRAINT PK_TBCOLOB PRIMARY KEY (CLB_ID);
ALTER TABLE TBCOMPANY ADD CONSTRAINT PK_TBCOMPANY PRIMARY KEY (COM_ID);
ALTER TABLE TBCOUNTRY ADD CONSTRAINT PK_TBCOUNTRY PRIMARY KEY (COU_ID);
ALTER TABLE TBLOB ADD CONSTRAINT PK_TBLOB PRIMARY KEY (LOB_ID);
ALTER TABLE TBPROVINCE ADD CONSTRAINT PK_TBPROVINCE PRIMARY KEY
(PRO_ID);
ALTER TABLE TBSUBLOB1 ADD CONSTRAINT PK_TBSUBLOB1 PRIMARY KEY (LB1_ID);
ALTER TABLE TBSUBLOB2 ADD CONSTRAINT PK_TBSUBLOB2 PRIMARY KEY (LB2_ID);


/***********************************************************************
*******/
/* Foreign Keys
*/
/***********************************************************************
*******/

ALTER TABLE TBADDRESS ADD CONSTRAINT FK_TBADDRESS FOREIGN KEY (PRO_ID)
REFERENCES TBPROVINCE (PRO_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE TBADDRESS ADD CONSTRAINT FK_TBADDRESS_CIT_ID FOREIGN KEY
(CIT_ID) REFERENCES TBCITY (CIT_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE TBADDRESS ADD CONSTRAINT FK_TBADDRESS_COU_ID FOREIGN KEY
(COU_ID) REFERENCES TBCOUNTRY (COU_ID) ON DELETE CASCADE ON UPDATE
CASCADE;
ALTER TABLE TBCITY ADD CONSTRAINT FK_TBCITY_PRO_ID FOREIGN KEY (PRO_ID)
REFERENCES TBPROVINCE (PRO_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE TBCOADDR ADD CONSTRAINT FK_TBCOADDR_ADD_ID FOREIGN KEY
(ADD_ID) REFERENCES TBADDRESS (ADD_ID) ON DELETE CASCADE ON UPDATE
CASCADE;
ALTER TABLE TBCOADDR ADD CONSTRAINT FK_TBCOADDR_COM_ID FOREIGN KEY
(COM_ID) REFERENCES TBCOMPANY (COM_ID) ON DELETE CASCADE ON UPDATE
CASCADE;
ALTER TABLE TBCOLOB ADD CONSTRAINT FK_TBCOLOB_COM_ID FOREIGN KEY
(COM_ID) REFERENCES TBCOMPANY (COM_ID) ON DELETE CASCADE ON UPDATE
CASCADE;
ALTER TABLE TBCOLOB ADD CONSTRAINT FK_TBCOLOB_LB1_ID FOREIGN KEY
(LB1_ID) REFERENCES TBSUBLOB1 (LB1_ID) ON DELETE CASCADE ON UPDATE
CASCADE;
ALTER TABLE TBCOLOB ADD CONSTRAINT FK_TBCOLOB_LB2_ID FOREIGN KEY
(LB2_ID) REFERENCES TBSUBLOB2 (LB2_ID) ON DELETE CASCADE ON UPDATE
CASCADE;
ALTER TABLE TBCOLOB ADD CONSTRAINT FK_TBCOLOB_LOB_ID FOREIGN KEY
(LOB_ID) REFERENCES TBLOB (LOB_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE TBPROVINCE ADD CONSTRAINT FK_TBPROVINCE_COU_ID FOREIGN KEY
(COU_ID) REFERENCES TBCOUNTRY (COU_ID) ON DELETE CASCADE ON UPDATE
CASCADE;
ALTER TABLE TBSUBLOB1 ADD CONSTRAINT FK_TBSUBLOB1_LOB_ID FOREIGN KEY
(LOB_ID) REFERENCES TBLOB (LOB_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE TBSUBLOB2 ADD CONSTRAINT FK_TBSUBLOB2_LB1_ID FOREIGN KEY
(LB1_ID) REFERENCES TBSUBLOB1 (LB1_ID) ON DELETE CASCADE ON UPDATE
CASCADE;