Based on the example, I was under the impression you were trying to fix a
comparison operator.
Oracle used hints( ie comments embedded in the sql) to tell the optimizer
which index to select.
Cant you assign a collating sequence in the ordre by? Why not use that to
determine if there is an index to use? It seems that then if one were to do the
following....
Is this ambiguous ???
select x
from t1 , t 2 where cast(t1.x as text collate nocase) = t2.y
order by X collate binary ;
Seems to me this is simply a filtering or comparison mechanism not for
ordering, as ordering is already handled by the order by clause.
In the case of Oracle, any time a function ie (toupper () ) is applied to a
column that is part of the index in a join clause, then the index is no longer
considered for the join operation and a Full table scan will occur. Since in
reallity the two are not really joinable, so for oracle a join operation must
be binary at least thats with simple indexes. There are
Function based indexes that will allow the user to define any function they
choose to apply to the indexed columns. Then the optimizer will select that
index when the function is applied to the join attributes that match...
IMHO the cast is the way to go to assign a collating sequence.
Maybe you need an additional index type ? One where the index is specified
with a function.
Ken
[EMAIL PROTECTED] wrote: "Igor Tandetnik" wrote:
>
> MS SQL also supports defining multiple indexes on the same table and
> field(s), differing only in collation (and the optimizer is smart
> enough, most of the time, to use these indexes appropriately). I haven't
> tried it with SQLite, maybe it's also supported.
>
SQLite also supports multiple indices differing only
in collation, and the optimizer will use the appropriate
index to satisfy the ORDER BY clause. But because the
SQLite parser does not currently provide a means to change
the collating sequence assigned to an expression, there
is no way for the optimizer to use different indices
for optimizing access since an expression in the WHERE
clause can only have a single collation. That's what
I'm trying to fix....
--
D. Richard Hipp
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
