| Oracle problems in split partition [message #148141] |
Tue, 22 November 2005 22:04  |
war123
Messages: 8
Registered: November 2005
|
Junior Member |
|
|
Please help me
I have a need to split partition when insert new row. So I do it in Before insert trigger.
CREATE TABLE TempTable
(
ID NUMBER (12) NOT NULL
)
PARTITION BY RANGE (ID)
(
PARTITION TempTable_R VALUES LESS THAN (MAXVALUE)
)
CREATE OR REPLACE TRIGGER TempTableBefInsTrigger
BEFORE INSERT ON TempTable
REFERENCING NEW AS newRow
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
Insert_PROC(:newRow.ID);
END TempTable;
CREATE OR REPLACE PROCEDURE Insert_PROC (ID NUMBER) AS
PRAGMA AUTONOMOUS_TRANSACTION;
msgResult NVARCHAR2(1000);
sqlString VARCHAR2(1000);
BEGIN
sqlString := 'ALTER TABLE TempTable ADD PARTITION TempTable_R AT (ID) INTO ( PARTITION TempTable_' || ID || ', PARTITION TempTable_R)';
BEGIN
execute immediate sqlString;
EXCEPTION
WHEN OTHERS THEN msgResult := SQLERRM;
dbms_output.put_line('Error TempTable table: ' || msgResult);
END;
END;
If I insert:
INSERT INTO TempTable VALUES(1);
COMMIT;
INSERT INTO TempTable VALUES(100);
COMMIT;
-> Split OK for all;
But If I insert:
INSERT INTO TempTable VALUES(1);
INSERT INTO TempTable VALUES(100);
COMMIT;
Error: ORA-00054: resource busy and acquire with NOWAIT specified
So, how do I can split partition any time when i insert new record.
Thanks
|
|
|
|
| Re: Oracle problems in split partition [message #148170 is a reply to message #148141] |
Wed, 23 November 2005 00:14  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
A DDL operation (ALTER, DROP, etc) on a table tries to lock the ENTIRE table in exclusive mode. The INSERT statement will lock the table in SHARE mode, so you cannot get the EXCLUSIVE lock you need. Even if you could lock it, you would get a mutating table error.
Upshot: you can't do what you're trying to do. Which is a good thing, because it's a horrible (yet un-nervingly interesting)concept. If you have a table big enough to be partitioned then you don't want a trigger on it with that sort of overhead anyway, because there are going to be tons of inserts.
However, if you choose to press on regardless, there are two things you could try.
1. You could use the FOR EACH ROW trigger to temporarily store the rowids in a nested table declared in a PACKAGE SPEC (persistent within the session), and then try to split the partition in an after-statement trigger (ie. no FOR EACH ROW clause) by reading the data in the nested table and taking action as required. You will probably come across the same locking problem though, so this will probably be a wash-out.
2. You could create a view as SELECT * FROM TempTable, and insert into the table via the view. Rather than letting Oracle handle the INSERTs, write an INSTEAD OF INSERT trigger to do it manually. The INTEAD OF INSERT trigger would insert the row, commit, then perform the SPLIT.
I don't know how this will go - commits in triggers seem like a bad idea. But I've never tried it, so it might just work.
Let me re-iterate - this whole thing is a terrible idea. I stongly recommend you do not proceed down this path. There is something fundamenally wrong with a design that requires this kind of approach.
I only provide the ideas above because what you have proprosed is an interesting theoretical question - not because I want to see you go for it.
_____________
Ross Leishman
|
|
|
|
| Re: Oracle problems in split partition [message #148201 is a reply to message #148141] |
Wed, 23 November 2005 01:17 |
war123
Messages: 8
Registered: November 2005
|
Junior Member |
|
|
Thanks for reply.
You could use the FOR EACH ROW trigger to temporarily store the rowids in a nested table declared in a PACKAGE SPEC (persistent within the session), and then try to split the partition in an after-statement trigger (ie. no FOR EACH ROW clause) by reading the data in the nested table and taking action as required. You will probably come across the same locking problem though, so this will probably be a wash-out.
Can you explain more it for me?
I have 4 SQL command:
ALTER TABLE TempTable ADD PARTITION TempTable_R AT (10) INTO ( PARTITION TempTable_1, PARTITION TempTable_R);
INSERT INTO TempTable VALUES(1);
ALTER TABLE TempTable ADD PARTITION TempTable_R AT (100) INTO ( PARTITION TempTable_2, PARTITION TempTable_R);
INSERT INTO TempTable VALUES(50);
COMMIT;
-> That's OK too.
How it is difference with Split and insert in a trigger?
|
|
|
|
| Re: Oracle problems in split partition [message #148367 is a reply to message #148201] |
Wed, 23 November 2005 16:47 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Are those ADDs in your example are supposed to be SPLITs?
The difference between your example and the trigger, is that the trigger doesn't work  For the reasons I described above.
The way you are doing it with 4 statements is still not a great idea, unless there are many thousands of INSERT between each SPLIT. Even then, its not such a hot idea.
The thing about partitions is that you shouldn't be mucking about with them in application code; they should ideally be left up to the DBA. Most applications would create the partitions they need without a MAXVALUE partition, and then create new partitions above the previous highest value as required.
Adding a partition above the previous highest value is fast. Splitting an existing partition means a lot of IO - ie. v.slow.
If you are splitting partitions, it means that they were not sized correctly in the first place. Sizing (and managing segment sizes) should be done in conjunction with the DBA when a table is deployed, not re-sized arbitrarily on the fly.
I see that you are eager to pursue this despite this advice (and that's your perogative, I'm not the boss of you ). If you want to know more about the statement-level trigger possibility (remember, I don't even know if it would work), then see here.
_____________
Ross Leishman
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
