index in tables 2

Tuning de Banco, Tuning de SQL, Ferramentas de tuning
  

Poston Wed, 13 Aug 2014 4:50 pm

You can create a composite index on 2 separate tables for example, column_1. tabela_1 and coluna_2. tabela_2?
heraldoaraujo

Poston Fri, 15 Aug 2014 9:11 am

Heraldo, perhaps the first question is: " because you need an index that would involve two separate tables? " If there was such a kind of index, the more correct to state that he would be a kind of Cartesian product " ". How you could normalize the relationship between two columns of different tables. How do you espeficaria the " " 1 registration of a table would need to be associated with the " record " of another table 20012?

An index will always be associated with a table and generally aims to expedite queries or build data integrity rules (PK/AK). If I ever need to do joins two separate tables, I can choose to create separate indexes in these two tables, involving the join filters (WHERE clause).

I have here a practical example, but if I have two tables that have relationships very " to many ", I can create an intermediate entity (relationship table).

Example:
[table] — — — —-[table A x B] — — — —-[table B]
in the example above, the table " x B " would be composed only by columns of join between the " " and " B ", and you could create a " composite " PK (primary key) or an index " AK " compound (unique) with these columns.

I expect foristas can give a better explanation to your question, hugs, Sergio
stcoutinho
Location: Sao Paulo - SP

Poston Sun, 24 Aug 2014 11:46 pm

Heraldo, Exists the bitmap join index that allows you to index in a table a column of another related table. Below is an example of how to create it:
Code: 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;
[] s
fbifabio
Location: São Paulo - SP

Fábio Prado
www.fabioprado.net

Poston Mon, 25 Aug 2014 4:27 pm

fbifabio wrote:Heraldo, Exists the bitmap join index that allows you to index in a table a column of another related table. Below is an example of how to create it:
Code: 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;
[] s
Thank you very much for the tip, that's what I was looking for. But could you please explain me the command snippet
Code: Select all
hr.employees(d.department_name )
|
heraldoaraujo

Poston Thu, 28 Aug 2014 10:38 pm

The script creates an index on the table HR.EMPLOYEES that contains data from the department_name column of the related table hr. departments (d), ok?

[] s
fbifabio
Location: São Paulo - SP

Fábio Prado
www.fabioprado.net

Poston Fri, 29 Aug 2014 1:55 pm

Fabio Prado, I thank you for the tip but I tried to create the index bitmap and Oracle raised the error ora-25954 saying you should have a primary key constraint.
In my situation I have a table of (employees) 1 to N (courses), until then I can create a constraint on the employees table but in the table not courses.
In the current scenario is still possible to create bitmap index?
heraldoaraujo

Poston Mon, 01 Sep 2014 10:51 am

Heraldo, for this case is unfortunately necessary to have relationship between tables. This type of index is widely used in queries from OLAP Dbs browsing fact tables and dimensions, scenarios in which there are referential integrity between the tables.

[] s
fbifabio
Location: São Paulo - SP

Fábio Prado
www.fabioprado.net



Return to DBA Tuning

Who is online

Users browsing this forum: No registered users and 2 guests