sql - Necesita una mejor opción: unión externa 32 veces a la misma mesa

join query-optimization

Question

Tengo un desagradable problema de consulta SQL y me encantaría ayudar con una solución elegante. Estoy tratando de evitar 32 uniones externas izquierdas a la misma mesa.

La base de datos es Teradata.

Tengo una mesa con 14 millones de registros y 33 columnas. La clave principal (llamémoslo Trans_Id) y 32 campos codificados (llamémosles codificados_1 ... codificados_32). Algo como esto:

CREATE SET TABLE BigTable ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT
(
TRANS_ID VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
ENCODED_1 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
ENCODED_2 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
ENCODED_3 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
...
ENCODED_32 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC )
PRIMARY INDEX ( TRANS_ID );

También tengo una sola tabla con los valores codificados / decodificados. Digamos que hay 100 registros en esta tabla.

CREATE SET TABLE LookupTable ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT
(
UNIQ_PK { just random numbers }
ENCODED_VAR VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
DECODED_DESC VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( UNIQ_PK );

Quiero evitar una unión desagradable como esta (utilicé elipsis en lugar de mostrar las 32 combinaciones externas):

SELECT
TRANS_ID
, a.ENCODED_1
, b1.DECODED_DESC DECODED_DESC_1
, a.ENCODED_2
, b2.DECODED_DESC DECODED_DESC_2
...
, a.ENCODED_31
, b31.DECODED_DESC DECODED_DESC_31
, a.ENCODED_32
, b32.DECODED_DESC DECODED_DESC_32
FROM BigTable a
LEFT OUTER JOIN LookupTable b1 ON a.ENCODED_1 = b1.ENCODED
LEFT OUTER JOIN LookupTable b2 ON a.ENCODED_2 = b1.ENCODED
...
LEFT OUTER JOIN LookupTable b31 ON a.ENCODED_31 = b31.ENCODED
LEFT OUTER JOIN LookupTable b32 ON a.ENCODED_32 = b32.ENCODED

Cualquier ayuda sería apreciada. Tengo la sensación de que unirme a 14 millones de registros 32 veces no es la manera más eficiente de hacerlo.

Answer 1

¿No podrías tenerlo así?

SELECT
TRANS_ID
, a.ENCODED_1
, CASE a.ENCODED_1 WHEN b.ENCODED THEN b.DECODED_DESC END DECODED_DESC_1
, a.ENCODED_2
, CASE a.ENCODED_2 WHEN b.ENCODED THEN b.DECODED_DESC END DECODED_DESC_2
...
, a.ENCODED_31
, CASE a.ENCODED_31 WHEN b.ENCODED THEN b.DECODED_DESC END DECODED_DESC_31
, a.ENCODED_32
, CASE a.ENCODED_32 WHEN b.ENCODED THEN b.DECODED_DESC END DECODED_DESC_32
FROM BigTable a
LEFT JOIN LookupTable b ON (
a.ENCODED_1 = b.ENCODED OR
a.ENCODED_2 = b.ENCODED OR
...
a.ENCODED_31 = b.ENCODED OR
a.ENCODED_32 = b.ENCODED
)

?

También podría estar tentado a reescribir la condición de unión de la siguiente manera:

...ON b.ENCODED IN (a.ENCODED_1, a.ENCODED_2, ... a.ENCODED_31, a.ENCODED_32)

Pero no estoy seguro si no puede ser más lento que la versión anterior. En realidad, supongo que sí, sería más lento, pero aún así lo verificaría.

Answer 2

Cambiaría el PI en la tabla LookUp por el Encoded_Var para principiantes. Ya debe redistribuir la tabla grande en cada una de las columnas Encoded_Var para unirse a la tabla LookUp. ¿Por qué molestarse en tener que redistribuir la tabla LookUp cada vez también?

¿Hay alguna razón por la cual el diseño de su mesa no sea más cercano a

CREATE SET TABLE BigTable ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT
(
TRANS_ID VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
ENCODED_VAR VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL
UNIQUE PRIMARY INDEX ( TRANS_ID, ENCODED_VAR );

Esto construiría una relación 1: M más apropiada entre un trans_id y encoded_var. A menos que haya detalles pertinentes que se hayan omitido que expliquen por qué esto no funcionaría. De hecho, si es necesario, puede compilar esta tabla como tabla de relaciones y tener otra tabla que se vea como:

 CREATE SET TABLE BigTable2 ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT
(
TRANS_ID VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
OtherData1 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
OtherData2 SMALLINT NOT NULL,
....,
OtherDataN VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL
UNIQUE PRIMARY INDEX ( TRANS_ID );

Espero que ayude.

Answer 3

He encontrado el mismo problema, también en Teradata. Un colega me ha informado de una solución elegante para usted que utiliza una sola UNIÓN EXTERIOR IZQUIERDA y una serie de instrucciones CASE.

Sin embargo, su ejemplo es un poco confuso, porque se está uniendo en una columna, el no existe (columna "ENCODED" en "LookupTable", que supongo que debe ser "ENCODED_VAR"?).

SELECT TRANS_ID
, a.ENCODED_1
, MAX(CASE WHEN b.ENCODED_VAR = a.ENCODED_1
THEN b.DECODED_DESC
ELSE NULL
END) DECODED_DESC_1
, a.ENCODED_2
, MAX(CASE WHEN b.ENCODED_VAR = a.ENCODED_2
THEN b.DECODED_DESC
ELSE NULL
END) DECODED_DESC_2
...
, a.ENCODED_31
, MAX(CASE WHEN b.ENCODED_VAR = a.ENCODED_31
THEN b.DECODED_DESC
ELSE NULL
END) DECODED_DESC_31
, a.ENCODED_32
, MAX(CASE WHEN b.ENCODED_VAR = a.ENCODED_32
THEN b.DECODED_DESC
ELSE NULL
END) DECODED_DESC_32
FROM BigTable a
LEFT OUTER JOIN LookupTable b
ON a.ENCODED_1 = b.ENCODED_VAR
OR a.ENCODED_2 = b.ENCODED_VAR
...
OR a.ENCODED_31 = b.ENCODED_VAR
OR a.ENCODED_32 = b.ENCODED_VAR
GROUP BY a.TRANS_ID 

Esto se basa en que existe una relación 1: 1 entre ENCODED_n en BigTable y ENCODED_VAR en LookupTable.

Además, como un aparte, no debe usar un número aleatorio como INDICE PRIMARIO en una tabla de Teradata. Si bien esto le dará una excelente distribución de la tabla, será completamente inútil al hacer una búsqueda en la tabla. Si utiliza un campo comúnmente unido para el PI, la base de datos puede ir directamente al AMP en el que se almacenan los datos. Sin embargo, sin esto, el DBMS debe hacer un escaneo de tabla completa cada vez. Es probable que use ENCODED_VAR como su ÍNDICE PRIMARIO y observe un rendimiento mucho mejor, siempre que la distribución sea razonable.

Espero que esto funcione. Creo que esto funcionará en tu caso. No he verificado que mi código sea correcto, pero es muy similar a mi propia solución.

Answer 4

Podría crear una función que tome como parámetro el VARCHAR (10) encoded_var y devolver el VARCHAR (50) decoded_desc, luego su selección sería algo como esto:

SELECT TRANS_ID,
ENCODED_1, somefunc(ENCODED_1) AS DECODED_DESC_1,
ENCODED_2, somefunc(ENCODED_2) AS DECODED_DESC_2,
etc.

Dependiendo de la cantidad de filas que planea regresar a la vez, esto sería factible.

Answer 5

Si codificado_1, codificado_2, etc. se están utilizando como claves de búsqueda en la misma tabla, parece que son la "misma idea". Pero mi primer pensamiento es que un mejor diseño en este caso sería:

big_table (trans_id, var_id, encoded_var)
lookup_table (encoded_var, decoded_desc)

Entonces la consulta se convierte en:

select trans_id, var_id, encoded_var, decoded_desc
from big_table
join lookup_table on lookup_table.encoded_var=big_table.encoded_var

No sé si este es el nombre real del campo o si simplemente está tratando de omitir detalles irrelevantes. Puede estar omitiendo detalles relevantes aquí. ¿Cuál es la diferencia entre encoded_1 y codded_2, etc.? Si son intercambiables, no hay ninguna razón para tener campos separados para ellos. De hecho, causa muchos problemas. Incluso si hay una diferencia semántica, si todos usan la misma tabla de búsqueda, todos deben provenir del mismo dominio.

Por ejemplo, hace algunos años trabajé en un sistema para administrar manuales técnicos que nuestra organización producía y usaba. Cada manual tenía 3 gerentes. (Un gerente administrativo que manejaba los presupuestos y los cronogramas, un gerente de stock que realizaba un seguimiento de quién necesitaba copias y se aseguraba de que los obtuviera, y un gerente de contenido responsable del texto en sí). Pero todos fueron extraídos de la misma lista de personas, como a menudo la misma persona tendría más de uno de estos roles o podría tener diferentes roles para diferentes manuales. Así que hicimos una tabla de "personas" con una identificación, nombre, dirección de correo electrónico, etc., y luego en el registro manual básico creé 3 columnas, una para cada tipo de administrador.

Esto fue un enorme error. Lo que debería haber hecho fue crear una tabla separada con identificación manual, ID de tipo de administrador e ID de persona, y luego tener 3 REGISTROS para los 3 tipos de administradores en lugar de 3 campos dentro de un registro.

¿Por qué? Con tres columnas, encontré el mismo problema que está describiendo, aunque en una escala menor: tuve que unirme de la tabla manual a la tabla de personas tres veces. Una consulta como "¿De qué libros es responsable Bob Smith?" requirió una consulta compleja sorprendente, algo así como

select ... whatever ...
from manual
join person p1 on p1.person_id=manual.admin_id
join person p2 on p2.person_id=manual.stockmanager_id
join person p3 on p3.person_id=manual.contentmanager_id
where p1.name=''Bob Smith''
or p2.name=''Bob Smith''
or p3.name=''Bob Smith''

Con una sola columna, habría sido simplemente

select ... whatever ...
from manual
join manual_manager on manual_manager.manual_id=manual.manual_id
join person on person.person_id=manual_manager.person_id
where person.name=''Bob Smith''"

Con toda la repetición, no fue sorprendente que hubo un par de veces que un programador accidentalmente solo verificó 2 de los campos en lugar de los 3. Con 1 campo este error no sería posible. Con 3 campos, si agregamos un 4º tipo de administrador, tendríamos que agregar otra columna y luego cambiar cada consulta que analice estos campos. Con 1 campo, probablemente no lo hagamos. Etc.

Con 3 campos necesitamos 3 índices, y hay otras implicaciones de rendimiento.

Sospecho que el mismo tipo de pensamiento se aplica a ti.

Si sus 32 campos son completamente intercambiables, entonces la tabla solo necesitaría un número de secuencia para hacer un pk único. Si hay alguna diferencia entre ellos, entonces puedes crear un código para distinguirlos.

Answer 6

Si no quiere escribir repetidamente la misma consulta, le sugiero ponerla en una vista.

Para el rendimiento, sugeriría lo siguiente:

• Como recomienda Rob Paller, cambie el índice primario en LookupTable a ENCODED_VAR.
• En LookupTable, agregue un nuevo registro con DECODED_DESC = null y ENCODED_VAR = algún valor que nunca usará. Actualice BigTable para reemplazar todo nulo ENCODED_ * con este valor. A continuación, puede cambiar su consulta para usar todas las combinaciones internas y obtener el mismo resultado.