Index in 2 tables

Tuning de Banco, Tuning de SQL, Ferramentas de tuning
Post Reply
User avatar
heraldoaraujo
Rank: Programador Pleno
Rank: Programador Pleno
Posts: 41
Joined: Sun, 19 Feb 2012 12:10 pm

Is it possible to create an index composite in 2 separate tables for example, coluna_1.tabela_1 and coluna_2.tabela_2?
User avatar
stcoutinho
Moderador
Moderador
Posts: 850
Joined: Wed, 11 May 2011 5:15 pm
Location: são Paulo - SP

Heraldo,

Maybe the first question is: "Why would you need an index that would involve two distinct tables?"

If there was such a type of index, the most correct to affirm is that it would be a type of "Cartesian product". How could you normalize the relationship between two columns of distinct tables? How would you like that "record 1" of a table would need to be associated with the "registration 20012" of the other table?

An index will always be associated with a table and usually aims to streamline queries or construct data integrity rules (PK / AK). If by chance I need to make jollys of two distinct tables, I can choose to create distinct indexes in these two tables, involving the Join filters (Clause Were).

I do not have a practical example here, but if I have two tables that present relationships "too much for many", I can create an intermediate entity (relationship table).
Example:



In the example above, the table "A x b" would be composed only by join columns between "A" and "B", and you could create a "Composite PK" (Primary Key) or an "AK Compound" index with these columns.

I hope forists can give a better explanation for your doubt,
Hugs,

Sergio
User avatar
fbifabio
Moderador
Moderador
Posts: 199
Joined: Tue, 22 Feb 2011 1:51 pm
Location: São Paulo - SP
Contact:
Fábio Prado
www.fabioprado.net

Heraldo,

There is the Bitmap Join index that allows you to index a column from another related table. See below an example of how to create it:

Select all

 
create bitmap index hr.emp_bm_idx on hr.employees(d.department_name ) 
      from hr.employees e, hr.departments d where e.department_id = d.department_id; 

User avatar
heraldoaraujo
Rank: Programador Pleno
Rank: Programador Pleno
Posts: 41
Joined: Sun, 19 Feb 2012 12:10 pm

Thank you very much for the tip, that's what I was looking for. But it could please explain the excerpt from the command

Select all

hr.employees(d.department_name )
|
User avatar
fbifabio
Moderador
Moderador
Posts: 199
Joined: Tue, 22 Feb 2011 1:51 pm
Location: São Paulo - SP
Contact:
Fábio Prado
www.fabioprado.net

The script creates an index in the HR.EMPLOYEES table that contains data from the HR.DAPARTMENTS (D) Related table column data, okay?


User avatar
heraldoaraujo
Rank: Programador Pleno
Rank: Programador Pleno
Posts: 41
Joined: Sun, 19 Feb 2012 12:10 pm

Fabio Prado,

Thanks for the tip but I tried to create the Bitmap index and Oracle raised the error ora-25954 saying that it should have a primary key constraint.
In my situation I have a table of (Employees) 1 for N (courses), until then I can create a constraint in the Employees table but in the Table No.
In the current scenario is still possible to create the bitmap index?
User avatar
fbifabio
Moderador
Moderador
Posts: 199
Joined: Tue, 22 Feb 2011 1:51 pm
Location: São Paulo - SP
Contact:
Fábio Prado
www.fabioprado.net

Heraldo, for this case unfortunately it is necessary to have a relationship between the tables. This type of index is widely used in BDS OLAP queries that consult clothing tables and dimensions, scenarios where there is the referential integrity between the tables.


Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 5 guests