Smart denormalization and Enhanced PL/SQL Function Call performance – Comparing Function Based Index and Materialized Views

2

Queries that contain calls to user defined PL/SQL Functions can be performance killers. If a function call is part of the WHERE clause, depending on the execution plan it can be performed for every record that is considered for returning in the query result. For large tables that can lead to thousands to even million of Function invocations (executions and SQL to PL/SQL context switches) in a single query. Needless to say this might add up. Especially if the function itself is non trivial and has to perform derivations or calculations that are not infinitely fast.

Since Oracle 8i we can make use of Function Based Indexes to alleviate some of our distress. With FBI the database pre-calculates the function on each record in the table and stores the outcome in an Index Structure. When the query tries to find records that have a specific result for the function call, the Cost Based Optimizer can decide to use the FBI to directly go to the records that satisfy that query condition. However, this will only help if we have an equality search (where function-result = value). If we want to find records that have values similar to or larger than the function result, the FBI cannot be used. Or if we want to query a number of records from the table, not filtering on the function result, but including the function result for each record, we cannot simply find that function result from the precalculated set of values recorded in the FBI.

With the advent of Materialized Views and the ever increasing query rewrite capabilities, it seems that Materialized Views can take over from or at least complement the Function Based Index functionality. In this article we will investigate what Materialized Views have to offer and how they hold up against the Function Based Index.

....
 

Let’s first create a test-table to run our queries against. Inspired by countless articles by Tom Kyte and Alex Nuijten I will create a table based on ALL_OBJECTS:

&nbsp;create table ao1 <br />as <br />select object_name<br />,      object_type<br />,      last_ddl_time<br />,      owner <br />,      object_id <br />from   all_objects <br />where  rownum &lt; 25000<br />/<br /><br />alter table ao1<br />add constraint ao_pk primary key (object_id)<br />/<br /><br />select count(*) <br />from ao1<br />/<br />  COUNT(*)<br />----------<br />     24999<br />

This table contains 25000 records – enough to get some meaningful results. The function to be used for our experiment is the following:

create or replace function calculate_stuff<br />( p_name in varchar2<br />, p_type in varchar2<br />) return varchar2<br />deterministic<br />is<br />begin<br />  keep_count.g_ctr:= keep_count.g_ctr + 1;<br />  for i in 1..100000 loop null; end loop; -- slow it down a little<br />  return initcap(p_type)||': '||lower(p_name);<br />end;<br />/<br />&nbsp;

It does nothing special: concattenating three string values – and spending some time in an idle loop to make the function execution somewhat non-trivial timewise. The package it calls – keep_count.g_ctr – is a package I have used to keep track of the number of function invocations.

We will first set up the Function Based Index as well as the Materialized View. Note that all queries in the remainer of this article are executed against AO1 to test the Function Based Index and against AO2 to try the Materialized View approach. We create table AO2 in exactly the same way as AO1:

create table ao2 <br />as <br />select object_name<br />,      object_type<br />,      last_ddl_time<br />,      owner <br />,      object_id <br />from   all_objects <br />where  rownum &lt; 25000<br />/<br /><br />alter table ao2<br />add constraint ao2_pk primary key (object_id)<br />/<br />&nbsp;

Setting up the Function Based Index 

Time to create the Function Based Index:

SQL&gt; create index calc_stuff_fbi on ao1( calculate_stuff(object_name, object_type) )<br />  2  /<br /><br />Index created.<br /><br />Elapsed: 00:02:10.50 <br />

Creating the index takes little over 2 minutes for our 25000 records. 

Setting up the Materialized View

Our Materialized View is very simple: it contains the primary key and the function result:

create materialized view ao2_mv<br />refresh fast on commit<br />with primary key<br />ENABLE QUERY REWRITE<br />as<br />select object_id<br />,      calculate_stuff(object_name, object_type) as calc_stuff<br />from   ao2<br />/<br />Materialized view created.<br /><br />Elapsed: 00:02:15.86<br /> <br />

In order to create this query as Refresh On Commit, we need to create a Materialized View on the underlying table:

create materialized view log on ao2<br />/<br />Materialized view log created.<br /><br />Elapsed: 00:00:01.11 <br />

Equality Search – The Function Based Index can Shine! 

The query we compare first – the equality search on the Function outcome:

select object_id<br />,      calculate_stuff(object_name, object_type) <br />from   ao1<br />where calculate_stuff(object_name, object_type) = 'Table: dual'<br />/ <br />

Here are the results for the two situations: 

<table cellspacing="1" cellpadding="1" border="1" style="width: 100%;"><tbody><tr><td><br /></td><td>Execution Plan<br /></td><td>Timing<br /></td><td>#Function Calls<br /></td><td>#Records returned<br /></td></tr><tr><td>Function Based Index<br /></td><td>&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=242 Bytes=9922)<br />&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; TABLE ACCESS (BY INDEX ROWID) OF 'AO1' (TABLE) (Cost=2 Card=242 Bytes=9922)<br />&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp; INDEX (RANGE SCAN) OF 'CALC_STUFF_FBI' (INDEX) (Cost=1 Card=97)<br /><br /></td><td>0.1s<br /></td><td>0<br /></td><td>1<br /></td></tr><tr><td>Materialized View<br /></td><td>0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT STATEMENT Optimizer=ALL_ROWS (Cost=40 Card=2 Bytes=40<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30)<br />1&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; MAT_VIEW REWRITE ACCESS (FULL) OF 'AO2_MV' (MAT_VIEW REWRITE) (Cost=40 Card=2 Bytes=4030)<br /></td><td>0.3s<br /></td><td>0<br /></td><td>1<br /></td></tr></tbody></table><p>&nbsp;</p>

Now we can go a little beyond this result for the Materialized View. We can create an index on the column calc_stuff in the materialized view:

create index calc_stuff_index on ao2_mv (calc_stuff)<br />/<br /><p>Index created.<br /><br />Elapsed: 00:00:00.93 <br /></p>

The results for the query are now: 

 

<table cellspacing="1" cellpadding="1" border="1" style="width: 100%;"><tbody><tr><td><br /></td><td>Execution Plan<br /></td><td>Timing<br /></td><td>#Function Calls<br /></td><td>#Records returned<br /></td></tr><tr><td>Materialized View<br /></td><td>&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=2015)<br />&nbsp;1&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; MAT_VIEW REWRITE ACCESS (BY INDEX ROWID) OF 'AO2_MV' (MAT_VIEW REWRITE) (Cost=2 Card=1 Bytes=2015)<br />&nbsp;2&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp; INDEX (RANGE SCAN) OF 'CALC_STUFF_INDEX' (INDEX) (Cost=1 Card=1)<br /></td><td>0.1s<br /></td><td>0<br /></td><td>1<br /></td></tr></tbody></table><p>&nbsp;</p>

 

Querying on similarity – not equality: What good can the FBI do now?

This next query queries for records whose Function Result is similar to a certain value. Will we be able to leverage the fact that the function results are pre-calculated and available in the FBI?

select object_id<br />,      calculate_stuff(object_name, object_type) <br />from   ao1<br />where calculate_stuff(object_name, object_type) like 'Table:%'<br /><p>/&nbsp;</p>

And the results:

<table cellspacing="1" cellpadding="1" border="1" style="width: 100%;"><tbody><tr><td><br /></td><td>Execution Plan<br /></td><td>Timing<br /></td><td>#Function Calls<br /></td><td>#Records returned<br /></td></tr><tr><td>Function Based Index<br /></td><td>Execution Plan<br />----------------------------------------------------------<br />&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT STATEMENT Optimizer=ALL_ROWS (Cost=48 Card=1208 Bytes=49528)<br /><br />&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; TABLE ACCESS (BY INDEX ROWID) OF 'AO1' (TABLE) (Cost=48 Card=1208 Bytes=49528)<br /><br />&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp; INDEX (RANGE SCAN) OF 'CALC_STUFF_FBI' (INDEX) (Cost=3 Card=217)<br /></td><td>0.3s<br /></td><td>0<br /></td><td>20<br /></td></tr><tr><td>Materialized View<br /></td><td>0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT STATEMENT Optimizer=ALL_ROWS (Cost=17 Card=20 Bytes=40300)<br />1&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; MAT_VIEW REWRITE ACCESS (BY INDEX ROWID) OF 'AO2_MV' (MAT_VIEW REWRITE) (Cost=17 Card=20 Bytes=40300)<br />2&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp; INDEX (RANGE SCAN) OF 'CALC_STUFF_INDEX' (INDEX) (Cost=2 Card=20)<br /></td><td>0.3s<br /></td><td>0<br /></td><td>20<br /></td></tr></tbody></table><p><br />&nbsp;</p>

Querying for the Function Result – no where clause referencing the function at all
here

In this case we look at a query that does not filter on the function result but still wants to return that result:

select object_id<br />,      calculate_stuff(object_name, object_type) <br />from   ao1<br />where rownum &lt; 500<br />/ <br />

We wonder of course whether we can make use of the precalculated values stored in the Function Based Index: 

<table cellspacing="1" cellpadding="1" border="1" style="width: 100%;"><tbody><tr><td><br /></td><td>Execution Plan<br /></td><td>Timing<br /></td><td>#Function Calls<br /></td><td>#Records returned<br /></td></tr><tr><td>Function Based Index<br /></td><td> 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=499 Bytes=20459)<br />&nbsp;1&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; COUNT (STOPKEY)<br />&nbsp;2&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp; TABLE ACCESS (FULL) OF 'AO1' (TABLE) (Cost=3 Card=24162 Bytes=990642)<br /><br /></td><td>4.4s<br /></td><td>499<br /></td><td>499<br /></td></tr><tr><td>Materialized View<br /></td><td>0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=499 Bytes=1005485)<br />1&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; COUNT (STOPKEY)<br />2&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp; MAT_VIEW REWRITE ACCESS (FULL) OF 'AO2_MV' (MAT_VIEW REWRITE) (Cost=3 Card=25929 Bytes=52246935)<br /></td><td>1.4s<br /></td><td>0<br /></td><td>499<br /></td></tr></tbody></table><p><br />&nbsp;</p>

The FBI result almost suggests – comparing with the previous query – that if we had included the function call in the where clause, we could have leveraged the FBI "cache". Let’s see about that. Well it turns out that queries like:

 select object_id<br /> ,      calculate_stuff(object_name, object_type)<br /> from   ao1<br /> where rownum &lt; 500<br /> and   (calculate_stuff(object_name, object_type) like 'Table%' or 1=1) <br />

still make the function call for all records returned. So we cannot leverage the FBI if we did not explicitly select records based on the expression the FBI was based on. The FBI is just to smart for its own good you could say. Well, that reminds me: we use a hint to try to enforce usage of the FBI:

select /*+ INDEX (ao1 calc_stuff_fbi) */object_id<br />,      calculate_stuff(object_name, object_type) <br />from   ao1<br />where rownum &lt; 500<br />/ <br />

The CBO happily ignores my hint. So still the 499 function calls.

Or-ring the results together

The next query filters on records that either have TABLE as their OBJECT_TYPE value or have a function result like ‘Synon%’. For each record returned, we want to see the function result.

select object_id<br />,      calculate_stuff(object_name, object_type) <br />from   ao1<br />where  object_type ='TABLE'<br />or     calculate_stuff(object_name, object_type) like 'Synon%' <br />/<br /><br />

We wonder how many function executions are required:

<table cellspacing="1" cellpadding="1" border="1" style="width: 100%;"><tbody><tr><td><br /></td><td>Execution Plan<br /></td><td>Timing<br /></td><td>#Function Calls<br /></td><td>#Records returned<br /></td></tr><tr><td>Function Based Index<br /></td><td>&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT STATEMENT Optimizer=ALL_ROWS (Cost=56 Card=1438 Bytes=58958)<br /><br />&nbsp;1&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; TABLE ACCESS (FULL) OF 'AO1' (TABLE) (Cost=56 Card=1438 Bytes=58958)<br /></td><td>4m3s<br /></td><td>37706<br /></td><td>12727<br /></td></tr><tr><td>Materialized View<br /></td><td>0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT STATEMENT Optimizer=ALL_ROWS (Cost=289 Card=752 Bytes<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =1533328)<br /><br />1&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; CONCATENATION<br />2&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp; HASH JOIN (Cost=199 Card=579 Bytes=1180581)<br />3&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TABLE ACCESS (FULL) OF 'AO2' (TABLE) (Cost=49 Card=174<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 80 Bytes=419520)<br /><br />4&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MAT_VIEW REWRITE ACCESS (BY INDEX ROWID) OF 'AO2_MV' (<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MAT_VIEW REWRITE) (Cost=38 Card=694 Bytes=1398410)<br /><br />5&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INDEX (RANGE SCAN) OF 'CALC_STUFF_INDEX' (INDEX) (Co<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; st=3 Card=125)<br /><br />6&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp; HASH JOIN (Cost=90 Card=173 Bytes=352747)<br />7&nbsp;&nbsp;&nbsp; 6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TABLE ACCESS (FULL) OF 'AO2' (TABLE) (Cost=50 Card=175<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Bytes=4200)<br /><br />8&nbsp;&nbsp;&nbsp; 6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MAT_VIEW REWRITE ACCESS (FULL) OF 'AO2_MV' (MAT_VIEW R<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EWRITE) (Cost=40 Card=694 Bytes=1398410)<br /></td><td>18.0s<br /></td><td>0<br /></td><td>12727<br /></td></tr></tbody></table><p>&nbsp;</p>

Aggregation
of the Function Results

Just to analyze all common situations, let’s see if aggegrating the function results has an unexpected behavior:

select object_type<br />,      max(calculate_stuff(object_name, object_type) )<br />from   ao1<br />group<br />by     object_type<br />/<br />&nbsp;

And here are the results for this query: 

<table cellspacing="1" cellpadding="1" border="1" style="width: 100%;"><tbody><tr><td><br /></td><td>Execution Plan<br /></td><td>Timing<br /></td><td>#Function Calls<br /></td><td>#Records returned<br /></td></tr><tr><td>Function Based Index<br /></td><td>0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT STATEMENT Optimizer=ALL_ROWS (Cost=52 Card=24162 Bytes=676536)<br />1&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; SORT (GROUP BY) (Cost=52 Card=24162 Bytes=676536)<br />2&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp; TABLE ACCESS (FULL) OF 'AO1' (TABLE) (Cost=49 Card 24162 Bytes=676536)</td><td>2m6s<br /></td><td>24999<br /></td><td>18<br /></td></tr><tr><td>Materialized View<br /></td><td>0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT STATEMENT Optimizer=ALL_ROWS (Cost=420 Card=20945 Byt<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; es=42706855)<br /><br />1&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; SORT (GROUP BY) (Cost=420 Card=20945 Bytes=42706855)<br />2&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp; MERGE JOIN (Cost=418 Card=20945 Bytes=42706855)<br />3&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MAT_VIEW REWRITE ACCESS (BY INDEX ROWID) OF 'AO2_MV' (<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MAT_VIEW REWRITE) (Cost=219 Card=25929 Bytes=52246935)<br /><br />4&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INDEX (FULL SCAN) OF 'AO2_PK1' (INDEX (UNIQUE)) (Cos<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; t=60 Card=25929)<br /><br />5&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SORT (JOIN) (Cost=199 Card=20945 Bytes=502680)<br />6&nbsp;&nbsp;&nbsp; 5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TABLE ACCESS (FULL) OF 'AO2' (TABLE) (Cost=49 Card=2<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0945 Bytes=502680)<br /></td><td>1.4s<br /></td><td>0<br /></td><td>18<br /></td></tr></tbody></table><p><br />&nbsp;</p>

Concattenating the function result – any FBI leverage?

The last query we take a look at uses the Function Result to simply concattenate it with another value. We already know the FBI won’t do us any good in this situation. Let’s find out about the Materialized Vie. Here is the query:

select 'Object Id: '||object_id<br />     ||' ('<br />     ||substr( calculate_stuff(object_name, object_type)<br />             , 1<br />             , instr(calculate_stuff(object_name, object_type),':')<br />             )<br />     ||')' Object_Id_and_Type<br />from   ao1<br />where  rownum&lt; 500<br />/ <br />

and here are the results:

<table cellspacing="1" cellpadding="1" border="1" style="width: 100%;"><tbody><tr><td><br /></td><td>Execution Plan<br /></td><td>Timing<br /></td><td>#Function Calls<br /></td><td>#Records returned<br /></td></tr><tr><td>Function Based Index<br /></td><td>0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=499 Bytes=20459)<br />1&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; COUNT (STOPKEY)<br />2&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp; TABLE ACCESS (FULL) OF 'AO1' (TABLE) (Cost=3 Card=24162 Bytes=990642)<br /><br /></td><td>5.8s<br /></td><td>499<br /></td><td>499<br /></td></tr><tr><td>Materialized View<br /></td><td>0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=499 Bytes=1<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 005485)<br /><br />1&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; COUNT (STOPKEY)<br />2&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp; MAT_VIEW REWRITE ACCESS (FULL) OF 'AO2_MV' (MAT_VIEW REW<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RITE) (Cost=3 Card=25929 Bytes=52246935)<br /></td><td>1s<br /></td><td>0<br /></td><td>499<br /></td></tr></tbody></table><p><br />&nbsp;</p>

Conclusions

The Function Based Index provides very efficient query performance improvements when we filter by an expression and (possibly) also want to return that expression. The FBI is a very lean structure with almost the lowest possible data duplication. If we want to find records that have a function result equal or similar to a certain value, the FBI is superior. (and I have to confess I was surprised to see that in the second query using LIKE on the Function Result the FBI could still be leveraged!).

The Materialized View approach is not as lightweight. It requires a Materialized View Log. And it stores a complete – though lean – record for every record in the base table. Adding an index on this Materialized View adds even more storage requirements as well as DML overhead (probably very slight). However, the precalculated values in the Materialized View could be leveraged in every scenario we went through. After creating the Materialized View, we never again had to make a single Function Call!

The over all conclusion can be that in many circumstances, Materialized Views can be a good alternative to Function Based Indexes. And – not something we specifically addressed in this article – MVs are a very good compromise between denormalization in the data design and pragmatical & performant data retrieval. We do not have to clutter our tables with column containing redundant – denormalized, derived, calculated – data, yet we can still benefit from the performance improvements such denormalization would give us!

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

2 Comments

  1. Ah, very interesting.

    An interesting hybrid solution would be to place the MV table in a hash cluster, thus getting co-location of the same value in a way that approaches the index and fast lookup on equality predicates.

    Plenty of food for thought in here though Lucas.