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

On Feb 5, 7:15 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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 --- Hide quoted text -
>
> - Show quoted text -

Thanks Mark D Powell :)

The goal is to extract the keys values.

Yes, the explain plan shows that both tables undergo full table scan. I didn't think of creating an index on the data table because I thought the LIKE clause doesn't allow for an index to be used. Can you suggest what the function-based index should be like on the data table? - or the glossary table, for that matter :)

Thanks.
Vamsi. Received on Tue Feb 05 2008 - 19:13:32 CST