At 10:23 AM 8/06/2003 +0000, you wrote:

>Hello,
>
>This is my query.
>
>select
>a.cust_id,
>b.lbl,
>c.lbl,
>a.cust_nam
>from
>cust_v a,
>source_v b,
>ctyp_v c
>where
>a.source_id=b.source_id and
>a.ctyp_id=c.ctyp_id and
>upper(a.source_id)='SAB'
>order by a.cust_nam
>
>This query takes 2 minutes 50 second to fetch 27019
>records.CUST_V,SOURCE_V and CTYP are views.i have the index for
>cust_id in the CUST Table, source_id in SOURCE table and ctyp_ix in
>CTYP Table.Foreign key is not defined and there is no index for
>source_id and ctyp_id in CUST table.
>
>how to improve the performance of this query???
>Should i create the index for source_id and ctyp_id in CUST table.Is
>it must?

If these columns hav low distribution (few values over many records) then a
single-column index on it would make the query slower, not faster. I think
the main sources of delay here are the upper() expression in the WHERE
clause and the lack of an index on cust_nam.

I would want to tidy this query up to use the SQL-92 explicit join syntax, too.

So - minimum - index cust_nam. And I'm wondering why you haven't enforced
upper-case on source_id, if it needs to be used for searching. If it's not
too late, I would want to fix that up:

update cust_v
set source_id = upper (source_id)
where source_id is not null;
commit;

set term ^ ;
create trigger bi_cust_v_1 for cust_v
active before insert position 1
as
begin
if (new.source_id is not null) then
new.source_id = upper(new.source_id);
end ^
commit ^
create trigger bu_cust_v_1 for cust_v
active before update position 1
as
begin
if (new.source_id is not null) then
new.source_id = upper(new.source_id);
end ^
commit ^
set term ; ^

New query (after mods and cust_name indexed):
select
a.cust_id,
b.lbl,
c.lbl,
a.cust_nam
from cust_v a
join source_v b on a.source_id=b.source_id
join ctyp_v c on a.ctyp_id=c.ctyp_id
where a.source_id = 'SAB'
order by a.cust_nam

heLen