I have lots of materialized views, and I routinely define a primary key on each.
(It ’ s a table; a table should have a primary key.)
I can ’ t find a way in TOAD to have it generate a complete script for
such an object. If I generate the script from the MV tab of the Schema Browser,
I can ’ t get the primary key (the most I can get is a CREATE UNIQUE
INDEX); if I generate the script from the Table tab, I get the primary key, but
of course I don ’ t get the MV query.
Is there a way?
Nate Schroeder
US Seed & Trait Commercial IT - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167
314-694-2592
This e-mail message may contain privileged and/or confidential information, and
is intended to be received only by persons entitled to receive such information.
If you have received this e-mail in error, please notify the sender immediately.
Please delete it and all attachments from any servers, hard drives or any other
media. Other use of this e-mail by you is strictly prohibited.
All e-mails and attachments sent and received are subject to monitoring, reading
and archival by Monsanto, including its subsidiaries. The recipient of this
e-mail is solely responsible for checking for the presence of “Viruses” or other
“Malware”. Monsanto, along with its subsidiaries, accepts no liability for any
damage caused by any such code transmitted by or accompanying this e-mail or any
attachment.
Hmmm – interesting problem. I hadn ’ t thought of that.
You could, of course, ignore that problem and provide the option anyway . The
worst case then is that someone runs the generated script and gets an error
“ duplicate constraint name ” or “ this table already has a
primary key ” or some such when the Alter … Create … Primary
Key is executed.
Without that option, the worst case happens when we generate a script using
Toad, drop the object, recreate the object using the Toad-generated script, and
now have a different object than we started with – and there ’ s no
message of any sort to tell us that we don ’ t have what we started with.
Personally, I ’ m a fan of more information rather than less; so I ’
d rather have an error message when there isn ’ t a problem, than to have
no message when there is a problem. Others may look at it differently.
(Actually, wouldn ’ t the Create Unique Index that the script generator
generat es now, give an error just like a Create Primary Key would give, in the
case where Oracle automatically creates the PK? I tried to test this, but I
couldn ’ t persuade Oracle to create a primary key automatically. )
Nate Schroeder
US Seed & Trait Commercial IT - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167
If I’m not mistaken – and that’s a real possibility
considering my brain is telling me it should be vacation time – then
Oracle behaves as follows:
If you define a constraint indicating primary key or uniqueness, then an index
is automatically created. On definition of an index however, no constraint is
created.
It makes sense to me to behave that way… a constraint inherently indicates
an index should exist, but an index existing does not necessarily imply a
constraint. However… I have zero arguments why that should be that
way… not a surprise… it’s a good thing I’ve got an extra
long weekend coming up
If I can find a way to differentiate between an automatically
generated and manually generated PK, I can add support for this.
You could use Oracle’s naming convention of SYS_% to identify manual vs
non-manual. Then it’d be up to the end-user not to use such a cryptic
naming convention
That’s actually not a bad idea. I just took a look at a table with a
generated constraint/index:
CREATE TABLE tmp_tab (tt_index NUMBER PRIMARY KEY, tt_value VARCHAR2(1));
Oracle auto-created a constraint and index. The names are:
Constraint: SYS_C00307868
Index: SYS_C00307868
So… if you find an index of Oracle’s naming convention and it has a
matching constraint… or… even when you specify a constraint name, a
matching named index is created. A possibility to consider if you haven’t
already
I was playing with this some more…my first email was incorrect. Oracle
does not add PKs on MVs automatically, just because the underlying table has a
primary key.
Arg! Dagnabbit! Ignore my prior email. My first one was correct. Roger, try
this. There’s no way to tell that the constraint on TEST_MV was created by
Oracle. Oracle even gives it a nice name. But, as Nate mentioned a while ago,
the worst thing that’s going to happen if I add the constraint DDL to the
script is an ‘object already exists’ error.
create table test ( col1 number , col2 number );
alter table test add constraint pk_test primary key ( col1 );
create materialized view test_mv as select * from test;
Weird – Oracle is choosy about it! If you follow your script with
create materialized view test_2_mv as select * from test_mv ;
select * from user_constraints
where table_name in ( ‘TEST’ , ‘TEST_2_MV’ , ‘TEST_MV’ );
you see that Oracle does not automatically create a PK on test_2_mv, even though
it’s constructed almost identically to the way test_mv was constructed.
Maybe because test_2_mv is selecting from a table that is an MV, rather than
from a table that is not an MV??
Nate Schroeder
US Seed & Trait Commercial IT - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167
Blah! Unfortunately I can’t test quite that far. As a lowly developer,
I’m not allowed to create a materialized view.
So… I’ll leave my 2 cents at the value it really was (.0002 cents)
as opposed to the value it was meant to be and curiously follow any further
discussion without being able to contribute
Just got back in town from some travel. The issue that people were missing in
this discussion is:
if the materialized view uses primary keys (the default if a PK is defined
and it's a simple MV) then Oracle will create a primary key (named like the
original PK if possible).
if the materialized view uses rowid, then Oracle will create its own index
on rowid, but will not create a primary key.
If you’re in the second situation and you create a primary key, then it’s easy
to get in trouble. If any data comes along in a refresh that violates that PK
the refresh will just fail. And a complete refresh will fail and leave the MV
completely empty.
For both the Oracle-created and manual PK, be really careful about pointing an
FK at that primary key – you won’t be able to do a complete refresh because
that requires Oracle to empty the table which will violation your FK. Most
critically (I’ve done this), don’t have an FK pointing at your materialized view
that does a cascade delete.
Yes, James, you have some good points. Our MVs that have PKs are generally
refreshed by the following strategy: drop the PK; refresh the MV (complete
refresh, by the way, since the MVs pretty much all involve joins); and re-create
the PK. In most cases we’re guaranteed uniqueness of the PKs by virtue of
the PKs of the source tables; in the other cases, if our assumptions about data
uniqueness get violated, the MV is still refreshed and has the current data, and
only the “create PK” fails. And you’re right, I’d like
to be able to define referential integrity using these keys but I can’t.
Nate Schroeder
US Seed & Trait Commercial IT - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167
… naturally, what is your reward for having done something good? Being
asked to do something else. The script now creates a unique index and a primary
key constraint. Can you add, as you currently have on the Tables option screen,
the “No constraint indexes” checkbox under “Indexes”?
Nate Schroeder
US Seed & Trait Commercial IT - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167
