-1

I have a User Table, which has a role column. Each user can have multiple roles (or none) and for ease of use, I used a comma separated list to store the roles.

USERNAME   ROLE
----------------------
bob        role1,role2
sally      role2

Now, needing to check on specific roles, I've created a view to get the user-role mappings (Similar to a basic XREF table) by using the following subselect:

SELECT DISTINCT username,
  trim(regexp_substr(str, '[^,]+', 1, level)) authority
FROM
  (SELECT username, role str FROM user WHERE role IS NOT NULL
  ) roles_table
CONNECT BY instr(str, ',', 1, level - 1) > 0

to get

USERNAME   AUTHORITY
----------------------
bob        role1
bob        role2
sally      role2

The problem is that this subquery takes an inordinate amount of time to run. Is there a better way of formatting it to optimize the process?

Or would I need to update my entire structure and revert to using a XREF table? If the latter, could I create a XREF view and speed it up somehow by using indices?

Thanks!

vinays84
  • 394
  • 4
  • 14

1 Answers1

1

You can speed it up by using instr and substr (rather than regexp versions), or speed it up A LOT if the input is CLOB and you use the dbms_lob versions of instr and substr.

You can create a materialized view with on commit refresh fast (at the cost of making transactions on the base table a bit slower).

The best choice is to replace the base table with one that is in first normal form - essentially with the RESULT of your current view. But perhaps that is not an option.

EDIT: I just looked at the query again and see what you are doing.... not good! The way you wrote the hierarchical query, it generates an inordinate number of rows, which you then reduce with DISTINCT. Try this first, perhaps it's all you need:

select username, trim(regexp_substr(str, '[^,]+', 1, level)) authority
from   user
connect by instr(str, ',', 1, level - 1) > 0
       and prior username = username
       and prior sys_guid() is not null;