Re: Improving performance with a Function instead of a

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
>
>

--
Hadley Willan » Director » hadley(dot)willan(at)deeperdesign(dot)com » +64(21) 28
41 463
Deeper Design Limited » +64(7) 377 3328 » www.deeperdesign.com