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

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Tue, 5 Feb 2008 16:15:56 -0800 (PST)
Message-ID: <649d396f-eea5-49b3-922a-3314a6e04e1b@i12g2000prf.googlegroups.com>


On Feb 5, 6: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

I do not have a system in front of me that I can test with but if you run explain plan on these queries I expect that both tables are being read via full table scans since you concatenate a comma before and after you key columns rendering any index useless unless you have built function based indexes on both set of keys.

Because the run time is exactly equal I suspect Oracle is using the same plan for both orderings of the table name in the query.

Is the goal to find the key value or to get the descriptions for the keys?

  • Mark D Powell --
Received on Tue Feb 05 2008 - 18:15:56 CST

Original text of this message