User1974's questions

Qual é o caso de uso pretendido para colunas virtuais com um índice baseado em função?

A razão pela qual eu pergunto (novato):

Eu tenho explorado opções para pré-computar colunas em uma tabela. Por exemplo, em uma tabela de estradas, preencha um campo usando o número da zona de interseção espacial de uma tabela de zonas. Mais informações aqui: Opções para campos de computação.

Minha experiência é que as consultas espaciais geralmente são lentas — seja usando o tipo de dados/funções SDO_GEOMETRY da Oracle ou usando um tipo de objeto definido pelo usuário, como o tipo de dados/funções ST_GEOMETRY da Esri. Então, eu definitivamente quero pré-computar esse cálculo — para evitar fazer cálculos caros constantemente toda vez que a consulta for usada.

No início, fiquei tentado a criar uma coluna virtual com um índice baseado em função. Mas agora (com a entrada de outros), estou começando a me perguntar se pré-computar uma coluna usando um índice baseado em função pode não ser o uso correto para FBIs. Em vez disso, estou querendo saber se simplesmente calcular um campo na tabela usando um gatilho pode ser uma opção melhor. (Estou ciente de que as visualizações materializadas também são uma opção. Mas não tenho privilégios CREATE MATERIALIZED VIEW.)

Então, para encurtar a história, suspeito que uma coluna virtual e um FBI não sejam apropriados para o meu caso de uso. Com isso dito, qual é o caso de uso certo para uma coluna virtual e um FBI?

Estou tentando aprender sobre índices no Oracle 18c.

Como iniciante, ocorreu-me que pode ser interessante visualizar os dados reais que estão em um índice baseado em função:

create index sidewalk_endpoint_list_idx on my_owner.sidewalks(my_owner.endpoint_list(shape),objectid); 

Existe uma maneira de SELECIONAR os dados de um índice em uma consulta?

Exemplo falso:

select
    *
from
    sidewalk_endpoint_list_idx

Essa pode ser uma pergunta boba. Talvez os índices não sejam armazenados em tabelas. Mas achei que valia a pena perguntar, só para aprender.

Eu tenho uma string que tem coordenadas.

• As coordenadas individuais são separadas por espaços.
• Os vértices (coordenadas XYZ) são separados por vírgulas.
• Os grupos de vértices são colocados entre colchetes e separados por vírgulas.

Before:
MULTILINESTRING M (( 0.0 5.0 123, 10.0 10.0 456, 30.0 0.0 789),( 50.0 10.0 -123, 60.0 10.0 -100000.0))

Eu quero substituir a terceira coordenada em cada vértice por um novo número:

After:
MULTILINESTRING M (( 0.0 5.0 1, 10.0 10.0 1, 30.0 0.0 1),( 50.0 10.0 1, 60.0 10.0 1))

Para simplificar, podemos usar o número 1como o número de substituição.

Qual é uma boa maneira de substituir esses números nessa string?

Eu tenho uma consulta que tem uma função inline:

with function with_f(p_text in varchar2) return varchar2 is
  begin
    return p_text;
  end;
select with_f(dummy) from dual

Existe uma maneira de obter o plano de explicação para essa consulta no SQL Developer?

Eu tentei isso:

explain plan for (
with function with_f(p_text in varchar2) return varchar2 is
  begin
    return p_text;
  end;
select with_f(dummy) from dual
); 
select plan_table_output from table(dbms_xplan.display());

Mas recebo o seguinte erro:

SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"

Quero sugerir algumas ideias para a Oracle sobre melhorias no SDO_GEOMETRY / Oracle Spatial.

Exemplo:

Solicitando uma função para selecionar vértices, incluindo os números de várias partes de polilinhas SDO_GEOMETRY.

Pergunta:

Temos uma maneira de solicitar melhorias à Oracle para SDO_GEOMETRY / Oracle Spatial?

O que eu tentei:

Encontrei a seguinte página: Como registrar uma ideia (solicitação de aprimoramento) no Laboratório de ideias do Customer Connect para produtos de aquisição (ID do documento 2577625.1)

Mas, infelizmente, minha conta pessoal da Oracle não está “conectada” a uma conta do Oracle Support da minha organização (não sou da área de TI; e meu DBA não quer enviar ideias em meu nome). Portanto, não consigo passar da etapa "Conectar sua conta de usuário".

Em uma pergunta relacionada , descobriu-se que havia uma maneira de solicitar melhorias para um produto diferente (SQL Developer) diretamente no fórum da Oracle . O que é ótimo, porque é acessível a não-DBAs como eu.

Então eu queria saber se algo semelhante pode existir para o Oracle Spatial.

E não tenho certeza se a página "Como registrar uma ideia" é o caminho certo de qualquer maneira. Parece que as ideias podem não ser ouvidas pela equipe adequada... talvez haja uma maneira mais direta de falar com a equipe do Oracle Spatial.

Sobre a ferramenta SQL Developer da Oracle:

Existe uma maneira de solicitar que a Oracle faça um aprimoramento na ferramenta?

Por exemplo, quero pedir ao Oracle para adicionar esta funcionalidade: Gerar cláusula WITH do conjunto de resultados usando o SQL Developer

No Oracle 21c, parece que podemos retornar um conjunto de resultados como texto JSON:

SELECT json_object(*)
from dual;

result:
{"DUMMY":"X"}

Isso funciona em db<>fiddle :

Pergunta:

Existe uma maneira de retornar o texto JSON usando uma formatação bonita?

{
   "DUMMY":"X"
}

Relacionado: Selecione o texto JSON de SDO_GEOMETRY usando SQL

Estou tentando criar uma visão com função inline em db<>fiddle.

Eu removi a parte CREATE VIEW por enquanto e estou apenas tentando executar a consulta, apenas para chegar ao problema raiz.

select  /*+ WITH_PLSQL */ calc from
(
with
function calculator (m number, r number) return number
is begin
return m * r;
end calculator;
select calculator(3, 2) as calc from dual
);
/

Fonte: função embutida em uma subconsulta de seleção ou visualização de criação

Quando executo esse código em db<>fiddle , recebo um erro:

ORA-00905: missing keyword

O que estou fazendo errado?

Eu tenho uma consulta em um campo Varchar2 em uma tabela Oracle:

select column from tbl where column like 'value%'

Essa consulta usa um curinga à direita.

Pergunta:

O Oracle é capaz de usar um índice nessa coluna quando um curinga à direita é usado?

Eu fiz uma versão personalizada da função GetVertices() do Oracle. A solução envolve a criação de um tipo definido pelo usuário e uma função.

Aqui está o código (funcionando):

CREATE TYPE vertex_type_cust AS object
(
  x  NUMBER,
  y  NUMBER,
  z  NUMBER,
  w  NUMBER,
  id NUMBER );

CREATE TYPE vertex_set_type_cust AS TABLE OF vertex_type_cust;

CREATE OR replace FUNCTION getvertices_cust(geometry mdsys.sdo_geometry)
  RETURN vertex_set_type_cust
IS
  i      NUMBER;
  dims   NUMBER;
  coords NUMBER;
  result vertex_set_type_cust;
  dim mdsys.sdo_dim_array;
  is_zero BOOLEAN;
  etype   NUMBER;
BEGIN
  result := vertex_set_type_cust();
  -- handle the POINT case here
  IF (geometry.sdo_ordinates IS NULL) THEN
    result.extend;
    result(1) := vertex_type_cust(geometry.sdo_point.x, geometry.sdo_point.y, geometry.sdo_point.z,NULL,1);
    RETURN result;
  END IF;
  -- all other cases here
  coords := geometry.sdo_ordinates.count;
  dims := geometry.get_dims;
  IF (dims = 0) THEN
    RETURN result;
  END IF;
  coords := coords/dims;
  FOR i          IN 0 .. coords-1
  LOOP
    result.extend;
    IF (dims = 2) THEN
      result(i+1) := vertex_type_cust(geometry.sdo_ordinates(2*i+1), geometry.sdo_ordinates(2*i+2), NULL,NULL,i+1);
    ELSIF (dims = 3) THEN
      result(i+1) := vertex_type_cust(geometry.sdo_ordinates(3*i+1), geometry.sdo_ordinates(3*i+2), geometry.sdo_ordinates(3*i+3) ,NULL,i+1);
    ELSIF (dims = 4) THEN
      result(i+1) := vertex_type_cust(geometry.sdo_ordinates(4*i+1), geometry.sdo_ordinates(4*i+2), geometry.sdo_ordinates(4*i+3), geometry.sdo_ordinates(4*i+4), i+1);
    END IF;
  END LOOP;
  RETURN result;
END;

Dados de teste:

create table a_sdo_geometry_tbl (line_id integer, shape mdsys.sdo_geometry);

insert into a_sdo_geometry_tbl (line_id, shape) 
values (1, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
    sdo_ordinate_array (671539.6852734378,4863324.181436138, 671595.0500703361,4863343.166556185, 671614.013553706,4863350.343483042, 671622.2044153381,4863353.525396131))  );

insert into a_sdo_geometry_tbl (line_id, shape) 
values (2, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
    sdo_ordinate_array (71534.5567096211,4863119.991809748, 671640.7384688659,4863157.132745253, 671684.8621150404,4863172.022995591))  );

insert into a_sdo_geometry_tbl (line_id, shape) 
values (3, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
    sdo_ordinate_array (671622.2044153381,4863353.525396131, 671633.3267164109,4863357.846229106, 671904.0614077691,4863451.286166754))  );

insert into a_sdo_geometry_tbl (line_id, shape) 
values (4, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
    sdo_ordinate_array (671684.8620521119,4863172.022995591, 671892.1496144319,4863244.141440067, 671951.2156571196,4863264.824310392, 671957.4471461186,4863266.847617676, 671966.8243856924,4863269.146632658))  )

select
    a.line_id,
    b.id as vertex_id,
    b.x, 
    b.y
from
    a_sdo_geometry_tbl a
cross join
    table(getvertices_cust(a.shape)) b            --<<-- the query uses the custom function
order by 
    a.line_id, b.id;

Problema:

Estou testando esta solução com ambientes Oracle online porque não tenho privilégios CREATE TYPE no banco de dados Oracle da minha empresa.

Consigo fazer o código acima funcionar quando o executo no ambiente de teste gratuito da Oracle (19c):

• https://livesql.oracle.com/
• Criar função: captura de tela
• Consulta usando a função: screenshot

No entanto, quando tento executar o código com db<>fiddle , recebo um erro ao criar a função:

• db<>sessão de violino (18c)
• ORA-24344: success with compilation error

Pergunta:

Como posso evitar esse erro ao criar a função personalizada com db<>fiddle?

Eu sei que há uma diferença de versão entre Oracle Live e db<>fiddle (19c vs. 18c). Mas eu ficaria surpreso se esse fosse o problema. Isso não me parece um problema de versão.

Eu sou um novato tentando aprender sobre otimização de consultas e tabelas temporárias no Oracle.

Os exemplos que vi para tabelas temporárias do Oracle envolvem instruções CREATE TABLE e INSERT INTO.

CREATE PRIVATE TEMPORARY TABLE ora$ppt_temp1(
    id INT,
    description VARCHAR2(100)
) ON COMMIT DROP DEFINITION;

INSERT INTO ora$ppt_temp1(id,description)
VALUES(1,'Transaction-specific private temp table');

No meu caso (IBM Maximo), minhas consultas são instruções SELECT puras (visualizações, etc.), então não posso incluir outras instruções como CREATE TABLE ou INSERT INTO.

Mas me deparei com um blog sobre SQL Performance que descreve algumas funcionalidades relacionadas no PostgreSQL que parecem úteis:

O PostgreSQL 12+ automaticamente materializa (aloca a saída na memória) CTEs que são chamadas mais de uma vez.

Para meus olhos destreinados, a ideia de materialização automática parece atraente. Isso poderia me ajudar a evitar executar CTEs várias vezes (desnecessariamente).

Existe uma maneira de fazer esse tipo de coisa no Oracle? (evitando a necessidade de instruções CREATE TABLE e INSERT INTO)

Eu sou um novato, tentando encontrar a melhor maneira de resumir meus dados. Oráculo 19c.

Eu tenho uma tabela WORKORDER que tem ordens de serviço (ISTASK=0) e tarefas (ISTASK=1).

• As tarefas são filhas de ordens de serviço.
• Ordens de serviço e tarefas são agrupadas por WOGROUP.

Os custos são divididos em quatro colunas:

• actlabcost(custo real de mão de obra)
• actmatcost(custo real do material)
• acttoolcost(custo real da ferramenta)
• actservcost(custo real do serviço)

As colunas de custo não são anuláveis. Portanto, não precisamos nos preocupar em converter nulos em zeros para evitar fazer contas com nulos.

Select 'WO1361' as WONUM, 'WO1361' as WOGROUP, 0 as ISTASK, 0 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167457977' as OTHER_WO_COLUMNS FROM DUAL
UNION ALL
Select 'WO1362' as WONUM, 'WO1362' as WOGROUP, 0 as ISTASK, 0 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167458280' as OTHER_WO_COLUMNS FROM DUAL
UNION ALL
Select 'WO1363' as WONUM, 'WO1363' as WOGROUP, 0 as ISTASK, 270.14 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167483430' as OTHER_WO_COLUMNS FROM DUAL
UNION ALL
Select 'WO1367' as WONUM, 'WO1363' as WOGROUP, 1 as ISTASK, 540.27 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167482806' as OTHER_WO_COLUMNS FROM DUAL
UNION ALL
Select 'WO1370' as WONUM, 'WO1363' as WOGROUP, 1 as ISTASK, 202.6 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167483431' as OTHER_WO_COLUMNS FROM DUAL
UNION ALL
Select 'WO1364' as WONUM, 'WO1364' as WOGROUP, 0 as ISTASK, 88.86 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167459454' as OTHER_WO_COLUMNS FROM DUAL
UNION ALL
Select 'WO1366' as WONUM, 'WO1364' as WOGROUP, 1 as ISTASK, 33.77 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167458946' as OTHER_WO_COLUMNS FROM DUAL
UNION ALL
Select 'WO1365' as WONUM, 'WO1365' as WOGROUP, 0 as ISTASK, 67.53 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167459331' as OTHER_WO_COLUMNS FROM DUAL
UNION ALL
Select 'WO1368' as WONUM, 'WO1368' as WOGROUP, 0 as ISTASK, 236.37 as ACTLABCOST, 0 as ACTMATCOST, 0 as ACTTOOLCOST, 0 as ACTSERVCOST, '167461627' as OTHER_WO_COLUMNS FROM DUAL

WONUM  WOGROUP     ISTASK ACTLABCOST ACTMATCOST ACTTOOLCOST ACTSERVCOST OTHER_WO_COLUMNS
------ ------- ---------- ---------- ---------- ----------- ----------- ----------------
WO1361 WO1361           0          0          0           0           0 167457977       
WO1362 WO1362           0          0          0           0           0 167458280       
WO1363 WO1363           0     270.14          0           0           0 167483430       
WO1367 WO1363           1     540.27          0           0           0 167482806       
WO1370 WO1363           1      202.6          0           0           0 167483431       
WO1364 WO1364           0      88.86          0           0           0 167459454       
WO1366 WO1364           1      33.77          0           0           0 167458946       
WO1365 WO1365           0      67.53          0           0           0 167459331       
WO1368 WO1368           0     236.37          0           0           0 167461627       


Notice rows 3-5 are in WOGROUP #WO1363. And rows 6-7 are in WOGROUP #WO1364.
                                                                             

Problema:

Desejo somar os custos da ordem de serviço por WOGROUP (incluindo os custos da tarefa), mas não quero mostrar as linhas da tarefa no conjunto de resultados. Em outras palavras, quero acumular os custos da tarefa para suas ordens de serviço pai.

Para as linhas da ordem de serviço, também quero incluir outras colunas que não foram agrupadas (ou seja, OTHER_WO_COLUMNS).

Eu encontrei algumas maneiras de fazer isso.

Opção nº 1: (GROUP BY, JOIN e SUM)

A consulta faz um GROUP BY (SUM) em uma subconsulta para obter os custos totais da ordem de serviço. Em seguida, ele seleciona as ordens de serviço (excluindo tarefas) e se junta à subconsulta para trazer os custos totais.

--The suffix "_ti" stands for "tasks included".
select
    a.wonum,
    a.istask,
    b.actlabcost_ti,
    b.actmatcost_ti,
    b.actservcost_ti,
    b.acttoolcost_ti,
    b.acttotalcost_ti,
    other_wo_columns
from
    cte a
left join
    (
    select 
        wogroup as wonum, 
        sum(actlabcost)  as actlabcost_ti,
        sum(actmatcost)  as actmatcost_ti,
        sum(actservcost) as actservcost_ti,
        sum(acttoolcost) as acttoolcost_ti,
        sum(actlabcost + actmatcost + actservcost + acttoolcost) as acttotalcost_ti
    from 
        cte 
    group by 
        wogroup
    ) b
    on a.wonum = b.wonum
where
    istask = 0

WONUM      ISTASK ACTLABCOST_TI ACTMATCOST_TI ACTSERVCOST_TI ACTTOOLCOST_TI ACTTOTALCOST_TI OTHER_WO_COLUMNS
------ ---------- ------------- ------------- -------------- -------------- --------------- ----------------
WO1361          0             0             0              0              0               0 167457977       
WO1362          0             0             0              0              0               0 167458280       
WO1363          0       1013.01             0              0              0         1013.01 167483430       
WO1364          0        122.63             0              0              0          122.63 167459454       
WO1365          0         67.53             0              0              0           67.53 167459331       
WO1368          0        236.37             0              0              0          236.37 167461627  

Executei a consulta em uma tabela de produção completa (a tabela WORKORDER tem 4.500 linhas) e obtive este plano de explicação:

Plan hash value: 1879239811
 
---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |  9841 |   586K|   622   (2)| 00:00:01 |
|   1 |  SORT GROUP BY      |           |  9841 |   586K|   622   (2)| 00:00:01 |
|*  2 |   HASH JOIN OUTER   |           |  9841 |   586K|   620   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| WORKORDER |  4609 |   184K|   310   (1)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| WORKORDER |  9841 |   192K|   310   (1)| 00:00:01 |
---------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("A"."WONUM"="WOGROUP"(+))
   3 - filter("A"."ISTASK"=0)

O custo estimado é de 622 .

Opção nº 2: (função analítica SUM)

Eu encontrei uma maneira de fazer isso com a função analítica SUM . E envolvi a consulta da função analítica SUM em uma consulta externa que oculta as linhas da tarefa.

--The suffix "_ti" stands for "tasks included".  
select
    wonum,
    istask,
    actlabcost_ti,
    actmatcost_ti,
    acttoolcost_ti,
    actservcost_ti,
    acttotalcost_ti,
    other_wo_columns
from
    (
    select
        wogroup as wonum,
        istask,
        sum(actlabcost ) over (partition by wogroup) as actlabcost_ti,
        sum(actmatcost ) over (partition by wogroup) as actmatcost_ti,
        sum(acttoolcost) over (partition by wogroup) as acttoolcost_ti,
        sum(actservcost) over (partition by wogroup) as actservcost_ti,
        sum(actlabcost + actmatcost + acttoolcost + actservcost) over (partition by wogroup) as acttotalcost_ti,
        other_wo_columns
    from
        cte
    )
where
    istask=0

WONUM      ISTASK ACTLABCOST_TI ACTMATCOST_TI ACTTOOLCOST_TI ACTSERVCOST_TI ACTTOTALCOST_TI OTHER_WO_COLUMNS
------ ---------- ------------- ------------- -------------- -------------- --------------- ----------------
WO1361          0             0             0              0              0               0 167457977       
WO1362          0             0             0              0              0               0 167458280       
WO1363          0       1013.01             0              0              0         1013.01 167483430       
WO1364          0        122.63             0              0              0          122.63 167459454       
WO1365          0         67.53             0              0              0           67.53 167459331       
WO1368          0        236.37             0              0              0          236.37 167461627    

Também executei esta consulta em produção e obtive este plano de explicação:

Plan hash value: 2003557620
 
---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |  9841 |  1749K|   312   (2)| 00:00:01 |
|*  1 |  VIEW               |           |  9841 |  1749K|   312   (2)| 00:00:01 |
|   2 |   WINDOW SORT       |           |  9841 |   394K|   312   (2)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| WORKORDER |  9841 |   394K|   310   (1)| 00:00:01 |
---------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("ISTASK"=0)

O custo estimado é de 312 , que é aproximadamente metade da primeira consulta.

Eu acho que é mais rápido porque ele faz apenas uma verificação completa da tabela (a outra consulta fez duas verificações completas).

Pergunta:

Qual é a melhor/mais rápida maneira de resumir esses dados?

Como mencionado, notei que a opção #2 é mais rápida que a #1. Mas para ser honesto, o número 2 parece um pouco atrasado para mim. Recebi algumas críticas bastante duras por essa abordagem no Stack Overflow, então acho que não é uma boa maneira de estruturar uma consulta.

Oráculo 18c:

Em um post relacionado, demonstrei como usar a função GetVertices() e a função Table() para extrair os vértices de uma linha SDO_GEOMETRY:

Selecione os vértices de linha SDO_GEOMETRY como linhas

select  
    a.line_id, b.id as vertex_id, b.x, b.y
from
    a_sdo_geometry_tbl a, 
    table(sdo_util.getvertices(a.shape)) b       --<<--Here

   LINE_ID  VERTEX_ID          X          Y
---------- ---------- ---------- ----------
         1          1 671539.685 4863324.18
         1          2 671595.050 4863343.17
         1          3 671614.014 4863350.34
         1          4 671622.204 4863353.53

         2          1 71534.5567 4863119.99
         2          2 671640.738 4863157.13
         2          3 671684.862 4863172.02

Como exercício de aprendizado, quero criar minha própria versão da função GetVertices() usando PL/SQL.

Estou aberto a ideias sobre que tipo de mecanismo usar; qualquer um dos seguintes seria bom:

• SQL básico
• Cláusula MODEL
• Função personalizada

O ponto é, eu quero aprender como extrair os vértices e colocá-los em uma coluna de tabela aninhada em uma consulta, assim como a função GetVertices() faz.

SDO_UTIL.GETVERTICES

Esta função retorna um objeto de MDSYS.VERTEX_SET_TYPE, que consiste em uma tabela de objetos de MDSYS.VERTEX_TYPE. O Oracle Spatial and Graph define o tipo VERTEX_SET_TYPE como:

CREATE TYPE vertex_set_type as TABLE OF vertex_type;

O Oracle Spatial and Graph define o tipo de objeto VERTEX_TYPE como:

CREATE TYPE vertex_type AS OBJECT
   (x   NUMBER,
    y   NUMBER,
    z   NUMBER,
    w   NUMBER,
    v5  NUMBER,
    v6  NUMBER,
    v7  NUMBER,
    v8  NUMBER,
    v9  NUMBER,
    v10 NUMBER,
    v11 NUMBER,
    id  NUMBER);

Nota: Os tipos VERTEX_SET_TYPE e VERTEX_TYPE destinam-se ao uso apenas pelo Oracle. Não use esses tipos em definições de coluna ou funções que você cria.

Pergunta:

Existe uma maneira de extrair os vértices de uma linha SDO_GEOMETRY em uma coluna de tabela aninhada em uma consulta - sem usar a função GetVertices()? A saída seria um conjunto de resultados de consulta, não uma tabela estática.

Dicas:

As seguintes funções podem ser úteis:

PointN: Retorna um ponto que é o enésimo vértice na coleção de vértices

GetNumVertices: Retorna o número de vértices na geometria de entrada.

Eu tenho uma tabela Oracle 18c que possui uma coluna SDO_GEOMETRY (linhas):

create table a_sdo_geometry_tbl (line_id integer, shape mdsys.sdo_geometry);

insert into a_sdo_geometry_tbl (line_id, shape) 
values (1, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
    sdo_ordinate_array (671539.6852734378,4863324.181436138, 671595.0500703361,4863343.166556185, 671614.013553706,4863350.343483042, 671622.2044153381,4863353.525396131))  );

insert into a_sdo_geometry_tbl (line_id, shape) 
values (2, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
    sdo_ordinate_array (71534.5567096211,4863119.991809748, 671640.7384688659,4863157.132745253, 671684.8621150404,4863172.022995591))  );

insert into a_sdo_geometry_tbl (line_id, shape) 
values (3, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
    sdo_ordinate_array (671622.2044153381,4863353.525396131, 671633.3267164109,4863357.846229106, 671904.0614077691,4863451.286166754))  );

insert into a_sdo_geometry_tbl (line_id, shape) 
values (4, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
    sdo_ordinate_array (671684.8620521119,4863172.022995591, 671892.1496144319,4863244.141440067, 671951.2156571196,4863264.824310392, 671957.4471461186,4863266.847617676, 671966.8243856924,4863269.146632658))  )

select
    line_id,
    sdo_util.to_wktgeometry(shape) as well_known_text
from
    a_sdo_geometry_tbl;

   LINE_ID    WELL_KNOWN_TEXT
--------------------------------------------------------------------------------
         1    LINESTRING (671539.685273438 4863324.18143614, 671595.050070336 4863343.16655619, 671614.013553706 4863350.34348304, 671622.204415338 4863353.52539613)         
                                                                                
         2    LINESTRING (71534.5567096211 4863119.99180975, 671640.738468866 4863157.13274525, 671684.86211504 4863172.02299559)                                             
                                                                                
         3    LINESTRING (671622.204415338 4863353.52539613, 671633.326716411 4863357.84622911, 671904.061407769 4863451.28616675)                                            
                                                                                
         4    LINESTRING (671684.862052112 4863172.02299559, 671892.149614432 4863244.14144007, 671951.21565712 4863264.82431039, 671957.447146119 4863266.84761768, 671966.824385692 4863269.14663266)                                                       
                                                                                
4 rows selected.

Para cada linha, quero selecionar cada vértice como uma linha separada em uma consulta/conjunto de resultados.

Como posso fazer isso?

Eu sou um novato que está começando a aprender sobre funções analíticas do Oracle (funções de janela).

Eu sei por experiência que é importante entender completamente como os mecanismos SQL funcionam ao usá-los em consultas. Pode ser extremamente fácil cometer erros e produzir resultados incorretos.

Por exemplo, uma junção esquerda pode propagar linhas duplicadas, se o relacionamento for 1:M, mas a pessoa que escreve a consulta não percebeu isso. E para não especialistas, GROUP BY pode produzir contagens de linhas inesperadas, etc.

Pergunta:

Existem cenários em que as funções analíticas podem adicionar ou remover linhas de um conjunto de resultados? Ou posso assumir com segurança que as funções analíticas fornecem apenas informações adicionais como colunas calculadas - e nunca afetam a contagem de linhas?

No livro Getting Started with SQL , Thomas Nield fala sobre uma técnica que ele chama de truque de caso zero/nulo :

Existe uma ferramenta simples, mas poderosa, que pode aplicar diferentes condições de filtragem a diferentes agregações. Podemos criar contagens totais separadas quando um tornado estava presente versus não presente em duas colunas separadas:

SELECT year, month,
SUM(CASE WHEN tornado = 1 THEN precipitation ELSE 0 END) as tornado_precipitation,
SUM(CASE WHEN tornado = 0 THEN precipitation ELSE 0 END) as non_tornado_precipitation
FROM station_data
WHERE year >= 1990
GROUP BY year, month

O que fizemos efetivamente foi nos livrar das condições WHERE quando tornado = 1 ou tornado = 0 e, em seguida, mover essas condições para expressões CASE dentro das funções SUM(). Se a condição for atendida, o valor da precipitação é adicionado à soma. Caso contrário, um 0 é adicionado, sem efeito. Fazemos isso para ambas as colunas, uma para quando um tornado estava presente e outra para quando um tornado não estava presente, respectivamente.

Você pode fazer com que a expressão CASE tenha quantos pares de condição/valor forem necessários, dando a você a capacidade de fazer interceptações de valores altamente particulares com suas agregações. Você também pode usar esse truque para emular crosstabs e tabelas dinâmicas, expressando agregações em colunas separadas em vez de linhas. Um exemplo comum disso é fazer a análise do ano atual/ano anterior, pois você pode expressar anos separados com colunas diferentes.

Como iniciante, essa técnica parece ser muito útil para resumir dados. Quero pesquisar essa técnica on-line para encontrar outras técnicas semelhantes em livros ou outros recursos.

O autor desse livro chama a técnica de "truque de caso zero/nulo". Mas quando eu google esse termo, não recebo muitos resultados.

Pergunta:

Existe um nome geralmente aceito para essa técnica? (que traria mais resultados ao pesquisar online)

Eu tenho um sistema OOB que tem centenas de tabelas. Cada tabela tem uma coluna ROWSTAMP (inteiro) que é preenchida por um gatilho toda vez que um registro é criado/atualizado.

Todas as tabelas e gatilhos no banco de dados usam a mesma sequência. Cada vez que um registro é atualizado em qualquer lugar do banco de dados, essa sequência é usada para obter o próximo inteiro disponível. (Suponho que eu poderia considerar a sequência como uma espécie de sequência global ).

IBM: Uso da coluna ROWSTAMP nas tabelas Maximo

É lamentável que essas colunas ROWSTAMP sejam números inteiros e não datas. O que realmente preciso nas tabelas são as colunas CHANGEDATE. As datas seriam úteis para solução de problemas de entrada de dados, relatórios e análises em geral. Mas não posso justificar a personalização de centenas de tabelas com colunas de data e a adição de centenas de gatilhos no sistema OOB. As tabelas customizadas não seriam suportadas pela IBM e não valeria a pena o esforço/complexidade adicional.

Eu sinto que essas colunas ROWSTAMP estão tão perto de serem úteis para mim. Não posso deixar de me perguntar:

Existe uma maneira de recuperar a data em que esses valores de sequência ROWSTAMP foram gerados?

Por exemplo, e duvido que isso seja possível, eu poderia adicionar uma coluna DATE à sequência e depois juntar da minha tabela à sequência para obter a data? Ou eu poderia usar algum tipo de mecanismo de log na sequência para colocar linhas (com datas) em uma tabela sempre que o próximo valor de sequência for usado?

Meu software GIS tem um tipo de objeto Oracle 18c chamado ST_GEOMETRY .

O tipo de objeto tem atributos :

Os atributos do tipo espacial representam as seguintes informações:

• Entidade: O tipo de recurso geométrico armazenado na coluna espacial (cadeia de linhas, cadeia de várias linhas, multiponto, multipolígono, ponto ou polígono), cujo valor é uma máscara de bits derivada do procedimento armazenado st_geom_util.
• Numpts: O número de pontos que definem a geometria; para geometrias de várias peças, isso inclui os separadores entre cada peça, um ponto para cada separador.
• Minx, miny, maxx, maxy: O envelope espacial da geometria
• Área: A área da geometria
• Len: O comprimento do perímetro da geometria
• SRID: Contém o identificador para a geometria que a vincula a seu registro de referência espacial associado (sistema de coordenadas) na tabela ST_Spatial_References
• Pontos: Contém o fluxo de bytes das coordenadas do ponto que definem a geometria

Posso selecionar o atributo de um objeto usando um dos dois métodos:

1. Use a função TREAT() do Oracle .

select 
    treat(shape as sde.st_geometry).numpts as num_points
from 
    a_test_table

NUM_POINTS
----------
        37
       161

2. Ou posso simplesmente usar um alias:

select 
    a.shape.numpts as num_points
from 
    a_test_table a

NUM_POINTS
----------
        37
       161

Pergunta:

Por que é necessário usar um alias para selecionar um atributo de um objeto Oracle?

Se eu fosse remover o alias...

select 
    shape.numpts as num_points
from 
    a_test_table

Então eu receberia um erro:

ORA-00904: "SHAPE"."NUMPTS": invalid identifier

Eu tenho uma consulta que é usada em um relatório BIRT (na plataforma Maximo Asset Management da IBM).

Estou pensando em armazenar a consulta em uma visão materializada para melhorar o desempenho e reduzir a carga no sistema.

Pergunta:

Existe uma maneira de fazer o relatório falhar deliberadamente se o MV estiver desatualizado?

• Em outras palavras, preferimos não ter dados no relatório do que dados errados .

Editar:

Eu removi os detalhes excessivos da pergunta.

Eu criei uma visão materializada em um banco de dados Oracle 18c.

Quando olho para a aba Info da visualização materializada no Toad, vejo que existe uma propriedade chamada Update Trigger .

O que a propriedade Update Trigger representa?