Oracle index question



  • Figured it couldn't hurt to ask. Sorry for the wall of text, there's a bunch of background.

    We're migrating our BI database to a cloud platform, and in the process we're going from Enterprise Edition to Standard Edition. The main impact of this that concerns me is that we can no longer use bitmap indexes.

    Now for most of the bitmap indexes we have this isn't a big problem. A few queries might not be able to run as efficiently but most stuff will be more or less OK using normal B-tree indexes instead.

    However, there's one that I'm worried about. One of our largest tables is frequently queried by a combination of line of business (LOB) and other factors. There are 14 LOBs in the system but the data is hugely skewed, with almost 90% of the data in a single LOB. Naturally, this LOB is frequently queried on. However, the large table only contains a foreign key to the LOB, not the name of the LOB which is being queried.

    So what used to happen is that someone would do a query for this LOB, the query optimiser would say "oh, there's a lot of LOBs, that probably partitions the space pretty well, let's use that index" and queries would often try to use the LOB index to look up the table even for the LOB that constitutes 90% of the data, so performance was very bad for queries on that LOB, which was most of them.

    I fixed this by creating a join index on the large table, so that the index contains the actual LOB name from the LOB table. That lets the optimiser understand that when we query for this LOB it shouldn't use the LOB index, but when we query for other LOBs the LOB index is a good idea.

    However, join indexes have to be bitmap type and so I can't have this index after we migrate.

    I'm currently thinking that the best approach may simply be not to index the LOB. That means everyone who's querying for the main LOB will see unchanged performance, but the people who are interested in other LOBs will suffer badly. (Since the other LOBs are much smaller in scope, typically only one person looks after them for the whole company - or indeed one person looks after several of them. So they won't have many other filters in their queries, meaning they'll probably have to look at a large chunk of the data for every query.)

    Is there a better way, one which will allow me to have efficient queries for the few people who are interested in the minor LOBs as well as for the many who are running queries against the main LOB?

    Note that making changes to the table structure is not on the cards for now, but it's something I can look at post migration if it will bring enough of a win.



  • @Scarlet_Manuka said in Oracle index question:

    Oracle doesn't handle this automatically? SQL Server had this use case covered for the past 20 years.


  • ♿ (Parody)

    @Jaime I always thought it did. Maybe statistics on the table need to be updated?


  • FoxDev

    @Scarlet_Manuka I know in SQL Server, you can configure an index to cover oft-used fields, so you could have an index on the LOB ID (or whatever it is) that covers the LOB name; that way, the query planner doesn't need to go to the table itself, as the data it needs is in the index. Does Oracle have a similar feature?


  • ♿ (Parody)

    @RaceProUK Yes, if you only request data that's in an index, only the index is loaded.



  • @RaceProUK said in Oracle index question:

    @Scarlet_Manuka I know in SQL Server, you can configure an index to cover oft-used fields, so you could have an index on the LOB ID (or whatever it is) that covers the LOB name; that way, the query planner doesn't need to go to the table itself, as the data it needs is in the index.

    This handles a different scenario. SQL Server's distribution page allows you to create an index and the system will know that this index is a great choice for doing lookup for the condition LastName = 'Plouff', but a poor choice for LastName LIKE 'S%'.


  • ♿ (Parody)

    An alternative, I suppose, would be to partition based on the LOB, with the behemoth in one and the rest in another (or whatever). I don't know if that falls under what you meant by changing the table structure or if it conflicts with what you have.



  • @Jaime said in Oracle index question:

    Oracle doesn't handle this automatically?

    Yes, Oracle has histograms. And if I was querying on "line of business #5" rather than on the line of business name, it would know not to use the index. Because we're querying on the name, which is only stored in the line of business table, the optimiser doesn't know which specific line of business ID is going to be queried and can't apply the appropriate histogram value.

    @RaceProUK said in Oracle index question:

    @Scarlet_Manuka I know in SQL Server, you can configure an index to cover oft-used fields, so you could have an index on the LOB ID (or whatever it is) that covers the LOB name; that way, the query planner doesn't need to go to the table itself, as the data it needs is in the index. Does Oracle have a similar feature?

    Yes, that's what it's using now (a join index). But it's not available in Standard Edition, so we'll lose that ability when we migrate; that's the point of this thread.

    @boomzilla said in Oracle index question:

    An alternative, I suppose, would be to partition based on the LOB

    Partitioning is another feature that is not available in Standard Edition. sigh

    Looks like we might be stuck with this one. Thanks all for your comments.


  • FoxDev

    @Scarlet_Manuka said in Oracle index question:

    Yes, that's what it's using now (a join index). But it's not available in Standard Edition

    @Scarlet_Manuka said in Oracle index question:

    Partitioning is another feature that is not available in Standard Edition.

    Wow, Standard Edition sucks


  • Java Dev

    @Scarlet_Manuka Could you add hints from the application side, force it to use or skip the index as appropriate?



  • @RaceProUK said in Oracle index question:

    Wow, Standard Edition sucks

    It's Oracle's version of "fuck you, give me money".

    More seriously, I guess you don't need this kind of stuff if you're a small business owner trying to keep track of a few thousand orders or whatever. So I can sort of see why they'd reserve it for Enterprise Edition. Doesn't mean I have to be happy about it though :)

    @PleegWat said in Oracle index question:

    @Scarlet_Manuka Could you add hints from the application side, force it to use or skip the index as appropriate?

    Hmmm, maybe sometimes, but I would think probably not in most cases. This is for our BI system, so most of the time we're looking at reports where users can select from a range of filters according to their needs. So usually we don't know when we're building the report whether it would be good to use this index on a particular query or not (it depends on whether the user will filter by LOB1, and if so, on what values they select).

    Still, there may be some reports that are hard-coded to the main LOB that we could add an optimiser hint to. It's worth checking, so thanks for that, but it's not going to solve most of the problem. (But fixing even only a few reports is still a good thing.)

    1 Actually not really, it doesn't do any harm to say "don't use this index" if they're not filtering on the LOB; the optimiser will just look at you and say "why would you even think I would do that, you moron".


  • Discourse touched me in a no-no place

    @RaceProUK said in Oracle index question:

    Wow, Standard Edition sucks

    It's almost like they want people to buy Enterprise Edition.


  • Discourse touched me in a no-no place

    @Scarlet_Manuka said in Oracle index question:

    More seriously, I guess you don't need this kind of stuff if you're a small business owner trying to keep track of a few thousand orders or whatever. So I can sort of see why they'd reserve it for Enterprise Edition.

    That too.


  • Discourse touched me in a no-no place

    @Scarlet_Manuka said in Oracle index question:

    More seriously, I guess you don't need this kind of stuff if you're a small business owner trying to keep track of a few thousand orders or whatever.

    This sort of thing would tend to encourage people to use a competitor…


  • Discourse touched me in a no-no place

    @dkf said in Oracle index question:

    This sort of thing would tend to encourage people to use a competitor…

    Having to pay for Enterprise Edition when you don't need it might also do that.
    Lose some features, save some money.


  • Discourse touched me in a no-no place

    @loopback0 said in Oracle index question:

    Having to pay for Enterprise Edition when you don't need it might also do that.

    I know someone who did that for a bakery automation job, switching from Oracle to SQLite (!!!) plus a bit of coded up server on the front end, the sort of thing you could knock together in Perl or Python in a day. I can't remember how much they were saving per year, but it was a lot and the customer was very happy with it…


  • FoxDev

    @Scarlet_Manuka said in Oracle index question:

    More seriously, I guess you don't need this kind of stuff if you're a small business owner trying to keep track of a few thousand orders or whatever. So I can sort of see why they'd reserve it for Enterprise Edition.

    MS do the same with partitioning in SQL Server, but as far as I can tell, covered indexes are available even in Express Edition, which is aimed at stuff like blogs that have six readers. And IIRC, Express Edition is free.


  • ♿ (Parody)

    @Scarlet_Manuka said in Oracle index question:

    Still, there may be some reports that are hard-coded to the main LOB that we could add an optimiser hint to.

    I once had a report where based on user filter selection, it made sense to do a cartesian join (one side of the join would have only one row). But Oracle would like that plan so much that if a user subsequently did something different it would still try to use the cartesian join, which would eventually exhaust temp space.

    I converted it to a stored procedure that ran different queries based on those filters. :headdesk:



  • @loopback0 said in Oracle index question:

    @RaceProUK said in Oracle index question:

    Wow, Standard Edition sucks

    It's almost like they want people to buy Enterprise Edition.

    Enterprise Edition also sucks, but with more features.


Log in to reply