-
Originally posted by chrisrlong
Which, as I said, is simply in the wrong order You don't want to insert the records *before* the update, or you will end up updating those very same records (as well as the others), right? You always do the update before the insert. My next post was only applicable if bulk statements could not be done, as I specified at the beginning of said post.
Ok now I get you, yes ofcourse Update then Insert should be performed, else unneccesary updates.
Originally posted by chrisrlong
Okay, so in my haste, I went a *little* too far, but not by much.
Code:
INSERT -- TEST02
INTO JUNK
SELECT * from JUNK WHERE PK = 1
INSERT -- TEST02
INTO JUNK
SELECT * from JUNK WHERE PK = 1
AND NOT EXISTS(SELECT 1 FROM JUNK WHERE PK = 1)
Compare the buffer gets on the two statements. The second one is much more efficient. Therefore, If more inserts:
INSERT WHERE NOT EXISTS
IF SQL%ROWCOUNT = 0
THEN
UPDATE...
However, the same does not appear to apply to updates
In this case, both statements run exactly the same.
So, if more updates, simply:
UPDATE ...
IF SQL%ROWCOUNT = 0
THEN
INSERT...
- Chris
Ultimate!!
Now, it wud be interesting to see Jurij's reply
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Now after additional explanation it makes much more sence to me. (Maybe the fact that I haven't yet had any beer today has something to do with that?)
However,
Originally posted by chrisrlong
Okay, so in my haste, I went a *little* too far, but not by much.
Code:
DROP TABLE JUNK
CREATE TABLE JUNK AS SELECT ROWNUM PK, T.* FROM ALL_OBJECTS T
CREATE UNIQUE INDEX JUNK_PK ON JUNK(PK)
ANALYZE TABLE JUNK COMPUTE STATISTICS
INSERT -- TEST02
INTO JUNK
SELECT * from JUNK WHERE PK = 1
INSERT -- TEST02
INTO JUNK
SELECT * from JUNK WHERE PK = 1
AND NOT EXISTS(SELECT 1 FROM JUNK WHERE PK = 1)
How many beers have you had, Chris? No, seriously, you were again a bit hasty with the above example, dont you think? The first select will always raise ORA-00001 "unique constraint violated" (I wonder how could you get logical reads from that select) and the second will never ever insert any rows, don't you think
Anyway, I agree with your conclusions.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Originally posted by jmodic
The first select will always raise ORA-00001 "unique constraint violated" (I wonder how could you get logical reads from that select) and the second will never ever insert any rows, don't you think
That's the point, actually. When you compare 2 successful inserts, adding an exists adds 1 LR. When comparing 2 that fail (and you can find both in the statement cache), adding the exists saves 13 LRs. This means that you would have to have 13 times as many inserts as updates for my method to lose, and this is with a small, simple table with a single index.
I wish I could find the code where I actually implemented this not too long ago - I know it save a good chunk of time but I can't find the bloody thing.
Oh well :(
- Chris
-
Originally posted by chrisrlong
That's the point, actually
No, I think you are missing my point, Chris! I don't object to your conclusions, I'm just saying that both selects that you've provided to demonstraint the effect on inserts are (excuse the expression) - braindead!
INSERT 1:
Code:
INSERT -- TEST02
INTO JUNK
SELECT * from JUNK WHERE PK = 1
With this, you are telling oracle to select a record from a table (that is unique index-ed) and without modifying that record to insert it in that same table again! You are violating the unique index so you'll get ORA-00001 - so what are you trying to show here? What is it good to compare the LRs for a query that will always return error message to any other query? To prove how many logical reads you need to get an ORA-00001?
INSERT 2:
Code:
INSERT -- TEST02
INTO JUNK
SELECT * from JUNK WHERE PK = 1
AND NOT EXISTS(SELECT 1 FROM JUNK WHERE PK = 1)
It's more than obvious that the query will never ever return any rows, so what's the point here? You could (logic-vise) as well written the above as:
Code:
INSERT -- TEST02
INTO JUNK
SELECT * from JUNK WHERE PK = 1
AND EXISTS(SELECT 1 FROM JUNK WHERE PK = 1)
AND NOT EXISTS(SELECT 1 FROM JUNK WHERE PK = 1)
or even more simpler:
Code:
INSERT -- TEST02
INTO JUNK
SELECT * from JUNK WHERE PK = 1
AND 1=2
Unfortunately the optimizer is not clever enough (at least not on 8i that I'm connected to just now) to find out from your original query itself that its WHEN clause can never result in TRUE, so indeed it makes some reads from the database. But it is clever enough to resolve this from my query (with "1=2" part in WHERE clause, which is logicaly totaly equivalent to yours) and doesn't even try to perform any database reads!
So I guess I'v just found the fastest insert statement possible - it doesn't nead a single logical read. However I don't know how one would use that statement in any comparison test as it is useles.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
I'm sorry. I must be explaining myself poorly. Bear with me.
I *know* that the statements I used failed. Again, that was the point. Assume we have a set of records in TableA that we want to INSERT or UPDATE in TableB in 8i. If we know that we usually have more records inserted than updated, we would essentially try an insert on each record, *and if that failed*, perform an update.
Now, this means that a certain percentage of inserts will succeed and a certain percentage will fail. I simply attempted to show that the standard INSERT/SELECT had, if I remember, 10 LRs for a successful INSERT and 15 for a failed INSERT. I then attempted to show that my version of INSERT/SELECT/WHERE NOT EXISTS had, if I remember, 11 LRs for a succesful INSERT and 2 for a failed INSERT.
To show that, I had to craft an INSERT statement of each type that failed.
I knew they failed.
I meant for them to fail
Again, that is because a certain number of the INSERTs *were going to fail*. I also had ones that didn't fail, so I could get stats on them as well. I also did the same thing with explicitely listed columns and various other permutations, etc. A more accurate set of statements would have involved 2 tables and some records that existed in both the Source and Destination versus ones that existed in the Source only and use bind variables in the statements, etc., but I was too lazy for that. And I didn't want to waste a lot of space with a lot of code when the premise could be shown simply with the statements I chose.
The premise being that waiting for a Unique Index to catch a duplicate record costs a lot more than adding a NOT EXISTS clause. The NOT EXISTS is an 'early out' scenario compared to the backend test of unique indexes.
So, my version costs slightly more for those records that didn't exist in TableB, but is LOTS cheaper for those records that ARE already in TableB, as some will be.
Did I make sense that time?
- Chris
Last edited by chrisrlong; 08-07-2003 at 10:01 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|