db-jdo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig L Russell <Craig.Russ...@Sun.COM>
Subject Re: [Fwd: last issue with completeness test]
Date Thu, 20 Apr 2006 21:20:31 GMT
Hi Jörg,

On Apr 20, 2006, at 5:40 AM, Jörg von Frantzius wrote:

> Hello Craig,
>
> I think that if we have includeSubclasses==true that the WHERE  
> clause generated by JPOX will OR all possible values of the  
> discriminator column. Won't that also be sped up by an index on the  
> discriminator column?

Not necessarily. It depends on how selective the indexes are in the  
query or navigation. Let's take an example where you have Employees  
in your Person table. You query for Employee where salary > 100000.  
Your SQL statement is: SELECT ID, DISCRIMINATOR, NAME, SALARY FROM  
PERSON p WHERE p.SALARY > 10000 AND p.DISCRIMINATOR IN  
('org.apache.jdo.tck.pc.company.Employee',  
org.apache.jdo.tck.pc.company.PartTimeEmployee',  
'org.apache.jdo.tck.pc.company.FullTimeEmployee')

If the index on SALARY is more selective than the index on  
DISCRIMINATOR, you will do an index scan of the table using the  
SALARY index. And you would only examine persons that passed the  
SALARY test to reject those that did not pass the DISCRIMINATOR test.  
If instead, the database chose the DISCRIMINATOR index, it would  
examine all employees to see if they passed the SALARY test.

So it's not clear to me that an index on DISCRIMINATOR is useful in  
this case. It depends on whether there exists a more selective index  
than DISCRIMINATOR. This is similar to why you usually don't index  
BOOLEAN or BIT columns. Not good selectivity.

Craig
>
> Regards,
> Jörg
>
> Craig L Russell schrieb:
>> Hi Jörg,
>>
>> Just to be clear: this improves performance only in the case that  
>> you know what specific class you are looking for (you have a  
>> relationship to a specific most-derived subclass) and there is not  
>> another more appropriate index to optimize on. So it's worthless  
>> in the generic query use case because there will typically be a  
>> more restrictive index, and of little value in the navigation case  
>> because you are joining on a foreign key anyway. So it's only  
>> useful for a table scan of a most-derived subclass or superclass  
>> Extent with includeSubclasses==false.
>>
>> This came out more harshly than I thought it would when I started.  
>> Is there a good use case for indexing the discriminator column?
>>
>> Regards,
>>
>> Craig
>>
>> On Apr 18, 2006, at 9:22 AM, Jörg von Frantzius wrote:
>>
>>> Craig Russell schrieb:
>>>> Hi Erik,
>>>>
>>>> It's probably not related to the problem, but why do you declare an
>>>> index defined on the discriminator columns in the metadata?
>>>>
>>> Yes I'm not Erik but still: in our production system we do that  
>>> to increase performance of queries. We'd even like to have that  
>>> by default.
>


Mime
View raw message