performance with INSERT/EXCEPTION/UPDATE pattern

[chrisrlong]

Originally posted by abhaysk
Chris :
I didnt get what you wanted to tell, but lemme explain what I thought

SQL%ROWCOUNT is not at all needed...(yes if using cursors, but not needed in bulk loads)

jus see below eg

Insert into X select %Attribute_List% From Y 
Where Not Exists (Select 1 From X Where PK_Of_X=Cols_Of_Y ( Or Sub Query ) )
;

Update X Set (COLx1,COLx2,...) = (Select %Attribute_List% From Y Where Cols_Of_Y ( Or Sub Query )=PK_Of_X)
Where Exists
(Select 1 from Y Where Cols_Of_Y ( Or Sub Query )=PK_Of_X)
;

Abhay.

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.

Originally posted by jmodic
I swear I've only had one beer today, but I'm totaly lost here. I'm not sure what are you talking about here.

You are talking about update/insert of a record by record inside cursor, right? If so, why checking for existance at all? I mean, ROWCOUNT will tell if it exists or not, no?

You are not talking about the same concept that slimdave has suggested, are you? Or have I totaly and 100% miss some important point here?

I realy am confused.....

Okay, so in my haste, I went a *little* too far, but not by much.

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)

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

UPDATE -- TEST06
JUNK	J
SET 
	(PK,	OWNER,	OBJECT_NAME,	SUBOBJECT_NAME,	OBJECT_ID,
	DATA_OBJECT_ID,	OBJECT_TYPE,	CREATED,	LAST_DDL_TIME,
	TIMESTAMP,	STATUS,	TEMPORARY,	GENERATED,	SECONDARY
	)	=
	(	
	SELECT
		PK,	OWNER,	OBJECT_NAME,	SUBOBJECT_NAME,	OBJECT_ID,
		DATA_OBJECT_ID,	OBJECT_TYPE,	CREATED,	LAST_DDL_TIME,
		TIMESTAMP,	STATUS,	TEMPORARY,	GENERATED,	SECONDARY
	FROM
		JUNK
	WHERE
		PK	=	J.PK
	)
WHERE
	PK	=	100000

UPDATE -- TEST06
JUNK J
SET 
	(PK,	OWNER,	OBJECT_NAME,	SUBOBJECT_NAME,	OBJECT_ID,
	DATA_OBJECT_ID,	OBJECT_TYPE,	CREATED,	LAST_DDL_TIME,
	TIMESTAMP,	STATUS,	TEMPORARY,	GENERATED,	SECONDARY
	)	=
	(	
	SELECT
		PK,	OWNER,	OBJECT_NAME,	SUBOBJECT_NAME,	OBJECT_ID,
		DATA_OBJECT_ID,	OBJECT_TYPE,	CREATED,	LAST_DDL_TIME,
		TIMESTAMP,	STATUS,	TEMPORARY,	GENERATED,	SECONDARY
	FROM
		JUNK
	WHERE
		PK	=	J.PK
	)
WHERE
	PK	=	100000
AND NOT EXISTS(SELECT 1 FROM JUNK WHERE PK	=	J.PK)

In this case, both statements run exactly the same.
So, if more updates, simply:

UPDATE ...
IF SQL%ROWCOUNT = 0
THEN
INSERT...

- Chris