What V11.5.7 can (and can’t) do with Expression-based index
Overview
Expression-based indexes have been around since V10.5, allowing your CREATE INDEX statement to build an index using an expression, e.g.
The expression you use can be based on the data in the table (as in this example), but it doesn’t have to be. The result of the expression is stored in the index itself. This can give some significant performance improvements:
- an SQL statement could contain a predicate that includes an expression and, if it matches the expression-based key in the index, the optimizer can use the expression-based index to access the data
- the result of the expression might be in the required result set, or
- the expression might be needed in the ORDER BY
The advantage of that example can be demonstrated with a query such as
SELECT * FROM Logbook.Airfield WHERE UPPER(country) = 'SWITZERLAND'If you look at the access path before the index was created, it shows
 |
 |
Not a huge estimated cost but this is a very small table and I try and avoid table scans as a rule.
Put the expression-based index in place and the access path changes to
 |
 |
The expression-based index is used and the Estimated Cost is only 6% of the original.
V11.5.7
The latest version of DB2 LUW advertised a couple of improvements around Expression-based index that caught my eye, specifically removing the limitation on using RENAME and ADMIN_MOVE_TABLE on tables that have them defined (see links below for some details).
https://www.ibm.com/docs/en/db2/11.5?topic=1157-sql-enhancements
https://www.ibm.com/docs/en/db2/11.5?topic=1157-database-administration-enhancements
They’re related but they advertise the removal of a couple of limitations that these Expression-based index have suffered from since arriving with 10.5.
REORG
If you try and do an on-line REORG of a table that has an expression-based index on it, you get an error:
To be fair, there’s nothing in the documentation that suggests that this issue was going to be addressed; I was just hoping it was in there with the other enhancements to Expression-based index. But it’s not.
RENAME
The issue here is that, until this latest release, an attempt to Rename a table with an Expression-based index, fails. Here’s an example on a V11.1.3.3 instance
As you can see, the first attempt fails but the second, after I remove the Expression-based index on the table, is successful.
The output from the same test run on a V11.5.7.0 instance (although I’m also including the output from a query to show Indexes on this table), is successful:
You can see that the Expression-based index has stayed with the table as it’s renamed and hasn’t impeded the operation. All good.
ADMIN_MOVE_TABLE
Here’s where it seems to get a bit funny. Let me just say at this juncture that I am happy to be corrected; it might be that I have got the wrong end of the stick from the advertised enhancements. But it looks to me as if the ADMIN_MOVE_TABLE procedure should now cope with an Expression-based index. If you follow the link I included in the Overview, you’ll find this
“The ADMIN_MOVE_TABLE procedure is now able to move tables having an index with an expression-based key. This feature is available when the expression does not contain qualified names.”
That qualifier in the 2nd sentence doesn’t seem to me to apply to my simple Expression-based index. But let’s compare and contrast V11.5.7 with my old V11.1.3. As I’m sure you’re aware, there are 2 ways of running ADMIN_MOVE_TABLE; you can supply the procedure with some parameters and it will build a target table for you, or you can pre-define the target table and get it to do the copying, replaying staging data and a final swap for you. Both methods should allow almost 100% access to the table as it is being changed.
V11.1.3 Method #1
This is an example of that first method; the proc is supplied with a parameter that indicates the change I want to occur to my source table. In this case I want to change the basis on which it is organized.
You don’t get a lot of detail with that failure, but it’s pretty black & white about the fact that it’s not going to work, unless you remove the table features it doesn’t like; in this case the Expression-based index.
If I drop that Expression-based index and try again, it works fine.
V11.5.7 Method #1
Now I’m going to do exactly the same operation in my latest instance, with the hope and expectation that it will cope with the fact that my source table has an Expression-based index.
Personally, I don’t think that’s a very helpful message. I spent quite a bit of time digging into this and trying to work out what the issue was. I suspect it might be connected to is this ADMIN_MOVE_TABLE FAILS WITH SQL0104N WHEN MOVING TABLE WITH DOUBLE QUOTES IN NAME WHICH HAS A STATISTICS PROFILE
I think what is happening is that it is trying to rename the Statistical View that is automatically created when you build an Expression-based index to a new, system-generated name. That name will include lower-case characters and will therefore need to be in quotes. The statistics profile might also be automatically created in order to service the Statistical View, but, according to the Apar
“Problem was first fixed in Db2 Version 11.5.7.0 (11.5 Mod 7 Fix Pack 0)”.
But the point is, it doesn’t work, and I thought the blurb said it would.
V11.1.3 Method #2
Next, it’s the pre-defined source table version. What you see in the screenshot below is
- the source table, AIRFIELD
- with an EXPRESSION-BASED INDEX defined on it
- and the automatically generated Statistical View that this brings with it
- the same error as with Method #1
V11.5.7 Method #2
Exactly the same operations are run in my new V11.5.7 instance and you can see, this time, it works
The only thing to point out is that a check of the objects, to make sure that the Procedure has successfully moved the table and the EXPRESSION-BASED INDEX, shows this:
Look at the name of that Statistical View now: that’s what makes me think the Method #1 ADMIN_MOVE_TABLE might be getting tripped up by its own processing.
Conclusion
I’m aware that Expression-based index haven’t gained a huge amount of traction in the DB2 customer base, but we do have customers using them with a great deal of success. So, I’m pleased to see some focus on making them more robust and making my job as a DBA easier in administering a database that includes them. But this latest enhancement looks like it might still have a few rough edges, unless, as I said earlier, I have misinterpreted the documentation.
If you think I have, please drop me a line. I’d be very happy to discuss.
E-mail: Mark.Gillis@triton.co.uk
Leave a Reply