How to Optimize Substring Regular Expression in Oracle BD

Asked

Viewed 43 times

0

I have the following expression:

SELECT us.FULL_NAME, us.USER_ID FROM knta_users us WHERE USER_ID IN(SELECT DISTINCT REGEXP_SUBSTR(PRJ.PRJ_PROJECT_MANAGER_USER_ID, '[^#@#]+', 1, LEVEL) AS GP_ID 
FROM KCRT_FG_PFM_PROJECT PRJ WHERE PRJ.PRJ_PROJECT_ID = '30580' CONNECT BY REGEXP_SUBSTR (PRJ.PRJ_PROJECT_MANAGER_USER_ID,'[^#@#]+', 1, LEVEL) IS NOT NULL)

It works, but it takes a long time to load.

Is there an alternative way to do this query? Because the system that consumes it does not load the data for exceeded time.

This snippet will find in the database a field that stores several users separated by special characters (e.g.: usuario1#@#usuario2) and bring each of them in a row.

sql oracle

  • First suggestion , normalize the table , complicated sqls because the tables are not normalized, it is not possible to try a subselect with EXISTS and LIKE.

  • Sorry for the question, but what do you mean by normalize the table?

  • Instead of having a "string" with the concatenated users having a second table and each user in a column, look for NORMAL SHAPES. A sql would be trivial.

  • Is that actually the table structure is already mounted that way. The only way would be to change the way the information is recorded? Just so you understand this field stores who is the manager of each project, but can be several people, so it will concatenate, but to display I need to separate these users.

  • Try by subselect and exists

1 answer

0

[I don’t have a score yet to comment] - Have you tried running an execution plan to check if any fullscan is occurring? If yes, try to find an index to use and perform parallelism to make the query more performative.

  • You can update the bank statistics to improve performance, I got good results by performing this practice, aligning to the study of the execution plan as the colleague mentioned above, you can improve your select by using columns indices that already have an optimized query in the table. EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'OWNER', TABNAME=>'NOME_TABELA', Cascade=>true);

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.