Joishi Bodio's questions

Eu tenho uma tabela de histórico que possui uma coluna 'ID' e 'TIMESTAMP' como tal

CREATE TABLE hist (
  HIST_ID INTEGER,
  HIST_TIMESTAMP TIMESTAMP,
  ID INTEGER, -- this is the id of the table that is being tracked
  --OTHER COLS
);

Eu também tenho um índice nesta tabela como tal

CREATE INDEX hist_ix ON hist (ID, HIST_TIMESTAMP);

Esta tabela tem muitas inserções contra ela e atualmente tem cerca de 30 milhões de linhas nela.

Quando tento executar a seguinte consulta, o oracle faz uma varredura completa da tabela em vez de usar o índice (que .. pelo menos eu acredito .. ele deveria ser capaz de usar).

SELECT ID, MAX(HIST_TIMESTAMP) FROM hist WHERE HIST_TIMESTAMP <= <<A TIMESTAMP>> GROUP BY ID;

Parece-me que o Oracle deve ser capaz de usar o índice para identificar rapidamente qual par id/timestamp está logo à "esquerda" de um ponto específico no tempo rapidamente, observando o índice id/timestamp em um id-by-id base, mas está insistindo em uma varredura completa da tabela.

Qualquer ajuda seria apreciada para obter esta consulta mais rápida.

Eu executei o seguinte para garantir que as estatísticas estavam atualizadas

EXEC DBMS_STATS.GATHER_TABLE_STATS('<meh>','hist');

Além disso, existem cerca de 1.000 valores de ID distintos na tabela hist.

Com relação à distribuição de dados. Dos ~1k IDs, 50 têm menos de 100 entradas na tabela, 70 têm entre 100 e 1.000 entradas, 146 têm entre 1.000 e 10.000 entradas e o restante varia de 10.000 a 60.000 entradas. Mais da metade das entradas tem pelo menos 30 mil registros.

Eu tenho uma accountstabela com aproximadamente 200k linhas e um índice para essas colunas:

  account_type_id BIGINT
, member_id BIGINT
, external_id VARCHAR(64)

CREATE INDEX account_full_qualifiers_idx
ON normal_object.account (account_type_id, member_id, external_id) TABLESPACE index_tbsp;

E tenho uma função que está realizando algum trabalho de ETL com a seguinte consulta:

EXECUTE '
   SELECT * FROM normal_object.account
   WHERE account_type_id = $1
   AND member_id = $2
   AND external_id = $3'
INTO e_row
USING r_row.account_type_id, r_row.member_id, r_row.external_id;

No entanto, o EXECUTEcomando está NOTusando o índice e não sei por quê. Meu único palpite é que os tipos de dados não estão alinhados. No entanto , r_row.account_type_idé um BIGINT, r_row.member_idé um BIGINTe r_row.external_idé um VARCHAR(64).

Alguma sugestão de por que não está usando o índice?
Como posso obtê-lo para usar o índice? (Já tentei enable_seqscanpartir.)

Sou o único DBA em nossa empresa e sou relativamente novo na profissão (tendo agora 2,5 anos de experiência). Os desenvolvedores e eu estamos discutindo sobre como projetar adequadamente o banco de dados para um novo software que está sendo desenvolvido. Eles desenvolvem seus aplicativos em módulos e acreditam que ter uma tabela de status privada para cada módulo é melhor, enquanto acredito que ter uma tabela para conter todos os estados gerados internamente (e possivelmente tabelas adicionais para dar algum significado a esses estados) seria melhor. O código de amostra abaixo está escrito em postgresql, pois é o que usamos.

Minha abordagem:

CREATE TABLE status (
  status_id SERIAL PRIMARY KEY,
  status_code VARCHAR(3) UNIQUE NOT NULL, -- short description
  status_description VARCHAR(50) UNIQUE NOT NULL -- long description
);

CREATE TABLE member_status (
  status_id INT PRIMARY KEY REFERENCES status (status_id)
);

A abordagem deles:

CREATE TABLE member_status (
  status_id SERIAL PRIMARY KEY,
  status_code VARCHAR(3) UNIQUE NOT NULL, -- short description
  status_description VARCHAR(50) UNIQUE NOT NULL -- long description
);
-- rinse and repeat for every object that may need a status

Eles só veem "problemas" com minha abordagem (que ainda precisam ser claramente articulados) e, embora eu não veja problemas com a abordagem deles, acredito que não seja o melhor design.

Qualquer contribuição sobre qual design é melhor e PORQUÊ seria apreciada.

Estou escrevendo um trabalho para transformar dados de um design antigo em um novo design. Nesse processo, preciso pegar o id de uma inserção em uma tabela separada e usá-lo em uma inserção na tabela de destino, da seguinte forma:

CREATE TABLE t1 {
  t1_id BIGSERIAL,
  col1 VARCHAR
};
CREATE TABLE t2 {
  t2_id BIGSERIAL,
  col2 VARCHAR, -- renamed from col1 to avoid confusion
  t1_id BIGINT REFERENCES t1.t1_id
};

Eu tenho o SQL definido que corresponde ao seguinte formulário:

WITH ins AS (
  INSERT INTO t1 (t1_id) VALUES (DEFAULT) RETURNING t1_id
) INSERT INTO t2
  (col1, t1_id)
SELECT
  a.val1, (SELECT * FROM ins)
FROM t3 a;

Eu queria que isso fosse executado SELECT * FROM inspara cada linha do SELECT.. mas, em vez disso, ele o executa apenas uma vez e usa esse valor para todas as linhas do SELECT. Como posso reestruturar meu SQL para obter o comportamento desejado?

edit4

t1 acaba se parecendo com:

1,<NULL>
(1 row)

t2 acaba se parecendo com:

10,'a',1
11,'b',1 -- problem with id from t1 being 1
12,'c',1 -- problem with id from t1 being 1
.
.

O que eu quero que t1 pareça:

1,<NULL>
2,<NULL>
3,<NULL>
.
.

O que eu quero que t2 se pareça:

10,'a',1
11,'b',2 -- id from t1 of 2
12,'c',3 -- id from t1 of 3
.
.

editar Para abordar o que a_horse_with_no_name disse, eu também tentei isso (com o mesmo resultado):

WITH ins AS (
  INSERT INTO t1 (t1_id) VALUES (DEFAULT) RETURNING t1_id
) INSERT INTO t2
  (col1, t1_id)
SELECT
  a.val1, b.t1_id
FROM t3 a
JOIN ins b ON TRUE;

edit2 Acabei de tentar referenciar diretamente o apropriado SEQUENCEem minha consulta, e isso FUNCIONA - mas não gosto muito dessa solução (principalmente porque não gosto de nomes de objeto codificados). do que referenciar diretamente o nome do SEQUENCE, eu agradeceria. :)

edit3 Suponho que outra solução seria usar um PROCEDUREpara fazer o INSERTem vez de um CTE .. mas ainda apreciaria as opções/sugestões.

O seguinte é em relação ao PostgreSQL 9.1:

Estou usando as funções de string right () e left () para corrigir algumas instâncias em que os dados foram inseridos em meu banco de dados sendo "desligados por um" right(field1,1)||left(field2,-1. . No processo, encontrei um resultado inesperado em uma coluna char(2).

Se eu fizer isso me dá o retorno SELECT RIGHT('A ',1)esperado .' '

No entanto, se eu fizer SELECT RIGHT('A '::CHAR(2),1)isso, me dá 'A'.

Por que está fazendo isso? É um bug? Eu tentei SELECT RIGHT('A '::CHAR(3),1)e também dá 'A'como resultado.

Atualmente, estou criando algum SQL para ler os catálogos do postgres (9.1) para criar definições de tabela. No entanto, estou encontrando um problema com os tipos de dados SERIAL/BIGSERIAL.

Exemplo:

CREATE TABLE cruft.temp ( id BIGSERIAL PRIMARY KEY );
SELECT * FROM information_schema.columns WHERE table_schema='cruft' AND table_name='temp';
"db","cruft","temp","id",1,"nextval('cruft.temp_id_seq'::regclass)","NO","bigint",,,64,2,0,,,,,,,,,,,,,"db","pg_catalog","int8",,,,,"1","NO","NO",,,,,,,"NEVER",,"YES"

Ele me dá o nome do banco de dados (db), nome do esquema (cruft), nome da tabela (temp), nome da coluna (id), valor padrão (nextval( ... )) e tipo de dados (bigint e int8 .. NOT bigserial) ... Percebo que poderia apenas verificar se o valor padrão era uma sequência - mas não acredito que seria 100% preciso, pois poderia criar manualmente uma sequência e criar uma coluna não serial onde o valor padrão era aquela sequência.

Alguém tem uma sugestão de como posso fazer isso? Algo além de verificar o valor padrão de um nextval(*_seq)?

Editado para solução SQL adicionado aqui no caso de TL;DR ou novos usuários não familiarizados com o pg_catalog:

with sequences as (
  select oid, relname as sequencename from pg_class where relkind = 'S'
) select
  sch.nspname as schemaname, tab.relname as tablename, col.attname as columnname, col.attnum as columnnumber, seqs.sequencename
from pg_attribute col
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid and deps.deptype = 'n'
left join sequences seqs on deps.refobjid = seqs.oid
where sch.nspname != 'information_schema' and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
  and col.attnum > 0
  and seqs.sequencename is not null -- TO ONLY VIEW SERIAL/BIGSERIAL COLUMNS
order by sch.nspname, tab.relname, col.attnum;