URGENT: Problem on Oracle Materialized View

Hi.
I've performance problem on this materialized view:

CREATE MATERIALIZED VIEW tom.mv1 
ON PREBUILT TABLE WITH REDUCED PRECISION
REFRESH FAST ON COMMIT
WITH PRIMARY KEY
AS 
SELECT col1, col2, col3, col4, col5, col6, ROWID,
  FROM tom.tab1
 WHERE col1 IS NOT NULL
UNION ALL
SELECT col1, col2, col3, col4, col5, col6, ROWID
  FROM sam.tab2
 WHERE col1 IS NOT NULL;

Open in new window



execution plan has ACCESS FULL TABLE on sam.tab2

last_analyzed statistics on table tom.tab1 is:  18/10/2013

last_analyzed statistics on table sam.tab2 is:  12/10/2013

What I must check to improve the performance of this MV?

Thanks in advence!
ralph_reaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
With a where clause of "WHERE col1 IS NOT NULL" a FULL TABLE SCAN (FTS) is likely.

Not ALL FTS' are bad.

Please show the table description for tab2.
0
ralph_reaAuthor Commented:
CREATE TABLE sam.tab2
(
  col1         VARCHAR2(4 BYTE)              NOT NULL,
  col2        NUMBER(8),
  col3         VARCHAR2(10 BYTE),
  col4         NUMBER(10),
  col5     VARCHAR2(1 BYTE),
  col6        VARCHAR2(10 BYTE),
  col7       VARCHAR2(10 BYTE),
  col8          VARCHAR2(3 BYTE),
  col9         NUMBER(10,3),
  col10           VARCHAR2(1 BYTE),
  col11           NUMBER(4),
  col12        NUMBER(32),
  col13            VARCHAR2(100 BYTE),
  col14        NUMBER(14),
  col15         VARCHAR2(20 BYTE),
  col16        NUMBER(14),
  col17         VARCHAR2(20 BYTE),
  col18       NUMBER(3),
  col19         NUMBER(12)                    NOT NULL,
  col20         VARCHAR2(2 BYTE),
  col21     NUMBER(5),
  col22  NUMBER(8)
)

Open in new window

0
slightwv (䄆 Netminder) Commented:
For 6 out of 22 columns, try creating an index on all 6 columns:

create index tab2_six_col_idx on tab2(col1, col2, col3, col4, col5, col6);
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.