| Indexing for lookup tables [message #288231] |
Sun, 16 December 2007 01:03  |
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 #288272 is a reply to message #288232] |
Sun, 16 December 2007 20:23  |
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 #288318 is a reply to message #288291] |
Mon, 17 December 2007 01:14 |
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 |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
