Oracle Types (types)

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
almir.jg
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 8
Joined: Wed, 20 Jan 2016 9:20 am

:?: Oracle Object:?:
Hello friends!

I'm starting a study to practice some Oracle features, one of them is Type Object. The data volume that work is considerable (B.I.), so that the process is agile, I chose to use Type Object provided by Banco Oracle.

Below is an example:

Select all

-- Criação do objeto (estrutura de dados) 
Create Type OBJ_Teste 
(Id number, 
nome varchar2(50) 
);

Select all

-- Criação da tabela de dados 
Create Type TBL_Teste is table of OBJ_Teste;

As I use This object [/b]:

Select all

 
Declare 
   xDataSet TBL_Teste := TBL_Teste(); 
   vNome Varchar2(50); 
Begin 
  -- Populando o objeto 
   for x in (select /*+ Parallel  */id, nome from tabela_blablabla) 
   loop 
     xDataSet.Extend; -- incrementa um elemento no objeto 
     xDataSet(xDataSet.Count) := OBJ_Teste(x.Id, x.Nome); -- Popula o DataSet baseado na estrutura do objeto 
   end loop; 
  -- Lendo o objeto (na memoria) 
  select a.nome into vNome from [b]TABLE[/b](xDataSet) a where a.id = 45 -- Ou outro ID qualquer; 
  dbms_output.put_line('Nome: '||vNome); 
end;   
So far so good! The object is created and populated and if the volume of data is small is a beauty, but when the data volume is large and the object has been transformed into a table it will be fully read (Full Table Scan), this leaves the search Very slow, even if in memory.

Where to pick up? This makes the object very slow (even in memory).

The question :: Idea: [color=# 0000bf] There is some way to create an index as if it were on a table, to access a Element based on the content of the element so that this search is faster.? [/color]

Note: Objective is to create a Quick search action , Object Type [/ u] of Oracle.
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

I am kind of apprehensive to leave in memory a data volume so large.
I would try other shapes, type, create a table global temporary table (does not generate archives), then you can try using an append hint, or some other to insert.

hence use this table and create the index at the end.
is an idea ...: roll:
souldeath
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 48
Joined: Wed, 25 Aug 2010 9:39 am
Location: Limeira

almir.jg wrote:

Select all

-- Criação da tabela de dados 
Create Type TBL_Teste is table of OBJ_Teste;
/ quote]

And if you create like this?

Select all

Create Type TBL_Teste is table of OBJ_Teste INDEX BY BINARY_INTEGER;
In the loop does not need to use Extend I think because it would already be indexed, it would only pass the value to the desired position:

Select all

 
for x in (select /*+ Parallel  */id, nome from tabela_blablabla) 
   loop 
     xDataSet(xDataSet.Count) := OBJ_Teste(x.Id, x.Nome); -- Popula o DataSet baseado na estrutura do objeto 
   end loop; 

needs to test if this would make it faster.
Post Reply
  • Information
  • Who is online

    Users browsing this forum: Google [Bot] and 21 guests