[Mondrian] Table hint schema extensions checked in

Eric McDermid mcdermid at stonecreek.com
Mon Jul 13 18:44:30 EDT 2009


I've just checked in 12936 & 12937, which extend the Mondrian schema  
to support table hints as discussed earlier.

Hint support is very minimal at this point (only "force_index", and  
only on MySQL), but the pattern is simple and should be easy to extend  
for other hints and dialects. For the time being, I've also confined  
the scope of hints to queries that select the level values (these are  
the ones built via SqlTupleReader.generateSelectForLevels()).  I'll  
need to experiment before I expand that further.  It may make sense to  
add a way of controlling scope within the Hint schema element itself,  
but we'll see.

Unfortunately, this does not include as robust a test suite as I would  
like.  A true positive test case would need an index defined in  
FoodMart (or SteelWheels) to work against.  Even a negative test using  
a nonexistent index an MDX query proved problematic, since the "no  
such index" SQL exception gets swallowed prior to returning to the  
calling test.  I'll chew on this a bit more, but as always suggestions  
are welcome.

  -- Eric



On Jun 19, 2009, at 3:28 PM, Julian Hyde wrote:

>> Eric:
>>
>> My sense is that the index hint probably is useful at least on some
>> databases, though obviously the above 3 don't qualify as an
>> exhaustive
>> list.  Given the way MySQL and SQL Server seem to work, I'm not sure
>> tagging on arbitrary text would work.  At the very least, we'd need
>> some kind of syntax indicating where those tags need to go, at which
>> point we may just be better off declaring an attribute and
>> handling it
>> in the dialects.
>
> Your 'forceIndex' attribute is too narrowly focused on one area of  
> hints in
> queries. I know people will want to use other kinds of hints. I  
> suggest a
> <Hint> sub-element, with 'type' attribute and text content. It's up  
> to the
> dialect to know how to interpret that type of hint.
>
> Therefore:
>
>    <Dimension foreignKey="my_dim_id" name="foo">
> 	<Hierarchy hasAll="true" allMemberName="All foo"
> primaryKey="my_dim_id" >
>                 <Table name="my_dim" alias="my_dim">
>                     <Hint type="force_index">my_index</Hint>
>                 </Table>
> 		...
> 	</Hierarchy>
>    </Dimension>
>
>
> I also worry about what will happen if mondrian identifies shared  
> usages of
> the same table internally. It will tend to generate hints wherever  
> the table
> is used. Nothing we can do about this, but it might bemuse some folks.
>
>>> Julian:
>>>
>>> why not extend the syntax of the Order function, e.g.
>>>
>>> Order(
>>> [Customer].Members,
>>> [Measures].[Unit Sales] ASC NULLS COLLATE AS ZERO EMPTY COLLATES
>>> LAST)
>>
>> I can't explain why the original authors did it this way, but as for
>> why I didn't think of it?  It was probably just too flippin'
>> obvious. :)
>
> It's obvious to anyone who has read the SQL standard. All 10 of  
> them. 6 of
> these are dead, and most of the others are wishing they hadn't.
>
> Julian
>
>





More information about the Mondrian mailing list