All perguntas(dba)

Estou tentando gerar números de ordem de compra exclusivos que começam em 1 e incrementam em 1. Tenho uma tabela PONumber criada usando este script:

CREATE TABLE [dbo].[PONumbers]
(
  [PONumberPK] [int] IDENTITY(1,1) NOT NULL,
  [NewPONo] [bit] NOT NULL,
  [DateInserted] [datetime] NOT NULL DEFAULT GETDATE(),
  CONSTRAINT [PONumbersPK] PRIMARY KEY CLUSTERED ([PONumberPK] ASC)    
);

E um procedimento armazenado criado usando este script:

CREATE PROCEDURE [dbo].[GetPONumber] 
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [dbo].[PONumbers]([NewPONo]) VALUES(1);
    SELECT SCOPE_IDENTITY() AS PONumber;
END

No momento da criação, isso funciona bem. Quando o procedimento armazenado é executado, ele começa no número desejado e é incrementado em 1.

O estranho é que, se eu desligar ou hibernar meu computador, na próxima vez que o procedimento for executado, a sequência avançará quase 1.000.

Veja os resultados abaixo:

Você pode ver que o número saltou de 8 para 1002!

• Por que isso está acontecendo?
• Como posso garantir que os números não sejam ignorados assim?
• Tudo o que preciso é que o SQL gere números que sejam:
  • a) Único garantido.
  • b) incrementar pelo valor desejado.

Admito que não sou um especialista em SQL. Eu entendi mal o que SCOPE_IDENTITY() faz? Devo usar uma abordagem diferente? Pesquisei as sequências no SQL 2012+, mas a Microsoft diz que não há garantia de que sejam exclusivas por padrão.

Digamos que eu tenha uma tabela grande que contém as informações do usuário e outra tabela que contém vários locais. Então eu uso outra tabela que contém o user_id e o location_id.

Para recuperar os dados, tenho que usar a consulta Left Join. Isso não torna todo o processo mais longo para recuperar em vez de ter tudo em uma tabela? Por exemplo, eu poderia ter a localização como texto na mesma tabela.

EDIT: Aqui está um exemplo.

CREATE TABLE  `user` (
`id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `gender` enum('M','F') DEFAULT NULL
);

CREATE TABLE `user_location` (
  `user_id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL
);

CREATE TABLE `location` (
`id` int(11) NOT NULL,
  `location` varchar(45),
  `parent_id` varchar(45) 
);

Observação: assuma que todos os campos relacionados estão indexados corretamente entre eles.

Editar: atualmente tenho um grande banco de dados com usuários que recuperam sua localização por meio de uma tabela de junção, conforme descrito acima. Me pediram para otimizar o banco de dados porque os resultados da pesquisa são lentos. Eu adicionei memcachee melhorou significativamente, mas agora estou apenas querendo saber sobre Left Joins.

Por exemplo, A consulta atual é algo assim:

SELECT * FROM users 
LEFT JOIN user_location 
ON user_location.user_id = user.id 
LEFT JOIN location
ON location.id = user_location.location_id;

E isso é apenas para obter a localização. Eles têm vários outros campos que são recuperados por meio de junções e todos são necessários para visualizar o perfil de um usuário. Temos números de telefone, endereços, senhas, DOB e muitos outros em tabelas diferentes.

Para que eu crie uma página para o perfil do usuário, tenho que enviar ao servidor uma consulta grande. Agora, após a primeira vez, ele é armazenado em cache e está tudo bem. Mas eu só estava me perguntando por que alguém construiria seu banco de dados assim?

Dada esta configuração no Postgres 9.4 atual ( desta questão relacionada ):

CREATE TABLE foo (ts, foo) AS 
VALUES (1, 'A')  -- int, text
     , (7, 'B');

CREATE TABLE bar (ts, bar) AS
VALUES (3, 'C')
     , (5, 'D')
     , (9, 'E');

db<>fiddle aqui (também da pergunta anterior).

Escrevi a SELECTcom a FULL JOINpara atingir o objetivo da questão referenciada. Simplificado:

SELECT ts, f.foo, b.bar
FROM   foo f
FULL   JOIN bar b USING (ts);

Conforme especificações, a forma correta de endereçar a coluna tsé sem qualificação de tabela. Qualquer um dos valores de entrada ( f.tsou b.ts) pode ser NULL. A USINGcláusula cria um caso estranho: introduzir uma coluna de "entrada" que não está realmente presente na entrada. Até agora tão elegante.

Eu coloquei isso em uma função plpgsql. Por conveniência (ou requisitos), quero os mesmos nomes de coluna para o resultado da função de tabela. Portanto, devemos evitar conflitos de nomenclatura entre nomes de colunas e parâmetros de função idênticos. Deve ser evitado escolhendo nomes diferentes, mas aqui estamos:

CREATE OR REPLACE FUNCTION f_merge_foobar()
  RETURNS TABLE(ts int, foo text, bar text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   FOR ts, foo, bar IN
      SELECT COALESCE(f.ts, b.ts), f.foo, b.bar
      FROM   foo f
      FULL   JOIN bar b USING (ts)
   LOOP
      -- so something
      RETURN NEXT;
   END LOOP;
END
$func$;

Ênfase em negrito para destacar o problema . Não posso usar tssem qualificação de tabela como antes, porque plpgsql geraria uma exceção (não estritamente necessária, mas provavelmente útil na maioria dos casos):

ERROR:  column reference "ts" is ambiguous
LINE 1: SELECT ts, f.foo, b.bar
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.

Sei que posso usar nomes diferentes ou uma subconsulta ou usar outra função. Mas eu me pergunto se há uma maneira de referenciar a coluna. Não consigo usar a qualificação de tabela. Alguém poderia pensar que deveria haver uma maneira.
Existe?

Qual certificado está sendo usado para criptografar cada um dos bancos de dados nas instâncias.

Eu posso obter os dados usando o seguinte, mas como escrevo as consultas

USE master
GO

-- this provides the list of certificates
SELECT * FROM sys.certificates


-- this provides the list of databases (encryption_state = 3) is encrypted
SELECT * FROM sys.dm_database_encryption_keys
 WHERE encryption_state = 3;

Percebi que as colunas sys.certifcates.thumbprint e sys.dm_database_encryption_keys.encryptor_thumbprint contêm os mesmos dados.

Eu tenho um caso típico em que a detecção de parâmetros faz com que um plano de execução "ruim" chegue ao cache do plano, fazendo com que as execuções subsequentes do meu procedimento armazenado sejam muito lentas. Posso "resolver" esse problema com variáveis ​​locais, OPTIMIZE FOR ... UNKNOWN, e OPTION(RECOMPILE). No entanto, também posso mergulhar na consulta e tentar otimizá-la.

Estou tentando determinar se devo : devido ao tempo limitado para corrigir problemas, gostaria de saber o custo de não fazê-lo. A meu ver, se eu continuar com OPTION(RECOMPILE), o efeito líquido é que um plano de consulta é recriado toda vez que a consulta é executada. Então, acho que preciso saber:

Como saber qual é o custo de criar um plano de consultas?

Para responder à minha própria pergunta, pesquisei no Google (por exemplo, com esta consulta ) e examinei a documentação das colunas para o dm_exec_query_statsDMV . Também inspecionei a janela de saída no SSMS para "Plano de consulta real" para encontrar essas informações. Por fim, pesquisei DBA.SE . Nenhum deles levou a uma resposta.

Alguém pode me dizer? É possível encontrar ou medir o tempo necessário para a criação do plano?

Tenho um banco de dados em Postgresql, que foi migrado do SQL Server (somente dados).
No SQL Server, uma tabela desse banco de dados possui estas colunas:

measure_id
datum
measure

onde measure_idé chave primária auto-incremental, datumé datetime e measureé float.
Após migração no Postrgresql, measure_idé coluna do tipo bigint.

Como posso alterar essa coluna ( measure_id) para bigserial e atribuí-la como chave primária, agora que minha tabela está cheia de dados?

Qual é a maneira recomendada de fazer backup de grandes conjuntos de dados no MongoDB? Digamos que temos um tamanho de dados na ordem de 10 TB - como você faria isso?

Estamos considerando um nó de conjunto de réplicas oculto, possivelmente atrasado. O atraso nos protegeria de quedas acidentais de todo o banco de dados. Esta é uma solução viável e quais outras opções você recomendaria investigar?

Obrigado!

Então, estou começando recentemente em uma nova empresa e tenho muitos usuários do ArcGIS que parecem realmente interessados ​​em avançar com uma instância do PostGIS para fornecer alguns dados aos nossos clientes. Embora eu não tenha problemas com isso, somos uma loja 95% SQL Server e 5% Oracle. Nosso GIS interno atual é executado no SQL Server e ainda não ouvi nenhuma reclamação.

Eu sei que o SQL Server tem muitos recursos espaciais/geométricos aprimorados a partir de 2012, mas há algum recurso matador no PostGIS que vale a pena entrar na nova plataforma? Eu tentei pesquisá-lo, mas não consigo encontrar nada realmente profundo ou que não seja completamente tendencioso.

Eu quero dar a eles as melhores ferramentas para fazer seu trabalho, mas também tenho que pesar o fato de que estarei aprendendo Postgres/GIS desde o início e isso é uma jornada inteira por si só.

Tenho mantido um registro de consultas caras em execução, juntamente com seus planos de consulta, em uma tabela para nos permitir monitorar tendências de desempenho e identificar áreas que precisam ser otimizadas.

No entanto, chegou ao ponto em que os planos de consulta estão ocupando muito espaço (já que estamos armazenando o plano inteiro em cada consulta).

Estou, portanto, tentando normalizar os dados existentes extraindo o QueryPlanHash e o QueryPlan para outra tabela.

CREATE TABLE QueryPlans
(
    QueryPlanHash VARBINARY(25),
    QueryPlan XML,
    CONSTRAINT PK_QueryPlans PRIMARY KEY
    (
      QueryPlanHash
    )
);

Como a definição de query_plan_hashin sys.dm_exec_query_statsé um campo binário (e estarei inserindo novos dados regularmente), eu estava usando VARBINARYpara o tipo de dados na minha nova tabela.

No entanto, a inserção abaixo falha ...

INSERT INTO QueryPlans
    ( QueryPlanHash, QueryPlan )
SELECT queryplanhash, queryplan
FROM
(
    SELECT 
      p.value('(./@QueryPlanHash)[1]', 'varchar(20)') queryplanhash,
      QueryPlan,
      ROW_NUMBER() OVER (PARTITION BY p.value('(./@QueryPlanHash)[1]', 'varchar(20)') ORDER BY DateRecorded) rownum
    FROM table
    CROSS APPLY QueryPlan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple[@QueryPlanHash]') t(p)
) data
WHERE rownum = 1

....com o erro

Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.

O problema é que os hashes do plano de consulta já estão em formato binário, porém armazenados como VARCHAR no Plano de Consulta XML, por exemplo

0x9473FBCCBC01AFE

e CONVERT to BINARY dá um valor completamente diferente

0x3078393437334642434342433031414645

Tentei alterar a definição de valor na seleção de XQuery para binário, mas não retornou nenhum valor.

Como eu extrairia o valor de 0x9473FBCCBC01AFEum plano de consulta XML como um VARBINARY, em vez de um VARCHAR?

Meu conhecimento de bancos de dados e SQL é baseado principalmente em aulas universitárias. De qualquer forma, passei poucos meses (quase um ano) em uma empresa, onde trabalhava com banco de dados.

Li poucos livros e participei de poucos treinamentos sobre bancos de dados como MySQL, PostgreSQL, SQLite, Oraclee também alguns nonSQL dbcomo nós MongoDB, Redis, ElasticSearchetc.

Bem como eu disse, sou iniciante, com muitos desconhecimentos mas hoje, alguém me disse algo, o que vai totalmente contra o meu conhecimento de iniciante.

Deixe-me explicar. Vamos pegar o banco de dados SQL e criar uma tabela simples Personcom poucos registros dentro:

id | name   | age
-----------------
1  | Alex   | 24
2  | Brad   | 34
3  | Chris  | 29
4  | David  | 28
5  | Eric   | 18
6  | Fred   | 42
7  | Greg   | 65
8  | Hubert | 53
9  | Irvin  | 17
10 | John   | 19
11 | Karl   | 23

Agora, é a parte que eu gostaria de focar - idé o arquivo INDEX.

Até agora, pensei que funcionasse assim: quando uma tabela está sendo criada, o INDEXestá vazio. Quando estou adicionando um novo registro à minha tabela, INDEXele está sendo recalculado com base em alguns algoritmos. Por exemplo:

Agrupando um a um:

1    ... N
N+1  ... 2N
     ...
XN+1 ... (X+1)N

então, para o meu exemplo com size = 11 elementse N = 3ficará assim:

id | name   | age
-----------------
1  | Alex   | 24     // group0
2  | Brad   | 34     // group0
3  | Chris  | 29     // group0
4  | David  | 28     // group1
5  | Eric   | 18     // group1
6  | Fred   | 42     // group1
7  | Greg   | 65     // group2
8  | Hubert | 53     // group2
9  | Irvin  | 17     // group2
10 | John   | 19     // group3
11 | Karl   | 23     // group3

Então, quando estou usando query SELECT * FROM Person WHERE id = 8ele vai fazer alguns cálculos simples 8 / 3 = 2, então temos que procurar esse objeto group2e então essa linha será retornada:

8  | Hubert | 53

Essa abordagem funciona no tempo em O(k)que k << size. Claro, um algoritmo para organizar linhas em grupos é com certeza muito mais complicado, mas acho que este exemplo simples mostra meu ponto de vista.

Agora, gostaria de apresentar outra abordagem, que me foi mostrada hoje.

Vamos pegar mais uma vez esta tabela:

id | name   | age
-----------------
1  | Alex   | 24
2  | Brad   | 34
3  | Chris  | 29
4  | David  | 28
5  | Eric   | 18
6  | Fred   | 42
7  | Greg   | 65
8  | Hubert | 53
9  | Irvin  | 17
10 | John   | 19
11 | Karl   | 23

Agora, estamos criando algo semelhante a Hashmap(na verdade, literalmente é um mapa de hash) que mapeia idpara addressuma linha com esse id. Digamos:

id | addr 
---------
1  | @0001
2  | @0010
3  | @0011
4  | @0100
5  | @0101
6  | @0110
7  | @0111
8  | @1000
9  | @1001
10 | @1010
11 | @1011

Então agora, quando estou executando minha consulta:SELECT * FROM Person WHERE id = 8

ele mapeará diretamente id = 8para o endereço na memória e a linha será retornada. Claro que a complexidade disso é O(1).

Então agora, eu tenho algumas perguntas.

1. Quais são as vantagens e desvantagens de ambas as soluções?

2. Qual deles é mais popular nas implementações de banco de dados atuais? Talvez dbs diferentes usem abordagens diferentes?

3. Ele existe em bancos de dados não SQL?

Agradeço antecipadamente

COMPARAÇÃO

               |      B-tree     |   Hash Table
----------------------------------------------------
----------------   one element   -------------------
----------------------------------------------------
SEARCHING      |  O(log(N))      | O(1) -> O(N)  
DELETING       |  O(log(N))      | O(1) -> O(N)
INSERTING      |  O(log(N))      | O(1) -> O(N)
SPACE          |  O(N)           | O(N)
----------------------------------------------------
----------------    k elements   -------------------
----------------------------------------------------
SEARCHING      |  k + O(log(N))  | k * O(1) -> k * O(N)
DELETING       |  k + O(log(N))  | k * O(1) -> k * O(N)
INSERTING      |  k + O(log(N))  | k * O(1) -> k * O(N)
SPACE          |  O(N)           | O(N)

N - número de registros

Estou certo? E quanto ao custo de reconstruir a árvore B e a tabela Hash após cada inserção/exclusão ? No caso da árvore B , temos que alterar alguns ponteiros, mas no caso da árvore B balanceada, é necessário mais esforço. Também no caso da tabela Hash , temos que fazer poucas operações, especialmente, se nossa operação gerar conflitos .