SQB's questions

Eu tenho várias colunas que armazenam dados que podem conter alguns caracteres extras para formatação/agrupamento. Pense em espaços em branco e traços em dados como números de telefone e números de placas.

Ao consultar, não podemos ter certeza se e onde esses traços estão presentes, então precisamos removê-los da coluna. Fazer isso durante a consulta não é ótimo para o desempenho, então quero criar uma coluna computada para a coluna removida e colocar um índice nela.

-- trimming the length down to 50 characters
-- which is the length of the original field
ALTER TABLE foo
ADD search_license_plate AS (LEFT(REPLACE(license_plate, '-', ''), 50));

CREATE INDEX ix_foo_search_license_plate ON foo(search_license_plate);

Essas colunas computadas são sempre precisas, portanto, não precisam ser persistidas devido à falta de precisão.

Os dados normalmente serão inseridos apenas uma vez e raramente serão alterados. Nunca em grandes volumes, então não me importo muito com qualquer impacto no desempenho de persistir a coluna.
Além disso, a coluna computada nunca será retornada de uma consulta; ele só será usado em condições na consulta, normalmente

SELECT
   foo.id,
   foo.license_plate
FROM foo
WHERE foo.search_license_plate LIKE '%bar%';

... embora eu não possa garantir que não haverá nada SELECT *feito nessas mesas.

Existe alguma necessidade de persistir essas colunas?

Eu tenho uma visão no MySQL, que tem alguns campos calculados - COALESCEe afins. Se eu fizer uma junção externa esquerda nessa exibição (para que os registros dessa exibição possam estar ausentes), os campos calculados ainda serão calculados para os registros ausentes, em vez de aparecer como NULL.

Exemplo

Testei o exemplo a seguir no MySQL versões 5.5.40-36.1-log e 5.5.53-38.5 (porque é o que tenho em mãos), bem como no SQL Fiddle ^‡ versões 5.5 e 5.6.

Configurar

-- A simple view
CREATE OR REPLACE VIEW foo
AS
      SELECT 1 AS x
UNION SELECT 2
UNION SELECT 3;

-- Another simple view
CREATE OR REPLACE VIEW bar
AS
      SELECT 1 AS y
UNION SELECT 2
UNION SELECT 3;

-- A (contrived) view with a calculated column
CREATE OR REPLACE VIEW baz
AS
SELECT
   f.x,
   b.y,
   SIGN(COALESCE(b.y, 0)) AS z
FROM foo f
LEFT JOIN bar b ON f.x = 2 * b.y;

Consulta

-- A query that does a left outer join
-- on the view with the calculated column
SELECT *
FROM foo f
LEFT JOIN baz b ON f.x = b.y;

Resultado esperado

Eu esperaria o seguinte resultado, onde vemos uma linha com registros de baze duas linhas sem.

| x | x | y | z |
-----------------
| 1 | 2 | 1 | 1 |
| 2 | - | - | - |
| 3 | - | - | - |

Resultado atual

Mas, em vez disso, recebo o seguinte, com de fato uma linha com registros de baz, mas duas linhas onde não há registros, bazexceto para a coluna calculada, que é calculada para todas as três linhas!

| x | x | y | z |
-----------------
| 1 | 2 | 1 | 1 |
| 2 | - | - | 0 |
| 3 | - | - | 0 |

Assim zé calculado, mesmo quando baznão retorna nenhum registro (como evidenciado pelos NULLs para xe y).

Meu caso real envolve uma junção entre a visão e uma tabela na qual a visão é baseada. Eu repliquei isso no exemplo, usando apenas visualizações. Eu também testei sem a "auto-junção"; isso não faz diferença.

Gambiarra

O que funciona para mim, está se escondendo bazem uma sub-seleção, assim.

SELECT *
FROM foo f
LEFT JOIN (
   SELECT *
   FROM baz
) b ON f.x = b.y;

Isso produz o resultado esperado:

| x | x | y | z |
-----------------
| 1 | 2 | 1 | 1 |
| 2 | - | - | - |
| 3 | - | - | - |

Este é o comportamento esperado? Por quê?

‡: funcionou para mim ontem, não consigo fazer funcionar hoje.

Eu tenho várias tabelas que possuem gatilhos que inserem registros em tabelas de enfileiramento e registro. O problema é que essas inserções fazem parte da mesma transação. Isso significa que qualquer DIU nas tabelas anteriores pode ser bloqueado por outro DIU que acione inserções na mesma fila, e aquele DIU que falhar não será registrado, pois essas inserções também serão revertidas.

Não me importo muito com entradas na fila onde o DIU desencadeador foi revertido; a fila é lida por algum processo que invalida/atualiza o cache e ações semelhantes. Caso algo tenha sido revertido, algum cache será atualizado com os mesmos dados. Nada demais.
O caso de negócios aqui é um site com um grande número de conjuntos que consistem em produtos únicos. Qualquer produto pode estar em até 15.000 conjuntos. Se o preço de um produto mudar, os preços de todos esses conjuntos precisam ser recalculados. Não quero fazer isso na mesma transação ao salvar o preço de um produto, porque isso levaria muito tempo e bloquearia muito o banco de dados, então enfileiro o recálculo.

Para registrar, há algumas ações que desejo registrar, mesmo ( especialmente ) se elas falharem. Eu não posso fazer isso agora.

Se fosse esse Oracle, que usei até pouco tempo atrás, eu teria usado PRAGMA AUTONOMOUS_TRANSACTIONpara lidar com o insert em uma transação separada, que sempre confirmaria não importando o que a outra transação fizesse.

Posso emular esse comportamento usando tabelas MyISAM para os logs e as filas, com concurrent_insertdefinido como ALWAYS, enquanto o restante das minhas tabelas são InnoDB? (Espero várias inserções neles, esse é o problema , embora raramente sejam lidos (logs) ou apenas uma vez (filas)).

Estou tentando decodificar html contendo entidades html.
Eu tentei dbms_xmlgen.converte , utl_i18n.unescape_referencemas os resultados são menos do que satisfatórios.

SET ESCAPE ON;
SELECT
   'dbms_xmlgen.convert' AS method,
   dbms_xmlgen.convert('\♥', 1) AS hearts,
   dbms_xmlgen.convert('\&',    1) AS amp_ent,
   dbms_xmlgen.convert('\&',  1) AS amp_dec,
   dbms_xmlgen.convert('\&', 1) AS amp_hex,
   dbms_xmlgen.convert('\激\光', 1) AS chinese_laser 
FROM dual
UNION ALL
SELECT
   'utl_i18n.unescape_reference',
   utl_i18n.unescape_reference('\♥'),
   utl_i18n.unescape_reference('\&'),
   utl_i18n.unescape_reference('\&'),
   utl_i18n.unescape_reference('\&'),
   utl_i18n.unescape_reference('\激\光') 
FROM dual;

Os resultados que obtenho são:

METHOD                        HEARTS      AMP_ENT     AMP_DEC     AMP_HEX     CHINESE_LASER
----------------------------------------------------------------------------------------------
dbms_xmlgen.convert           ♥    &           &     &    激光
utl_i18n.unescape_reference   ¿           &           &           &           ¿¿

Meu problema real envolve caracteres chineses, processados ​​por um programa Java para criar relatórios em PDF. Não tenho acesso fácil ao código Java, mas tenho controle sobre a consulta utilizada pelo programa.

Um exemplo de caracteres chineses que uso para teste é 激光, que o Google Tradutor me diz que significa 'laser' e que recebo codificado como 激光. Estes não são decodificados corretamente, como pode ser visto no exemplo acima.

Percebo que na segunda linha, os pontos de interrogação invertidos parecem indicar que as entidades foram convertidas, mas não podem ser exibidas corretamente. Porém, é o próprio Oracle fazendo isso, ou o cliente (tentei tanto no SQL+ quanto no Toad)? Quando eu conecto utl_i18n.unescape_referencea consulta usada pelo programa Java, ela funciona para entidades como ±(±), mas, novamente, não para os caracteres chineses.

Como posso ter todas as entidades devidamente decodificadas?

• Devo usar outra função? (Estes foram recomendados em algum lugar na Internet ).
• Devo alterar algumas configurações? (Configurações relevantes mostradas abaixo).

Informação relevante

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
SQL*Plus: Release 10.1.0.5.0

NLS_LANGUAGE                           AMERICAN
NLS_TERRITORY                          AMERICA
NLS_CHARACTERSET                       WE8MSWIN1252
NLS_NCHAR_CHARACTERSET                 AL16UTF16