Help: Join a "glossary" table and a "data" table referring to terms in the glossary

From: Vamsidhar <tvamsidhar_at_gmail.com>
Date: Tue, 5 Feb 2008 15:24:31 -0800 (PST)
Message-ID: <3c440690-0156-4c65-b7c2-21d5412552f4@s12g2000prg.googlegroups.com>

I have a "glossary" table that holds a unique identifier (key) for each "text" string used elsewhere in the app. The fields in the data tables that should hold the text are then populated with the "key" instead of the text - helps clients substitute their own "glossary" for the default one, I suppose. In some cases, there may be more than one key in a given field (separated by commas). And the objective here is to extract a list of all the keys used in a given data table.

I've tried to describe the scenario with a simplified example below:

Given the following data:

• ---------------------
• This is my "glossary" create table vt_del_lib (key varchar2(100), text varchar2(100));

insert into vt_del_lib values ('010001', 'One');
insert into vt_del_lib values ('010002', 'Two');
insert into vt_del_lib values ('010003', 'Three');
insert into vt_del_lib values ('010004', 'Four');
insert into vt_del_lib values ('010005', 'Five');
insert into vt_del_lib values ('010006', 'Six');
insert into vt_del_lib values ('010007', 'Seven');
insert into vt_del_lib values ('010008', 'Eight');
insert into vt_del_lib values ('010009', 'Nine');
insert into vt_del_lib values ('010010', 'Ten');

commit;

• This is my "data" table create table vt_del_keys (val1 varchar2(1000));

insert into vt_del_keys values('010001');
insert into vt_del_keys values('010004,010005,010008,010010');
insert into vt_del_keys values('010005,010008');

commit;
-- ---------------------

Here's the query I've finally decided to use. I know it works, but not how :) I was wondering if someone could explain - how/why does it work - which version is better - is there a better way - etc.

It gets pretty slow as the data table grows and I'm having trouble trying to improve its performance.

• --------------------- select distinct l.key from vt_del_lib l join vt_del_keys d on ','||d.val1||',' like '%,'||l.key||',%';
• OR

select distinct l.key
from

	vt_del_keys d
	join vt_del_lib l on ','||d.val1||',' like '%,'||l.key||',%';



-- ---------------------

Sorry for the cryptic subject line - I couldn't classify the problem any better :)

TIA!
Vamsi.

P.S.: Some sample output...

SQL> --Get used keys
SQL> select l.key
  2 from

  3  	vt_del_lib l
  4  	join vt_del_keys d on ','||d.val1||',' like '%,'||l.key||',%';

KEY

---

010001
010004
010005
010008
010010
010005
010008

7 rows selected

Executed in 0.375 seconds
SQL> select l.key
  2 from

  3  	vt_del_keys d
  4  	join vt_del_lib l on ','||d.val1||',' like '%,'||l.key||',%';

KEY

---

010001
010004
010005
010005
010008
010008
010010

7 rows selected

Executed in 0.375 seconds
SQL> --Get distinct used kyes
SQL> select distinct l.key
  2 from

  3  	vt_del_lib l
  4  	join vt_del_keys d on ','||d.val1||',' like '%,'||l.key||',%';

KEY

---

010001
010004
010005
010008
010010 Received on Tue Feb 05 2008 - 17:24:31 CST