Need some help with ideas/suggestions ... Oracle 19
We have 2 large tables (both with millions of rows). Common ID column to relate the records (1 to many relation)
Unfortunately, the data model is "old" and is "as is", so the data model is not (at this time) pliable.
Let's go with this simple model to work with:
create table tab_a
(id number ,
first_name varchar2(100),
last_name varchar2(100)
);
alter table tab_a add constraint tab_a_pk primary key ( id );
create index tab_a_ind1 on tab_a ( first_name, last_name );
create table tab_b
(id number,
alias_id varchar2(20),
alias_name varchar2(100)
);
alter table tab_b add constraint tab_b_pk primary key ( id, alias_id );
create index tab_b_ind1 on tab_b ( alias_name );
So given that, you can kinda of see the model intention, we have a primary table holding a person's first and last name, we then have a 2ndary table with other "aliases" and names. The "alias_id" is a simple string, such as "NICKNAME" or "PREVLASTNAME" or other such strings for various "aliases" we want to keep.
As mentioned, this model has been in place for quite a number of years, and has been working fine. We had a materialized view helping to join these tables, however, recently upgraded to Oracle 19, and having issues with the materialized view. Full disclosure - we are working with Oracle support on this as well, as they recommended a patch for possible bug with MV, and are now recommending we remove the Mat view. (in short, issue with mat view is JI contention on refresh - ie with incoming updates).
So in here, I'm just hoping to pick some ideas for an alternate solution to selecting data from these 2 tables. assuming we no longer use the Mat view, and just focusing on select.
Our current query:
select a.*
from tab_a a
left join tab_b b
on b.id = a.id
and b.alias_id = 'PREVLASTNAME'
where a.first_name = :1
and ( a.last_name = :2
or b.alias_name = :2 )
/
this query ends up running way too long. it's doing a full scan on tab_b. Since the OR in the where, it seems to only look at the first_name in tab_a ... which returns thousands of rows ... to then join those ids to tab_b, full scan seems better option.
So hopefully that explains what we're trying to do ... and with the unfortunate restriction of no changes to the tables ... what are possible options to help speed query up?
We tried breaking the OR out into union all .. however, that didn't seem to help much either.
Just hoping to get some ideas to work with and try .. ie other ways of joining/writing the query ... any "outside of the box" type of thoughts ??
My only thoughts were that the mat view resolved this nicely, as it "prejoined" the rows, and put the columns together in the same "table" .. allowing queries to work efficiently.
any suggestions are appreciated!
thanks!
[edit] Interestingly, we found something that seems to help significantly. Sharing here, but still would love to see any other ideas anyone has.
by writing the query like this:
with w1 as
( select id
from tab_b
where alias_id = 'PREVLASTNAME'
and alias_name = :2
)
select a.*
from tab_a a
left join w1 b
on b.id = a.id
where a.first_name = :1
and ( a.last_name = :2
or b.id is not null )
/
it seems to greatly improve the performance. Plan shows the name index for tab_b being used for initial w1 with clause, then PK with the join tab_a uses the name index (leading edge) for first_name ..
[/edit] [edit2] adding additional info here .. Oracle finally id'd issue with Mat View Refresh statistics. Once disabled, everything working fine with the mat view again .. so we won't need to rewrite .. I'll leave question open, as I am still genuinely curious of other ways to write this sort of query in future. [/edit2]