Home » SQL & PL/SQL » SQL & PL/SQL » Creating one index for 2 columns or two (10g)
Creating one index for 2 columns or two [message #391132] Wed, 11 March 2009 03:34 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,
Just some opinion needed

I have 2 columns in table, plan to create nonunique index on both columns

  period_id - used in join
  period_name - used in where clause


The question is, whether to create one nonunique index for both columns, or two nonunique indexes (1 for period_id, another 1 for period_name)

Is there any differents? No idea which approach is better

[Updated on: Wed, 11 March 2009 03:35]

Report message to a moderator

Re: Creating one index for 2 columns or two [message #391135 is a reply to message #391132] Wed, 11 March 2009 03:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Indexing depends on queries.
What are they? Do you restrict on both columns at the same time or one column in each query?

Regards
Michel
Re: Creating one index for 2 columns or two [message #391139 is a reply to message #391135] Wed, 11 March 2009 03:45 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

It depends, actually user makes use of this table for analysis.

They might use both this columns at same time, or only one of the column during data selection in their reporting tool.
Re: Creating one index for 2 columns or two [message #391144 is a reply to message #391139] Wed, 11 March 2009 04:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to first know the queries then you define the indexes, you can't do the opposite way.

An index on both columns is (mostly) not used if you only restrirt on the second one.

Regards
Michel
Re: Creating one index for 2 columns or two [message #391148 is a reply to message #391144] Wed, 11 March 2009 04:06 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
I see. Tq
Re: Creating one index for 2 columns or two [message #391347 is a reply to message #391148] Wed, 11 March 2009 21:26 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It will depend on the distribution of data, but (very) generally speaking, indexes for filtering and indexes for joining are used separately.

Consider this:
SELECT *
FROM   emp
JOIN   dept ON emp.deptno = dept.deptno
WHERE  emp.sal > 5000
AND    dept.country = 'US'


Oracle could either:
  1. Find the employees with SAL>5000 and then lookup their deparment, filtering if they are not US.
  2. Find the departments with COUNTRY='US' and then lookup their employees, filtering if their SAL<=5000
  3. Find the departments with COUNTRY='US', find the employees with SAL>5000, then join the 2 lists filtering unmatched rows.

In the first case, indexes EMP(SAL) and DEPT(DEPTNO) would be useful. DEPT(DEPTNO, COUNTRY) would be even better.

In the second case, indexes DEPT(COUNTRY) and EMP(DEPTNO) would be useful. EMP(DEPTNO, SAL) would be even better.

In the third case, indexes DEPT(COUNTRY) and EMP(SAL) would be useful.

As you can see from the first two cases, if you scan on the filter predicate (eg. SAL>5000), there is no benefit in having an index on the join column (EMP.DEPTNO), REGARDLESS of whether it is a separate index or combined with the filter (SAL) index.

The only time a concatenated index really helps is when the table is not the leading table in a join. In these cases, the join columns go first in the index.

My suggestion is to index join clauses and filter predicates separately. If you then need to tune, you can experiment by adding filter columns to the end of join indexes.

Ross Leishman
Re: Creating one index for 2 columns or two [message #391562 is a reply to message #391347] Thu, 12 March 2009 10:28 Go to previous message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Thanks for the detail explanation. It really helped to improve my understanding
Previous Topic: trigger
Next Topic: PL/SQL comment bug
Goto Forum:
  


Current Time: Thu Dec 08 01:54:56 CST 2016

Total time taken to generate the page: 0.15204 seconds