| Can anybody explain this index [message #307725] |
Wed, 19 March 2008 13:12  |
cycloscott
Messages: 4
Registered: March 2008
Location: San Francisco
|
Junior Member |
|
|
Sorry, but I'm at a complete loss as to why anybody would do this. If anybody has a valid reason why, I'd like to hear it before I go after the vendor.
30M row table: SHIPMENT
PK constraint/index on SHIPMENT_KEY
17 existing composite indexes (with a fair amount of column overlap)
The latest hot fix from the vendor creates yet another index on
(col1, col2, SHIPMENT_KEY, col4, col5)
---and I'll add that col1 contains less than 10 unique values across 30M rows. col2 has even less.
My first instinct is to tell them to go pound sand. But there is a question of continuing support if we don't apply all of their hot fixes. How can this index be anything other than extra overhead and garbage data for the CBO? I'm certainly not ruling out that I may be missing something, but I sure can't think of what.
|
|
|
|
|
|
| Re: Can anybody explain this index [message #307727 is a reply to message #307726] |
Wed, 19 March 2008 13:24  |
cycloscott
Messages: 4
Registered: March 2008
Location: San Francisco
|
Junior Member |
|
|
| Michel Cadot wrote on Wed, 19 March 2008 11:16 | Do you know all the queries they used?
Did you ask them what is the purpose of this new index?
|
They're not terribly forthcoming with answers. We're only one of their clients, and I suspect that col1 and col2 are in greater use by some of their others.
My take on the whole matter is that if we're querying with the SHIPMENT_KEY already, it's the single most efficient method of getting the rows that we're interested in. But how can the PK be of any additional benefit in the #3 position?
[Updated on: Wed, 19 March 2008 13:43] Report message to a moderator |
|
|
|
|
|
| Re: Can anybody explain this index [message #307743 is a reply to message #307731] |
Wed, 19 March 2008 15:06 |
cycloscott
Messages: 4
Registered: March 2008
Location: San Francisco
|
Junior Member |
|
|
| Michel Cadot wrote on Wed, 19 March 2008 11:48 | For instance if conditions are on the first 2 columns and selected columns are the other ones, then this will prevent from accessing the table itself.
Just an example, there can be many other cases when it will be useful.
|
Oh I completely understand having a composite index with the PK leading. We've built several of those ourselves. PK for efficiency, the second and third columns for the data, excludes a table read.
But with a less precise column leading the index, I would think that we're looking at a much less efficient retrieval. Let alone the idea that we're not even using the data in the second column. We've got the first column with very skewed data, the second column with a single value, and the third column being unique across the table. Strikes me as being a very bottom-heavy index structure. The top nodes would be next to worthless.
|
|
|
|
|
|
| Re: Can anybody explain this index [message #307759 is a reply to message #307745] |
Wed, 19 March 2008 17:23 |
cycloscott
Messages: 4
Registered: March 2008
Location: San Francisco
|
Junior Member |
|
|
| Michel Cadot wrote on Wed, 19 March 2008 13:34 | Let's take classical emp table.
Assume an index on (mgr,empno).
Then you can get very efficiently all empno below a manager: just a small index scan.
It all depends on your query, restrictions and returned data.
|
Gotcha. I thought of that same thing in my last meeting. What I didn't mention, and should have, is that the bulk of the queries that we see in this DB are 'select * ...'
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
