Forum Stats

  • 3,751,465 Users
  • 2,250,366 Discussions
  • 7,867,433 Comments

Discussions

Indexs on MVs - performance tuning

Hi All,

Lets say we have two MVs (refresh fast on demand) which pulls data from remote DB.
A regular view is based on these 2 mvs which are joined together.

The regular view is queried by application users with where conditions such as
"lower(first_name) like 'test%'.
To improve the performance, where we should possible create the function based index
on the first_nm / last_nm. I tried that when they are created on the MV itself then
they are not picked up as I noticed in the explain plan and using a hint to force index
in the regular view select query makes it worse.
Any suggestions.

for eg - (MV1)
CREATE MATERIALIZED VIEW st_mv
REFRESH FAST ON DEMAND
WITH primary key
AS
SELECT st_ID ,
FIRST_NM ,
LAST_NM ,
MIDDLE_NM ,
ACTIVE_FLAG ,
ACT_ID
FROM [email protected]_link

--and MV2
CREATE MATERIALIZED VIEW st_dtl_mv
REFRESH FAST ON DEMAND
WITH primary key
AS
SELECT st_ID ,
email,
address,
ph
FROM [email protected]_link

--view on MV1 and MV2
create or replace view st_v
as
select a.first_nm,
a.last_nm,'
a.middle_nm
b.email,
b.address,
b.ph
from st mv a,
st_dtl_mv b
where a.st_id = b.st_id
and a.active_ind = 'Y'
and b.active_ind = 'Y'

Basically, how can we improve the performance of serch queries on the regular view st_v.
Any suggestions are appreciated.

I also tried tp create a query rewrite enable view as below (hoping that query against regular view st_v will
use the ENABLE QUERY REWRITE view). However the index does not
get picked up in the serch queries. BTW all the mv are analyzed for optimal explain plan.

CREATE MATERIALIZED VIEW st_query_mv
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT * from st_v;

CREATE INDEX st_query_ix1 ON st_query_mv
(lower(FIRST_NM));

Any help / suggestions are much appreciated.

Thanks a lot for any help or suggestion you can provide.
DK
Tagged:
This discussion has been closed.