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

From: JMoo <michaeljmoore_at_gmail.com>
Date: Thu, 7 Feb 2008 13:44:50 -0800 (PST)
Message-ID: <d614b2cf-afa6-4f65-8b91-932b7441469b@f10g2000hsf.googlegroups.com>


On Feb 5, 3:24 pm, Vamsidhar <tvamsid..._at_gmail.com> wrote:
> 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

This will only work if all keys are the same length. replace your first insert
"insert into vt_del_lib values ('010001', 'One');" with this one "insert into vt_del_lib values ('100', 'One');"

As you have said that you've simplified the scenario, this probably explains why the result is useless. I know that this is not what you are asking, but you would make your life so much easier if you normalized the vt_del_keys table.
Mike Received on Thu Feb 07 2008 - 15:44:50 CST

Original text of this message