Home » RDBMS Server » Performance Tuning » Indexing for lookup tables
Indexing for lookup tables [message #288231] Sun, 16 December 2007 01:03 Go to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Hi,

select col1,col2..... from table1,table2,table3,table4,table5
where condition1,
condition1,
......
conditionn


table1,table2,table4,table5 are in million records/table.
table3 is a lookup table.

Is it advisable to have a index on columns of where clause of table3?

What I found :
When I removed the index on table3(lookup), the query ran faster.

Regards
Srivaths
Re: Indexing for lookup tables [message #288232 is a reply to message #288231] Sun, 16 December 2007 01:06 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

>Is it advisable to have a index on columns of where clause of >table3?
Yes, but sometime depands on sitution.
Re: Indexing for lookup tables [message #288272 is a reply to message #288232] Sun, 16 December 2007 20:23 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Exactly right.

You should always have access and join paths indexed, but you should NOT always use them.

If it is faster without the index, then Oracle was mistakenly using the index. This could be for a variety of reasons. Here are some.

Ross Leishman
Re: Indexing for lookup tables [message #288291 is a reply to message #288272] Sun, 16 December 2007 22:56 Go to previous messageGo to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

I would rather remove the index from the lookup table rather than issuing a hint for making a full table scan on that table.

Thank you Mohammad & rleishman for your inputs.

Regards
Srivaths
Re: Indexing for lookup tables [message #288318 is a reply to message #288291] Mon, 17 December 2007 01:14 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
So you're happy for every other developer and user who has a query that DOES use the index to beat you with sticks? Because that's what will happen if you drop an important index.

Also, you have found that it is faster NOT to use the index for ONE PARTICULAR QUERY. What if you use different values? What if you change the query slightly?

The fact is, the index is either a good idea or not REGARDLESS of your query. Only in very rare circumstances should individual queries dictate indexing strategies, and even then it should only be to CREATE NEW or AUGMENT existing indexes; never to DROP existing indexes.

Keep the index, analyze ALL tables with DBMS_STATS.GATHER_TABLE_STATS and try again. If it still uses the index inappropriately, try a CARDINALITY hint (rather than a NO_INDEX hint, which is a bit brute-force).

Ross Leishman
Re: Indexing for lookup tables [message #288496 is a reply to message #288318] Mon, 17 December 2007 15:22 Go to previous messageGo to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

My point was that

SQLs on small tables 

When accessing a small table (say with 500 or fewer rows) it is surprising how often a Full Table Scan will out-perform indexed access. Typically no SQL on 
a small table will run for very long at all, however the cumulative effect of a small performance loss over many iterations can become enormous. For 
example, a loss of one twentieth of a second (0.05 sec) over 100,000 iterations will add over 80 minutes to a report or batch job.


The way to tell which is better is to try both. Use an INDEX() or HASH() hint to encourage indexed / hash cluster access respectively, or FULL() to 
encourage a Full Table scan, and SQL*Trace both. Compare the TK*Prof output to determine which is faster.

Source : The link you provided.

I will try.

Thanks
Srivaths

[Updated on: Mon, 17 December 2007 15:25]

Report message to a moderator

Re: Indexing for lookup tables [message #288505 is a reply to message #288496] Mon, 17 December 2007 20:29 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
srivaths wrote on Tue, 18 December 2007 08:22

My point was that
The link you provided

SQLs on small tables

When accessing a small table (say with 500 or fewer rows) it is surprising how often a Full Table Scan will out-perform indexed access. Typically no SQL on
a small table will run for very long at all, however the cumulative effect of a small performance loss over many iterations can become enormous. For
example, a loss of one twentieth of a second (0.05 sec) over 100,000 iterations will add over 80 minutes to a report or batch job.


The way to tell which is better is to try both. Use an INDEX() or HASH() hint to encourage indexed / hash cluster access respectively, or FULL() to
encourage a Full Table scan, and SQL*Trace both. Compare the TK*Prof output to determine which is faster.





Hang on, are we re-writing history? I thought your point was:
srivaths wrote on Mon, 17 December 2007 15:56

I would rather remove the index from the lookup table rather than issuing a hint for making a full table scan on that table.


Hopefully nowhere on the link I provided does it suggest that dropping production indexes is a good idea.

Ross Leishman
Previous Topic: Optimum value for INITIAL_EXTENT
Next Topic: Procedure Performance
Goto Forum:
  


Current Time: Sat Nov 09 14:04:53 CST 2024