![]() In this syntax, you need to specify the user that you want to drop after the DROP USER keywords. L_ddl:=dbms_metadata.fetch_clob(l_myhandle) Ģ 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18ĭbms_t_filter(l_myhandle, 'SYSTEM_GENERATED', true) ĬREATE UNIQUE INDEX "SYS"."SYS_C0088374" ON "SYS".Code language: SQL (Structured Query Language) ( sql ) L_transhandle:=dbms_metadata.add_transform(l_myhandle, 'DDL') L_myhandle := dbms_metadata.open('INDEX') ĭbms_t_filter(l_myhandle, 'SYSTEM_GENERATED', false) ĭbms_t_filter(l_myhandle, 'BASE_OBJECT_SCHEMA', user) ĭbms_t_filter(l_myhandle, 'BASE_OBJECT_NAME', 'TESTTAB2') SQL> alter table testtab2 modify (a primary key) SQL> create table testtab2 (a number, b number) Seems this property can be checked through dbms_metadata.Ĭode is based on example from “Expert Indexing in Oracle Database 11g”: SQL> - but the output is: No, not both PK constraints get droppedĬomment by Martin S. SQL> alter table scott.test2 drop primary key SQL> alter table scott.test1 drop primary key OBJECT_NAME OBJECT_ID BITAND(IND$.PROPERTY,4096) PROPERTY SQL> select do.object_name, do.object_id, bitand(ind$.property,4096), ind$.propertyĥ and do.object_name in ('TEST1_PK','TEST2_PK') SQL> - get dropped when the PK constraint gets dropped SQL> - according to your posting ind$.property would make think that both indexes SQL> - two test tables - index created at same time as constraint is createdģ, constraint test2_pk primary key (id) using indexĤ (create index scott.test2_pk on scott.test2 (id)) The following testcase let’s me think that your conclusion might not be fully right. If both are non-zero then it’s an index that will be dropped if it’s protecting a primary key or unique constraint and you drop or move the constraint:ĭon’t forget to check the possible performance implications of running this query on a database with a very large data dictionary. It has to be run by SYS, and it references a schema and set of indexes by name (so it’s far from generic) and reports the two critical bits. Here’s a little bit of SQL I hacked together while I was checking the behaviour of modifying a primary key to “move” it to a different index. The exact SQL for the check is left as an exercise to the reader – you will probably want to join ind$ to obj$ to user$.įootnote: this particular bit of the propertycolumn doesn’t seem to be exposed in any of the data dictionary views but if anyone knows of any other way to see it then I’d be interested to hear about it, especially if you don’t have to have access to the sys schema to see it. (But see comment 3 and the subsequent reply below: the test should almost certainly be bitand(ind$.property,4097) = 4097 ) If the predicate evaluates to true the index will be dropped. So it would appear that if we want to know if an index would be dropped as the constraint was dropped, we need only check to see if bitand(ind$.property,4096) = 4096. (For the less mathematically inclined, 0x1000 = decimal 4096). ![]() In the first case (which generated the index automatically) ind$.property held the value 4097 in the second case, where I created the index explicitly, it held the value 1. ![]() This looks promising, so let’s try a few experiments to see what happens in a few cases of creating indexes and constraints – here’s a starting sample:Īlter table t1 add constraint t1_pk primary key (n1) * The index was created by a constraint : 0x1000 */ Given this clue, the obvious next step is to look in sql.bsq (or dcore.bsq if you’re running 11g) to see what it says about column ind$.property, and we find the following comment: First, run a trace while dropping a primary key constraint and see if this gives you any clues on an instance running 10gR2 I noticed the following statement appearing in the trace file immediately after the delete from cdef$ (constraint definitions). So here’s my best theory so far – along with the observations that led to it. The answer is yes – after all, Oracle has to make a decision somehow, so if we can find out how it makes the decision we can predict the decision. Someone asked me recently if it was possible to tell whether or not an index would be dropped without having to find out the hard way by dropping the constraint. If you drop a unique or primary key constraint the index that supports it may be dropped at the same time – but this doesn’t always happen.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |