Início / dba / Perguntas / 39002
Accepted
a1ex07
Asked: 2013-04-02 09:55:42 +0800 CST

Usando consultas hierárquicas do Oracle para transpor o valor do campo em linhas

  • 772

Uma coluna da minha tabela armazena uma lista de valores separados por vírgula (sei que é um design ruim, mas a refatoração não é uma opção no momento). Parece que encontrei uma solução funcional, mas receio que não seja muito eficiente:

select distinct test_id , regexp_substr(str_data,'[^,]+', 1, level)  
from 
(
select 1 as test_id, '1,2,3' as str_data from dual 
union all 
select 2 as test_id, '4,5,6' as str_data from dual
)test_data 
connect 
by 
regexp_substr(str_data, '[^,]+', 1, level) is not null

Eu não gosto que eu tenha que usar distinct. Existe uma abordagem melhor ?

Obrigada.

oracle hierarchy

1 respostas

  1. Best Answer

    Seu CONNECT BYune todas as linhas a todas as outras (incluindo ela mesma) N vezes. Esta é uma junção cruzada automática múltipla. Posso ver como ele pode se comportar mal em termos de desempenho quando o número de registros aumenta.

    Sugiro uma consulta como esta:

    SQL> SELECT test_id, regexp_substr(str_data, '[^,]+', 1, lvl)
  2    FROM (SELECT 1 AS test_id, '1,2,3' AS str_data
  3            FROM dual
  4          UNION ALL
  5          SELECT 2 AS test_id, '4,5,6' AS str_data FROM dual) test_data
  6    JOIN (SELECT LEVEL lvl FROM dual CONNECT BY LEVEL <= 1000)
  7      ON lvl <= length(str_data) - length(REPLACE(str_data, ',')) + 1;

TEST_ID REGEXP_SUBSTR(STR_DATA,'[^,]+',1,LVL)
------- -------------------------------------
      1                                     1
      2                                     4
      1                                     2
      2                                     5
      1                                     3
      2                                     6

    Substitua 1000 pelo número máximo de elementos (ou calcule-o com uma COUNTsubconsulta interna). Cada linha será consultada apenas n vezes (onde n é o número de vírgulas).

    Além disso, se o desempenho for crítico, use INSTRe SUBSTRem vez de REGEX.

    • 2

relate perguntas