Responder a este comentário
Utilizando selects hierárquicos. Relação pai-filho na mesma tabela.
Selects hierárquicos são utilizados para tabelas que possuem relacionamento de pai-filho com ela mesma.
Com elas é possível reescrever um SELECT de modo muito simples, fácil entendimento e ótima performance. São muito uteis em relacionamento de hierarquia de setores, empregados, produtos, contabilidade e etc.
Este recurso foi incluído na versão 11.50 xC5, com uma nova sintaxe o comando SELECT permite uma escrita fácil para este tipo de relacionamento, e para funcionar é necessário apenas que a tabela seja estruturada de maneira que cada registro tenha um campo para o relacionamento com o seu item de hierarquia superior.
A sintaxe utilizada é exatamente igual a utilizada pelo Oracle.
Recursos - Vantagens / Desvantagens
Por ser um recurso novo este tipo de select ainda está um pouco limitado, a que mais você irá sentir é a impossibilidade de realizar um join com outra tabela, por exemplo, se você precisar buscar uma descrição de uma select hierárquica em outra tabela não será possível, o que você terá que fazer é gerar o resultado em uma tabela temporária e então relaciona-la com as demais tabelas.
Outra limitação que irá fazer diferença para quem possui uma grande massa de dados é impossibilidade de utilizar recursos de paralelismo, o que não é suportado com este tipo de query.
O filtro da clausula WHERE é realizado após o relacionamento de hierarquia ter sido realizado. Isto pode parecer uma desvantagem onde você irá pensar que terá problemas para grande massas de dados, mas não é 100% verdade pois é permitido realizar filtros no relacionamento de hierarquia. Colocarei exemplos mais abaixo.
As vantagens são inúmeras, onde começamos com a escrita extremamente simplificada da query , também temos inclusão de funções/operadores que permite que você filtre ou exiba dados da hierarquia de modo muito fácil e gerando melhor visualização do seu dado.
Nestas funções/operadores é permitido:
- Exibir o nível de hierarquia que um registro está com relação ao registro pai-raiz (root)
- Identificar registros que são os últimos da hierárquica, ou seja, que não possuem filhos (registros leaf)
- Exibir um path completo de cada nível utilizando um separador. Igual a que vemos quando entramos em diretórios: /usr/local/bin.
- Exbir e tratar registros que geram um loop na hierarquia
- Ordenar por um campo da select, respeitando a hierarquia dos dados.
Sintaxe e comandos
A sintaxe básica é:
SELECT ... [, CONNECT_BY_ISLEAF] [, CONNECT_BY_ISCYCLE] [, CONNECT_BY_ROOT campo] [, SYS_CONNECT_BY_PATH()] [, LEVEL] FROM ... WHERE ... [ START WITH condição ] CONNECT BY [NOCYCLE] PRIOR condição [ ORDER SIBLINGS BY ...]
Onde os parâmetros descrito entre colchetes são opcionais:
- START WITH: Define qual registro será o inicio da busca pela hierarquia.
Este parâmetro não é obrigatório mas muito útil. Se você não informa-lo o select irá processar a hierarquia de todos os registros encontrados. - PRIOR: Utilizado apenas na clausula CONNECT BY, faz o relacionamento entre os níveis.
Deve-se informar este operador sempre antes de um campo que seja do nível anterior (superior, do ponto de vista do select). Pode utilizar o operador mais de uma vez para fazer filtros. - NOCYCLE: Pode ocorrer situações onde o relacionamento gere um loop o que poderia gerar um SELECT infinito. O banco detecta estas situações e gera o erro -26079 quando ocorre. Para evitar este erro deve-se utilizar este operador e então o retorno do select irá finalizar automaticamente no ultimo item antes de reiniciar o loop.
- LEVEL: Exibe o nível do registro dentro da hierarquia, baseado na hierarquia retornada pelo select.
Este operador também pode ser utilizado na clausula CONNECT para filtros. - CONNECT_BY_ISLEAF: Retorna 1 (numero um) se o registro é um leaf, ou seja, se é o ultimo registro da hierarquia e não possui filhos.
Este comando não pode ser utilizado em nenhuma clausula de condição (where, connect, start). - CONNECT_BY_ISCYCLE: Pode ser utilizado apenas quando o operador NOCYCLE foi especificado. Retorna 1 (numero um) quando o registro é o ultimo da hierarquia e possui o relacionamento que irá iniciar o loop. Este comando não pode ser utilizado em nenhuma clausula de condição (where, connect, start).
- CONNECT_BY_ROOT [campo]: Exibe um campo do registro pai/root na hierarquia do registro exibido. Este comando não pode ser utilizado em nenhuma clausula de condição (where, connect, start).
- SYS_CONNECT_BY_PATH(campo, separador): Retorna uma string (LVARCHAR(4000)) com o path de toda hierarquia utilizado o campo e separador especificado. Este comando não pode ser utilizado em nenhuma clausula de condição (where, connect, start).
- ORDER SIBLINGS BY: Este operador só pode ser utilizado com o CONNECT BY. Ele reordena apenas os itens que fazem parte do do mesmo nível na hierarquia.
Clasula WHERE
A clausula WHERE só é aplicada na query após os relacionamentos hierárquicos terem sido feitos, portanto não considere ela como seu filtro principal. Para filtros primários e básicos utilize a clausula CONNECT BY.
Abaixo segue a ordem que o Informix processa internamente os dados.
Copiado do Manual IDS 11.50 xC5 - SQL Syntax, pagina 2-583
The clauses of a SELECT statement that includes the Hierarchical clause are
processed in the following sequence:
1. FROM clause (for only a single table object in the current database)
2. Hierarchical clause
3. WHERE clause (without join predicates)
4. GROUP BY clause
5. HAVING clause
6. Projection clause
7. ORDER BY clause
Exemplos
# Crio uma estrutura de empresas onde monto relações hierárquicas entre # elas, identificando o tipo de cada uma e quem é a empresa pai # através do campo cd_emp_pai. create table empresas ( cd_emp int , desc varchar(16) , tipo char(2) check (tipo in ("F","QG","M","P")) , cd_emp_pai int ); Table created. alter table empresas add constraint primary key (cd_emp) constraint pk_emp; Table altered. insert into empresas values ( 1, "Matriz", "M" , null ); insert into empresas values ( 2, "QG_SP", "QG" , 1 ); insert into empresas values ( 3, "Filial_SP1", "F" , 2 ); insert into empresas values ( 4, "Filial_SP2", "F" , 2 ); insert into empresas values ( 5, "Filial_SP3", "F" , 2 ); insert into empresas values ( 6, "Posto_Avancado 1", "P" , 3 ); insert into empresas values ( 7, "Posto_Avancado 2", "P" , 3 ); insert into empresas values ( 8, "Posto_Avancado 3", "P" , 4 ); insert into empresas values ( 9, "QG_RJ", "QG" , 1 ); insert into empresas values (10, "Filial_RJ1", "F" , 9 ); insert into empresas values (11, "Filial_RJ2", "F" , 9 ); insert into empresas values (12, "Posto_Avancado 4", "P" , 11 ); # Seleciono toda hierarquia de baixo p/ cima, do "Posto Avancado 4" # até a matriz SELECT * FROM empresas START WITH cd_emp = (select max(cd_emp) from empresas) CONNECT BY PRIOR cd_emp_pai = cd_emp; cd_emp desc tipo cd_emp_pai 12 Posto_Avancado 4 P 11 11 Filial_RJ2 F 9 9 QG_RJ QG 1 1 Matriz M 4 row(s) retrieved. # Executo no sentido inverso, de cima p/ baixo, ou seja, # da Matriz até o ultimo item. # Repare que mudo de o operador PRIOR para o campo cd_emp # que indica então que este campo é o do registro superior na hierarquia SELECT * FROM empresas START WITH tipo = "M" CONNECT BY cd_emp_pai = PRIOR cd_emp ; cd_emp desc tipo cd_emp_pai 1 Matriz M 9 QG_RJ QG 1 11 Filial_RJ2 F 9 12 Posto_Avancado 4 P 11 10 Filial_RJ1 F 9 2 QG_SP QG 1 5 Filial_SP3 F 2 4 Filial_SP2 F 2 8 Posto_Avancado 3 P 4 3 Filial_SP1 F 2 7 Posto_Avancado 2 P 3 6 Posto_Avancado 1 P 3 12 row(s) retrieved. # Altero código da Matriz de modo que criamos um loop # eterno ao utilizar o CONNECT BY, desta maneira # o banco detecta o loop e gera o erro: # 26079: CONNECT BY query resulted in a loop/cycle. UPDATE empresas SET cd_emp_pai = 12 where cd_emp = 1 ; 1 row(s) updated. SELECT * FROM empresas START WITH cd_emp = (select max(cd_emp) from empresas) CONNECT BY PRIOR cd_emp_pai = cd_emp ; cd_emp desc tipo cd_emp_pai 12 Posto_Avancado 4 P 11 11 Filial_RJ2 F 9 9 QG_RJ QG 1 26079: CONNECT BY query resulted in a loop/cycle. Error in line 39 Near character position 35 # Para evitar este problema basta utilizar o comando # NOCYCLE que indica para não realizar estas querys recursivas. SELECT * , CONNECT_BY_ISCYCLE FROM empresas START WITH cd_emp = (select max(cd_emp) from empresas) CONNECT BY NOCYCLE PRIOR cd_emp_pai = cd_emp ; cd_emp desc tipo cd_emp_pai connect_by_iscycle 12 Posto_Avancado 4 P 11 0 11 Filial_RJ2 F 9 0 9 QG_RJ QG 1 0 1 Matriz M 12 1 4 row(s) retrieved. # Desfaço o loop UPDATE empresas SET cd_emp_pai = null where cd_emp = 1 ; 1 row(s) updated. # Demonstração dos comandos CONNECT_BY_ISLEAF e LEVEL # Observe que o resultado destes comandos são referentes # ao todo conteúdo retornado, portanto não tem relação com # situação dos dados na tabela. SELECT *, CONNECT_BY_ISLEAF leaf, LEVEL FROM empresas START WITH cd_emp = 6 CONNECT BY PRIOR cd_emp_pai = cd_emp; cd_emp desc tipo cd_emp_pai leaf level 6 Posto_Avancado 1 P 3 0 1 3 Filial_SP1 F 2 0 2 2 QG_SP QG 1 0 3 1 Matriz M 1 4 4 row(s) retrieved. SELECT *, CONNECT_BY_ISLEAF leaf, LEVEL FROM empresas START WITH tipo = "M" CONNECT BY cd_emp_pai = PRIOR cd_emp ; cd_emp desc tipo cd_emp_pai leaf level 1 Matriz M 0 1 9 QG_RJ QG 1 0 2 11 Filial_RJ2 F 9 0 3 12 Posto_Avancado 4 P 11 1 4 10 Filial_RJ1 F 9 1 3 2 QG_SP QG 1 0 2 5 Filial_SP3 F 2 1 3 4 Filial_SP2 F 2 0 3 8 Posto_Avancado 3 P 4 1 4 3 Filial_SP1 F 2 0 3 7 Posto_Avancado 2 P 3 1 4 6 Posto_Avancado 1 P 3 1 4 12 row(s) retrieved. # Aqui podemos ver a utilização do operador LEVEL na # parte de filtro do CONNECT SELECT *, CONNECT_BY_ISLEAF leaf, LEVEL FROM empresas START WITH tipo = "M" CONNECT BY cd_emp_pai = PRIOR cd_emp AND LEVEL <=3 ; cd_emp desc tipo cd_emp_pai leaf level 1 Matriz M 0 1 9 QG_RJ QG 1 0 2 11 Filial_RJ2 F 9 1 3 10 Filial_RJ1 F 9 1 3 2 QG_SP QG 1 0 2 5 Filial_SP3 F 2 1 3 4 Filial_SP2 F 2 1 3 3 Filial_SP1 F 2 1 3 8 row(s) retrieved. # Demonstração do comando CONNECT_BY_ROOT e do filtro WHERE # Observe que o nivel (campo LEVEL) não considera o registro # filtrado pela clausula WHERE SELECT * , CONNECT_BY_ROOT desc as root_desc , LEVEL FROM empresas WHERE tipo != "M" START WITH tipo = "F" CONNECT BY cd_emp_pai = PRIOR cd_emp ; cd_emp desc tipo cd_emp_pai root_desc level 11 Filial_RJ2 F 9 Filial_RJ2 1 12 Posto_Avancado 4 P 11 Filial_RJ2 2 10 Filial_RJ1 F 9 Filial_RJ1 1 5 Filial_SP3 F 2 Filial_SP3 1 4 Filial_SP2 F 2 Filial_SP2 1 8 Posto_Avancado 3 P 4 Filial_SP2 2 3 Filial_SP1 F 2 Filial_SP1 1 7 Posto_Avancado 2 P 3 Filial_SP1 2 6 Posto_Avancado 1 P 3 Filial_SP1 2 9 row(s) retrieved. # Demonstração do comando SYS_CONNECT_BY_PATH. # Neste exemplo utilizo o CAST apenas para diminuir o tamanho da # string para que retorne todos os campos em uma unica linha. SELECT cd_emp, desc, tipo , cast(SYS_CONNECT_BY_PATH(desc, "/") as char(42)) as path FROM empresas START WITH tipo = "M" CONNECT BY cd_emp_pai = PRIOR cd_emp ; cd_emp desc tipo path 1 Matriz M /Matriz 9 QG_RJ QG /Matriz/QG_RJ 11 Filial_RJ2 F /Matriz/QG_RJ/Filial_RJ2 12 Posto_Avancado 4 P /Matriz/QG_RJ/Filial_RJ2/Posto_Avancado 4 10 Filial_RJ1 F /Matriz/QG_RJ/Filial_RJ1 2 QG_SP QG /Matriz/QG_SP 5 Filial_SP3 F /Matriz/QG_SP/Filial_SP3 4 Filial_SP2 F /Matriz/QG_SP/Filial_SP2 8 Posto_Avancado 3 P /Matriz/QG_SP/Filial_SP2/Posto_Avancado 3 3 Filial_SP1 F /Matriz/QG_SP/Filial_SP1 7 Posto_Avancado 2 P /Matriz/QG_SP/Filial_SP1/Posto_Avancado 2 6 Posto_Avancado 1 P /Matriz/QG_SP/Filial_SP1/Posto_Avancado 1 12 row(s) retrieved. # Abaixo segue algumas demonstrações de filtros no CONNECT # e WHERE SELECT *, LEVEL FROM empresas START WITH tipo = "M" CONNECT BY cd_emp_pai = PRIOR cd_emp AND tipo IN ("QG","F") ; cd_emp desc tipo cd_emp_pai level 1 Matriz M 1 9 QG_RJ QG 1 2 11 Filial_RJ2 F 9 3 10 Filial_RJ1 F 9 3 2 QG_SP QG 1 2 5 Filial_SP3 F 2 3 4 Filial_SP2 F 2 3 3 Filial_SP1 F 2 3 8 row(s) retrieved. SELECT *, LEVEL FROM empresas WHERE LEVEL != 2 START WITH tipo = "M" CONNECT BY cd_emp_pai = PRIOR cd_emp AND tipo IN ("QG","F") ; cd_emp desc tipo cd_emp_pai level 1 Matriz M 1 11 Filial_RJ2 F 9 3 10 Filial_RJ1 F 9 3 5 Filial_SP3 F 2 3 4 Filial_SP2 F 2 3 3 Filial_SP1 F 2 3 6 row(s) retrieved. SELECT *, LEVEL FROM empresas START WITH tipo = "M" CONNECT BY cd_emp_pai = PRIOR cd_emp AND ( ( LEVEL = 2 and tipo = "QG" ) OR ( LEVEL = 3 and tipo = "F" )) ; cd_emp desc tipo cd_emp_pai level 1 Matriz M 1 9 QG_RJ QG 1 2 11 Filial_RJ2 F 9 3 10 Filial_RJ1 F 9 3 2 QG_SP QG 1 2 5 Filial_SP3 F 2 3 4 Filial_SP2 F 2 3 3 Filial_SP1 F 2 3 8 row(s) retrieved. # Exemplo de registros sendo ordenado pelo campo DESC # através do comando ORDER SIBLINGS BY SELECT *, LEVEL FROM empresas START WITH tipo = "M" CONNECT BY cd_emp_pai = PRIOR cd_emp ORDER SIBLINGS BY desc ; cd_emp desc tipo cd_emp_pai level 1 Matriz M 1 9 QG_RJ QG 1 2 10 Filial_RJ1 F 9 3 11 Filial_RJ2 F 9 3 12 Posto_Avancado 4 P 11 4 2 QG_SP QG 1 2 3 Filial_SP1 F 2 3 6 Posto_Avancado 1 P 3 4 7 Posto_Avancado 2 P 3 4 4 Filial_SP2 F 2 3 8 Posto_Avancado 3 P 4 4 5 Filial_SP3 F 2 3 12 row(s) retrieved. Database closed.
Debugando
Para identificar como um select hierarquico está sendo utilizado, utilize o comando SET EXPLAIN, através deste comando é possível ver e entender como o Informix faz os relacionamentos internamente e aplica os filtros.
Exemplo de um output do SET EXPLAIN:
QUERY: (OPTIMIZATION TIMESTAMP: 09-29-2009 15:49:43)
------
SELECT *
, CONNECT_BY_ROOT desc as root_desc
, LEVEL
FROM empresas
WHERE tipo != "M"
START WITH tipo = "F"
CONNECT BY cd_emp_pai = PRIOR cd_emp
Connect by Query Rewrite:
---------------------------
select x0.cd_emp ,x0.desc ,x0.tipo ,x0.cd_emp_pai ,x0.dtab_22679_439_desc ,x0.level
from (select x1.cd_emp ,x1.desc ,x1.tipo ,x1.cd_emp_pai ,x1.cd_emp ,1 ,1 ,0 ,x1.desc
from "informix".empresas x1 where (x1.tipo = 'F' ) union all select x2.cd_emp ,x2.desc ,x2.tipo
,x2.cd_emp_pai ,x2.cd_emp ,(level + 1 ) ::integer ,connect_by_isleaf ,dtab_22679_439_stkcol
,dtab_22679_439_desc from "informix".empresas x2 ,"informix".dtab_22679_439 x0
where (x2.cd_emp_pai = dtab_22679_439_p_cd_emp ) )
x0(cd_emp,desc,tipo,cd_emp_pai,dtab_22679_439_p_cd_emp,level,connect_by_isleaf,dtab_22679_439_stkcol
,dtab_22679_439_desc)where (x0.tipo != 'M' )
Estimated Cost: 1
Estimated # of Rows Returned: 2
1) informix.dtab_22679_439: COLLECTION SCAN
Subquery:
---------
Estimated Cost: 8
Estimated # of Rows Returned: 2
1) informix.empresas: SEQUENTIAL SCAN
Filters: informix.empresas.tipo = 'F'
Subquery:
---------
Estimated Cost: 5
Estimated # of Rows Returned: 1
1) informix.dtab_22679_439: SEQUENTIAL SCAN
2) informix.empresas: SEQUENTIAL SCAN
DYNAMIC HASH JOIN (Build Outer)
Dynamic Hash Filters: informix.empresas.cd_emp_pai = informix.dtab_22679_439.dtab_22679_439_p_cd_emp
Filters: informix.dtab_22679_439.tipo != 'M'
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 dtab_22679_439
type table rows_prod time
-----------------------------------
clscan t1 9 00:00.00
- 2484 leituras
Tags:
