Gostaria de saber em que ponto faz sentido reconstruir o índice BTree em tabelas relativamente grandes (algumas centenas de GB).
Para mim, qualquer coisa acima de 30% ou 40% parece ser um bom candidato, mas vi alguns dizerem que até 70% não é tão ruim para o Postgres. Estou obtendo fragmentação da pgstattuple.pgstatindexfunção, se for importante.
Eu me pergunto por que há uma diferença no custo do plano de execução para as 2 consultas:
create table test_insert(id int not null primary key, value varchar2(10));
--1
insert into test_insert(id,value)
values (2,'111');
--2
insert into test_insert(id,value)
select 3,'111' from dual where not exists (select null from test_insert where id =3);
O custo da consulta 2 é sempre maior (quanto depende do número de linhas na tabela). No meu entendimento not exits ..., não deve adicionar nenhuma sobrecarga - para impor a restrição de PK, o mecanismo deve verificar o índice exclusivo correspondente de qualquer maneira, portanto, a subconsulta adiciona tempo de análise extra, mas não tempo de execução ou etapa extra ao plano.
O exemplo é para Oracle, mas verifiquei o Postgres também, os resultados são semelhantes.
Por exemplo,
--1
| Id | Operation | Name | Cost |
--------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 |
| 1 | LOAD TABLE CONVENTIONAL | TEST_INSERT | |
--------------------------------------------------------
--2
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 3 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | TEST_INSERT | | | | |
|* 2 | FILTER | | | | | |
| 3 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C0012345 | 1 | 13 | 1 (0)| 00:00:01 |
Gostaria de saber se é possível renomear a tabela Postgres sem alterar o tipo em cascata para objetos dependentes? Por exemplo,
create table test_1(id int);
create function test_1_funct (param test_1[]) returns void as $body$
begin
return;
end
$body$
language plpgsql;
alter table test_1 rename to test_2 ;
-- function test_1_funct signature silently changed,
-- so it now accepts test_2[].
Eu quero emular um comportamento semelhante ao que o Oracle faria - marque a função inválida, deixe-me criar uma tabela com o nome antigo e recompilar a função sob demanda. Do jeito que está, não me deixa drop table test_2.
Um dos bancos de dados físicos em espera em meu ambiente está aberto no modo somente leitura e usado para fins de geração de relatórios.
Quero dar aos usuários acesso apenas a esse modo de espera específico, não ao banco de dados principal.
A melhor abordagem que encontrei até agora é criar um AFTER LOGON ON DATABASEgatilho que verifica v$instance(ou talvez v$database) e lança um erro para depender dos valores de host_name/ instance_rolecolunas .
Eu me pergunto se há uma maneira melhor de fazer isso.
É possível imitar o Oracle SET AUTORECOVERY ONno SQL Server 2008 para aplicar logs de transações?
RESTORE LOG [DB_NAME] FROM DISKrequer nome de backup; caso eu tenha algumas centenas de logs, devo garantir que os logs sejam restaurados na ordem correta. Certamente, posso classificar arquivos por nome, mas me pergunto se talvez haja uma solução pronta para uso.
Obrigada.
Por exemplo (apenas para ilustrar o problema),
CREATE TABLE test_join(ID INT NOT NULL);
--1
SELECT ID FROM
test_join A
INNER JOIN test_join b ON 1=1;
--2
SELECT ID FROM
test_join A
INNER JOIN test_join b ON 1=1
INNER JOIN test_join c ON 1=1;
O primeiro dá o esperado "ORA-00918: coluna definida de forma ambígua". O segundo executa sem reclamações (Oracle 10g). Eu me pergunto se é um bug ou se estou perdendo algo óbvio nas regras de resolução de nomes. Oracle 11 dá erro em ambos os casos.
obrigada
Acontece que tive que trabalhar com SQL Server e Oracle por um bom tempo (felizmente não ao mesmo tempo).
O que ainda me intriga é a abordagem de armazenar tabelas como árvores balanceadas. No heap RDMS semelhante ao Oracle é padrão, no SQL Server (e muitos outros) o inverso (clusterizado, IOT) é verdadeiro. Adeptos de cada abordagem afirmam que seu caminho é o único 'correto' e apoiam o ponto de vista escolhido com vários testes/apresentações. No entanto, na minha opinião, o único ponto que eles provaram é que a implementação da abordagem "não padrão" é ruim e não deve ser usada na maioria dos casos ...
Tenho certeza de que ambas as abordagens são boas o suficiente (apenas porque ainda existem no mercado e mostram desempenho comparável) e têm um pouco de matemática por baixo, mas não consegui encontrar boas referências.
Sei que o tópico pode ser muito amplo para responder, e bons links são muito bem-vindos, mas realmente quero saber por que duas abordagens aparentemente controversas provaram que são válidas.
Estou movendo o banco de dados do Oracle para o SQLServer (2012, se isso importa). O processo de migração de tabelas/views é mais ou menos direto e automatizado. Os gatilhos também não são um problema, pois não tenho muitos deles.
No entanto, gostaria de saber qual é a melhor prática para mover pacotes Oracle. Tanto quanto eu sei, SQLServer não tem nada parecido. Minha ideia é mapear cada pacote para o esquema SQLServer (assim, por exemplo, terei esquemas 'package1' , 'package2', etc no SQLServer, cada um deles não possui tabelas, apenas funções/procedimentos/tipos). Acredito que isso preservará o propósito original dos pacotes e, ao mesmo tempo, reduzirá o número de alterações necessárias no lado do aplicativo.
Faz sentido ou estou no caminho errado?
Obrigada.
Eu tive um problema de impasse que foi fácil de corrigir alterando a lógica do aplicativo, mas tive a impressão de que o Oracle se comportaria de maneira diferente e o impasse nunca aconteceria. Eu tenho duas tabelas, por exemplo
table1( table1_id (PK), num1, num2);
table2(table2_id(PK), table1_id(FK to table1,indexed), low_cardinality_column, num3, num4);
Table2 também possui um after updategatilho de nível de linha que atualiza table1.num( table1.num1 = table1.num1 + :NEW.num3 where table1.table1_id = :NEW.table1_id).
O primeiro processo é executado UPDATE table2 set num3 =1 where low_cardinality_column =:bind_var(sem índice em low_cardinality_column, normalmente alguns milhares de registros afetados).
O segundo processo atualiza table2 e table1 em uma transação com
UPDATE table2
SET num4 = :bind_var4
WHERE table2_id = :bind_var_id
RETURNING table1_id INTO :out_var
UPDATE table1
SET num2 = :new_num2_val
WHERE table1_id = :out_var
O rastreamento mostrou alguns impasses entre esses 2 processos, se eles forem executados ao mesmo tempo, e estou um pouco confuso com isso. Entendo que impasses devem ter ocorrido no caso de o segundo processo atualizar as tabelas na ordem inversa ( table1, então table2), mas, neste caso específico, pensei que o mecanismo não iniciaria a atualização até obter o bloqueio RX em cada registro que deve ser atualizado (portanto um processo esperaria que outro terminasse). Se não for garantido, a explicação do impasse é óbvia: o Processo 1 bloqueia os registros em ordem indefinida e o Processo 2 atualizou a linha table2ainda não bloqueada e tenta atualizar a linha table1já atualizada pelo Processo 1. No caso do SQLServer I teria 100% de certeza de que é esse o caso, mas ainda sou muito novo no Oracle ...
Gostaria de saber se alguém poderia esclarecer a questão. Obrigada. Eu uso 10g se isso importa.
Supondo que eu tenha o seguinte procedimento
CREATE PROCEDURE foo (table1_id IN TABLE1.table1_id%type,
table1_val IN TABLE1.table1_value%type)
AS
SQL_UPDATE VARCHAR2(500) := 'UPDATE TABLE1 SET table1_value =:1 WHERE table1_id = :2';
BEGIN
--.....
--1 :
EXECUTE IMMEDIATE SQL_UPDATE USING foo.table1_val, foo.table1_id;
--2 :
UPDATE TABLE1 SET table1_value = foo.table1_val WHERE table1_id = foo.table1_id;
END;
Além do estilo/legibilidade, há alguma penalidade de desempenho para usar a consulta dinâmica (1) em comparação com (2) nesses casos (quero dizer, quando é absolutamente evitável)?
Obrigada.
Uma coluna da minha tabela armazena uma lista de valores separados por vírgula (sei que é um design ruim, mas a refatoração não é uma opção no momento). Parece que encontrei uma solução funcional, mas receio que não seja muito eficiente:
select distinct test_id , regexp_substr(str_data,'[^,]+', 1, level)
from
(
select 1 as test_id, '1,2,3' as str_data from dual
union all
select 2 as test_id, '4,5,6' as str_data from dual
)test_data
connect
by
regexp_substr(str_data, '[^,]+', 1, level) is not null
Eu não gosto que eu tenha que usar distinct. Existe uma abordagem melhor ?
Obrigada.
Eu tenho um procedimento armazenado que leva coleção de objetos. ( TABLE OF MyCustomType). Dentro do procedimento estou tentando juntar esse parâmetro com tabelas reais.
Por exemplo, algo como
create or replace TYPE MYTYPE AS OBJECT (....);
CREATE OR REPLACE TYPE LIST_OF_MYTYPE AS TABLE OF MYTYPE;
CREATE PROCEDURE FOO(my_table LIST_OF_MYTYPE ,....) AS
CURSOR cur_read_data IS
SELECT a.col1, b.col2, b.col3
FROM
TABLE(FOO.my_table) a
INNER JOIN existing_table b ON (b.existing_table_id = a.existing_table_id)
--b.existing_table_id - primary key supported by unique index
ORDER BY a.existing_table_id
;
BEGIN
FOR record_info in cur_read_data
LOOP
......
END LOOP;
END;
Funciona, mas tenho um problema de desempenho. O parâmetro de coleção passado para o procedimento não possui muitos elementos; mesmo que tenha apenas um elemento, o plano de execução envolve a varredura completa da tabela de existing_table.
No caso de 1 elemento, mudar INNER JOIN existing_table b ON (b.existing_table_id = a.existing_table_id)para INNER JOIN existing_table b ON (b.existing_table_id =FOO.my_table(1).existing_table_id )faz uma grande diferença - a consulta usa "INDEX UNIQUE SCAN" como eu esperava para a consulta inicial. Eu tentei até dicas de consulta (ordenadas, iniciais) sem resultados ...
Mesmo que o número de elementos na coleção em meu aplicativo esteja entre 1 e 5 e seja possível escrever 5 versões diferentes de um procedimento, gostaria de saber se é possível fazê-lo funcionar conforme o esperado.
Para testar também fiz
CURSOR cur_read_data IS
SELECT a.col1, b.col2, b.col3
FROM
(
SELECT 1 as existing_table_id, 'test 1' as col1 FROM DUAL
UNION
SELECT 2 as existing_table_id, 'test 2' as col1 FROM DUAL
UNION
SELECT 3 as existing_table_id, 'test 3' as col1 FROM DUAL
)
a
INNER JOIN existing_table b ON (b.existing_table_id = a.existing_table_id)
--b.existing_table_id - primary key supported by unique index
ORDER BY a.existing_table_id
Dessa forma também funciona como esperado, INDEX_UNIQUE_SCAN, não full scan...
Obrigado por suas respostas.
ATUALIZAÇÃO Surpreendentemente, mas reescrevendo-o para
CURSOR cur_read_data IS
WITH CTE1 AS (SELECT * FROM TABLE(FOO.my_table))
SELECT a.col1, b.col2, b.col3
FROM
CTE1 a
INNER JOIN existing_table b ON (b.existing_table_id = a.existing_table_id
AND b.existing_table_id IN (SELECT existing_table_id FROM CTE1))
ORDER BY a.existing_table_id
custo reduzido cerca de 30 vezes (1200 versão original vs 38 com WITH) o que é aceitável, mas ainda não tenho ideia de por que isso ajudou ...
outra atualização
A análise V$SQLSTATSrevela que para a primeira versão do cursor ele evita DISK READS a qualquer custo (0), DIRECT WRITES também é 0; usar de WITHalguma forma altera o plano de execução, resultando em grande melhoria no TEMPO da CPU, que supera o aumento de LEITURAS DE DISCO (1) ...
Gostaria de saber qual é a melhor prática para recompilar objetos programáveis do Oracle 10g que se tornam inválidos devido a alterações em um deles.
Para ser mais específico, tenho alguns pacotes que usam tipos de nível de esquema. Também tenho gatilhos no sistema que chamam funções/procedimentos de pacotes. Pelo que entendi, alterações no corpo do pacote não requerem nenhuma ação adicional (exceto a recompilação do corpo). No entanto, se eu alterar package , tenho que recompilar vários gatilhos que usam este pacote e outros pacotes que dependem dele.
Até agora, apenas peguei a lista de objetos inválidos e os recompilei ou usei DBMS_UTILITY.compile_schema.
Obrigada.
Eu tenho a seguinte tabela:
CREATE TABLE action (action_id INT NOT NULL,
action_type_id INT NOT NULL,
action_date DATE NOT NULL);
CREATE INDEX IDX_ACTION_ACT_TYPE_ACT ON action(action_id, action_type_id);
ALTER TABLE action ADD CONSTRAINT PK_ACTION PRIMARY KEY (action_id)
USING INDEX IDX_ACTION_ACT_TYPE_ACT;
ALTER TABLE action ADD CONSTRAINT UQ_ACTION UNIQUE (action_id,action_type_id)
USING INDEX IDX_ACTION_ACT_TYPE_ACT;
-- I think the line below is irrelevant in the context of my question, but
-- in case I'm wrong...
ALTER TABLE action ADD CONSTRAINT FK_action_type_id FOREIGN KEY(action_type_id)
REFERENCES action_type(action_type_id);
-- Since action_type is a lookup added for normalization sake with no updates/deletes,
-- I don't see any value in having an index on FK column.
É uma tabela pai comum para muitas tabelas de detalhes que armazenam informações extras para uma ação específica. action_type_idfaz parte da restrição exclusiva para permitir que as tabelas de detalhes tenham FK para (action_id,action_type_id) e para impor que cada tabela de detalhes armazene apenas as informações que deveria armazenar.
Gostaria de saber se há alguma penalidade de desempenho por usar o mesmo índice em 2 restrições em comparação com a criação de 2 índices ... Na minha opinião, deveria se comportar melhor do que 2 índices, mas talvez esteja faltando alguma coisa. Estou usando o Oracle 10g, se isso importa.
Obrigado por suas respostas.
Digamos que eu tenha a seguinte tabela:
CREATE TABLE test(test_id int not null identity primary key,
field1 int not null,
field2 int
);
CREATE INDEX IDX_test_field1 ON test(field1);
CREATE INDEX IDX_test_field2 ON test(field2);
Agora ALTER TABLE test ALTER COLUMN field1 intfunciona e field1permite null.
No entanto, não posso mudar ALTER TABLE test ALTER COLUMN field2 int not nullpor causa de
ALTER TABLE ALTER COLUMN field2 falhou porque um ou mais objetos acessam esta coluna.
Além disso, não posso mudar field1de volta para not null.
No entanto, posso adicionar e descartar a restrição de verificação quantas vezes quiser:
ALTER TABLE test ADD CONSTRAINT CHK_NN_field2 CHECK (field2 IS NOT NULL);
DROP CONSTRAINT CHK_NN_field2` without any problems.
É um comportamento bem definido? Alguém poderia explicar por que isso acontece ou me indicar a documentação?
Estou usando o SQL Server 2008 R2, se isso for importante.
Obrigada.
Estou perdendo algo ao tentar fazer meu procedimento armazenado usar arquivos EXECUTE AS. O procedimento armazenado está lendo dados de source_db, agrega-os e armazena o resultado em target_db.
O sp em si está em target_db. Eu tenho um login dedicado e o mapeio para usuários em ambos source_dbe target_dbpara o proprietário do sp (portanto, há um usuário app_agentin source_dbe in target_dbpara login app_agent).
Se eu fizer login como app_agent, e executar
EXEC target_db.app_agent_schema.import_data
tudo funciona bem. Mas se eu mudar
ALTER PROCEDURE app_agent_schema.import_data WITH EXECUTE AS OWNER` (or `AS SELF`)
e tente executá-lo, ele lança
O servidor principal "app_agent" não pode acessar o banco de dados "source_db" no contexto de segurança atual.
Estou usando o SQLServer 2008.
Alguém poderia apontar meu erro?
Obrigado
Atualização
Depois de fazer algumas pesquisas, descobri que ALTER DATABASE target_db SET TRUSTWORTHY ONresolve o problema, mas não me parece a solução certa...
Não consigo descobrir por que o seguinte não está funcionando como eu esperava:
CREATE TABLE table1(table1_id INT NOT NULL PRIMARY KEY,
col1 INT NOT NULL,
val1 VARCHAR(50));
CREATE INDEX IDX_table1_fnct ON table1(CASE WHEN col1 = 3 THEN val1 ELSE NULL END);
SELECT COUNT(val1) WHERE col1 = 3 ; shows full scan
Eu esperaria que um novo índice fosse usado para tais consultas, mas não é mesmo se eu tentar forçá-lo com uma INDEXdica ... Alguém poderia esclarecer por que funciona dessa maneira (estou usando o Oracle 10, se isso importa)?
obrigada
Gostaria de saber se a ordem em que os registros são bloqueados é garantida quando uso SELECT ... FOR UPDATEem cursores ? Por exemplo,
DECLARE
CURSOR test_cursor IS
SELECT field1, field2
FROM table1
ORDER BY pk_field
FOR UPDATE OF field1;
Quando abro esse cursor, ele sempre bloqueia os registros na ordem de pk_field?
Pelo que li até agora, a ordem de bloqueio não é garantida para regular SELECT ... ORDER BY pk_field FOR UPDATE, mas não tenho certeza se é verdade para cursores. Eu esperaria que ele travasse na ordem definida na ORDER BYcláusula para cursores; no entanto, não consigo encontrar confirmação.
Obrigada.
Para uma consulta que retorna se há alguma transação ativa na sessão atual,
SELECT COUNT(*)
FROM
v$transaction t
INNER JOIN v$session s ON (t.ses_addr = s.saddr )
INNER JOIN v$mystat m ON (s.sid = m.sid )
WHERE ROWNUM = 1;
EXPLAINmostra custo 0. No entanto, isso não parece ser verdade. A execução periódica dessa consulta em um ambiente de alta carga faz com que o servidor desperdice quase todos os seus recursos.
Qual é a maneira certa de estimar o impacto de tais consultas (acredito que o mesmo problema ocorre não apenas para esta consulta específica, mas para qualquer coisa que envolva visualizações do sistema)?
Obrigado.
Peço desculpas se o título da pergunta é vago, mas não consegui formulá-lo melhor. O que eu gostaria de ter é uma alternativa ao TSQL CREATE TYPE, para poder usá-lo na definição de coluna.
Por exemplo, preciso do tipo "dinheiro" no Oracle, que está tecnicamente NUMBER(18,4)em meu sistema, e cada tabela terá a coluna "dinheiro" em vez de subjacente NUMBERpara fins de consistência. Existem alguns outros "tipos" que eu gostaria de ter aliases em vez de usá-los diretamente.
Infelizmente, ainda não encontrei uma solução aceitável, exceto desistir e usar tipos padrão com restrições (ou especificação de tamanho) para cada coluna. A única abordagem que parece funcionar é criar OBJECTtipo, mas adicionará complexidade desnecessária ao sistema que estou tentando evitar.
Um recurso muito bom do Oracle SUBTYPE, não funciona neste caso. Tanto quanto eu entendo, SUBTYPE(e subtipos de STANDARD) cria tipo de dados PL/SQL que não pode ser usado em SQL. Eu entendo que há várias razões para isso.
Eu me pergunto qual é a melhor prática para tal problema.
Obrigada.
Atualização
Parece até agora que listei todas as abordagens possíveis e prefiro encerrar a questão. Não tenho certeza se pode ser útil para alguém; caso seja, não retiro a pergunta por enquanto.