Thanks, but I don't believe this to be a problem because my JDBC layer when I construct the query is using setObject( parameter, getId, Types.BIGINT )
so by the time it arrives at the database that cast should have already occured?
I could be wrong but running the Explain Analyse shows indexes being used, but the right join for the locale stuff is the killer.
Thanks
On Thu, 2004-02-05 at 13:31, Mike Mascari wrote:
Hadley Willan wrote:
> Hi all,
> I am using some views now to put together a particular format for
> my Java client factory to produce Java Beans from the database.
>
> Because we support internationalisation we are representing values as
> an id then storing their multiple languages in unicode to support the
> same repesentation at the database.
>
> This format is:
>
> base_table, id bigint, is_disabled boolean default false.
>
> resource_table, foreign_key_to_base_table, locale_foreign_key,
> display_name, is_translated
>
> As such, my views are quite slow because there are a number of Right
> Joins occuring so that I can present a single "locale" field in the
> view that all the localised information will attach to correctly.
>
> That way I can > select * FROM v_object where locale = 'en_GB' and
> object_id = 120031;
Without taking the view definition into account, the above query could
not use an index on object_id because it is of type 'bigint', but the
integer constant is parsed as 'integer'. It must either be rewritten as:
object_id = 120031::bigint
or
object_id = '120031'
or set the sequence for this identifier to start fetching values > 4.2
billion (32-bit numbers). Of course, the view definition may have other
optimization possibilities as well...
Mike Mascari
