Object Relational Map
Joseph Reuben., December 16, 2012 - 6:16 pm UTC
When using a structure that maps to an Object to Relational Mapping is not unusual to see the number of joins going all the way up to 80-150 joins. Of course a denormalization of the model will be advised.
I've succesfully ran queries of 200+ Joins in Oracle and it's the only database that will allow you to perform this many number of joins. If you're dealing with this numbers, I suggest that you take a look into Bitmap-Indexing and Index-Oriented-Tables. They will be very useful for this kinds of joins.
December 18, 2012 - 12:21 pm UTC
how does a bitmap index make it any better? I'm just curious as to what you've found.
to join via an index - we'd use the index to get rowids to get rows.
whether bitmap or b*tree.
the advantage of bitmaps would be for ad-hoc queries - where you could create a bunch of single column bitmaps and let us OR/AND them together to create a new index on the fly. Presumably - if you are joining, you have b*tree indexes in place on primary/foreign keys - so they'd be pretty much just as effective.
I'm not saying you are wrong, just curious as to the circumstances?
Bitmap Indexes, Index Oriented Tables and Many Joins.
José Rubén Hernández Isla, December 19, 2012 - 7:37 pm UTC
I've developed an Object Relational Mapper for Oracle.
Scenario 1. I use bitmap indexes on some of the Join tables when these tables are usually just a reference for some small catalog (eg. car-brand). When joining using a Bitmap Index,it runs faster, I suppose is because no additional trip to another index is needed. It is a big performance gain if there are not many items in car-brand, otherwise inserts and updates get slow.
Scenario 2. I use Index-Oriented-Tables when the point of entry is usually the same for select operations, In the same example, could be a Model of a car in wich the first column is the car manufacurer, when selecting the table "CARSFORSALE", this query runs faster.
Scenario 3. The third of course is using covering indexes that satisfy the most common used queries. One very good feature in Oracle is that you can create two indexes that are accessed sequentially, ie. one index can satisfy the condition of another index and with some tweaking you can satisfy most of the query combinations with some four or five indexes.
The use of covering indexes will degrade the insert speed and update speed of all tables and will also take more space on the server specially RAM. However (and is often the case with ORM Mapping) a lot of tables will not change that much and will be joined a lot. So in this kind of tables is I always add as many indexes as (within reason) possible.
Another thing.... when using this many Joins I always pass where statements as parameters, the parsing of these super-big queries slows down the server A LOT.
December 20, 2012 - 5:08 pm UTC
scenario one: please give more details. there would be no real difference between a bitmap and a b*tree index here.
the statement about "no additional trip to another index" doesn't compute - there wouldn't be with a b*tree.
scenario two: I didn't dispute the efficiency of index organized tables for some types of situations.
scenario three: not under dispute nor mentioned previously.
I'm just looking for some factual information behind the statement about joins via bitmaps being significantly better than b*trees. I don't agree with that assessment.
bitmaps are good when you have to merge a lot of indexes together to build a new index for ad-hoc queries (b*trees do not do that so well)
If you can - just tell us how to set up a test case that demonstrates the statement you made. Then we can discuss the science behind it (why it works better).
and of course, bitmaps only work on read only/read mostly tables (tables that are loaded in bulk in a warehouse for example) they are not at all appropriate in an OLTP system