0

I have a table that has the following definition:

Table name: MY_TAB

ID          NUMBER,
ACCESS_LVL  CLOB

Some example data that may exist within this table is as follows:

ID: 1
ACCESS_LVL: RoleName-A,RoleName-B,RoleName-C,RoleName-D,RoleName-E

ID: 2
ACCESS_LVL: RoleName-D,RoleName-E

ID: 3
ACCESS_LVL: RoleName-A,RoleName-B,RoleName-C,RoleName-D,RoleName-E,RoleName-F,RoleName-G,RoleName-H

ID: 4
ACCESS_LVL: RoleName-E

The query I am unsure how to do is that I need to return all the IDs that may have the following ACCESS_LVL values (where ACCESS_LVL is a clob), i.e.

RoleName-B,RoleName-C,RoleName-D

Basically something like:

select id
from my_tab
where ('RoleName-B','RoleName-C','RoleName-D') in (ACCESS_LVL)

So the result in this example would be just:

ID
1
2
3
halfer
  • 19,824
  • 17
  • 99
  • 186
tonyf
  • 34,479
  • 49
  • 157
  • 246
  • Is this http://stackoverflow.com/questions/17649011/search-for-a-particular-string-in-oracle-clob-column helpful? – Peter Sep 06 '16 at 00:32
  • @Peter don't think it's what I'm after as I need to perform an IN clause for a specific set of data. Would like to do this as one query. – tonyf Sep 06 '16 at 00:41
  • No, you don't need to perform an IN clause. There is no such requirement in the business problem; there is only such a requirement in the narrow way YOU think the problem should be SOLVED. You need to learn to make this distinction very clearly in your head. Google "XY Problem" and see what that means - it will help you in the future. –  Sep 06 '16 at 01:14

1 Answers1

2

Storing comma-separated values in a single column violates every rule of normalization. That's going to make your queries much harder to write and much slower to run. You really ought to have a 1-to-many child table that stores the roles. If you do that, your queries will be much more efficient.

You could do something like

select id
  from my_tab
 where ',' || access_lvl || ',' like '%,RoleName-B,%'
    or ',' || access_lvl || ',' like '%,RoleName-C,%'
    or ',' || access_lvl || ',' like '%,RoleName-D,%'

That is going to be terribly slow but it will work.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Understand what you are saying Justin but this is the table definition with data that I have. There is no way of turning the ACCESS_LVL clob into a variable IN list, so that I can use it as part of a normal IN clause? – tonyf Sep 06 '16 at 00:47
  • @tonyf - You could but it's going to be a lot more complicated. You'd take the `access_lvl` value, do a `connect by level` from `dual` for the number of elements, and then some `substr` and `instr` calls to parse out the single row into one row per value and then use that as your data source. That's quite a bit more complex than this code and I'd expect it's going to be no faster. – Justin Cave Sep 06 '16 at 00:54
  • And since it's a CLOB, it (where 'it' = separating each CLOB into its component tokens) is likely to take a lot of time. Actually any solution will take a lot of time, potentially - the price paid for violating first normal form. –  Sep 06 '16 at 01:16
  • I guess I will also look at the dbms_lob.instr solution and see if that will help. – tonyf Sep 06 '16 at 01:23
  • @tonyf - dbms_lob.instr may indeed work faster. Also - if you are forced to deal with this type of data, but you are allowed to create a materialized view, you MAY benefit from breaking the strings down into tokens in a MV... especially if the data changes (very) slowly. –  Sep 06 '16 at 01:29
  • @mathguy are you able to point me in the right direction on how to break the strings down into tokens using a MV and then use this for my query as the ACCESS_LVL data will change? – tonyf Sep 06 '16 at 01:40
  • @tonyf - If you can add structures but not change the underlying data model, I'd be looking at an Oracle Text index rather than a MV. There would be a lot to read up on to figure out the best type of Text index to use-- the Oracle Text manual for your version of Oracle would be a good place to start. You'll have more options if you can accept stale results. – Justin Cave Sep 06 '16 at 01:59