Hey,
I have a large table 600 million rows, approximately 70gb, not partitioned (yet), a few indices, etc.
I have reports that query this table often but they're very slow (20-60 minutes at times). I want to create this materialized view but I can't because my base table doesn't have a primary key
This is what I want to do:
CREATE MATERIALIZED VIEW LOG ON LLATTRDATA;
CREATE MATERIALIZED VIEW MV_LLATTRDATA_TEST1
NOLOGGING
CACHE
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT *
FROM LLATTRDATA D
WHERE
(D.DEFID = 3070056 AND D.ATTRID IN (2, 3, 4)) OR
(D.DEFID = 3070055 AND D.ATTRID IN (3, 30, 31, 2, 24, 23, 4)) OR
(D.DEFID = 3071379 AND D.ATTRID IN (3, 5, 8)) OR
(D.DEFID = 3072256 AND D.ATTRID = 5);
BEGIN
DBMS_STATS.gather_table_stats(
ownname => 'CHEACOCK',
tabname => 'MV_LLATTRDATA_TEST1');
END;There's an ID field but it's not unique and no other column would server well as a Primary Key.
So I was thinking of using SYS_GUID on a new column and declaring that the Primary Key:
ALTER TABLE LLATTRDATA ADD PKID RAW(16);
ALTER TABLE LLATTRDATA ADD CONSTRAINT PK_LLATTRDATA PRIMARY KEY(PKID);
UPDATE LLATTRDATA SET PKID = SYS_GUID()
Would this make sense or is there some considerations I'm not taking into account?
I'd be perhaps more tempted to move toward a partitioning model before heading down the mview path. I don't have enough information to make a recommendation, but *for example*, hash partitioning on DEFID might give sufficient data distribution and granularity without adding columns or mviews.
As as aside, you don't want to be updating 600m rows. It would take a long time, and you'd probably need to perform a reorganization of the table afterwards anyway because every row will grow in size - you might get a big row migration performance issue.
You would probably use DBMS_REDEFINITION to do the work, which minimizes any outage, eg
SQL> create table t as select * from dba_objects where object_id is not null;
Table created.
SQL> exec dbms_redefinition.can_redef_table(user, 'T', dbms_redefinition.cons_use_rowid);
PL/SQL procedure successfully completed.
SQL>
SQL> create table t2 as
2 select *
3 from t where 1=2;
Table created.
SQL>
SQL> alter table t2 add pk raw(16) default sys_guid();
Table altered.
SQL>
SQL> exec dbms_redefinition.start_redef_table(user, 'T', 'T2',options_flag=>dbms_redefinition.cons_use_rowid);
PL/SQL procedure successfully completed.
SQL>
SQL> alter table t2 add (constraint t2_pk primary key (pk));
Table altered.
SQL>
SQL> exec dbms_redefinition.finish_redef_table(user, 'T', 'T2');
PL/SQL procedure successfully completed.
You can also use the same technique to partition the table without downtime.