 Perl DBI: Check is item exist and performance [message #299475] |
Tue, 12 February 2008 03:13  |
wakula
Messages: 150
Registered: February 2008
Location: Poland
|
Senior Member |
|
|
Hello,
I have some small project where my goal is to parse some input data and then put the output to a table in Oracle database.
However I should not add the item if the same item already exists.
Additionally: character case should be ignored when checking if it already exists.
Currently I am doing this like bellow.
Is this "the correct way" or should I make this differently to increase the performance?
1. Calculate data to add
2. Query the database:
$sth->prepare('SELECT (NON_UNIQUE_ID,NON_UNIQUE_TYPE,NON_UNIQUE_VALUE) FROM NON_INDEXED_TABLE WHERE UPPER(NON_UNIQUE_ID)=UPPER(?) AND UPPER(NON_UNIQUE_TYPE) = UPPER(?) AND UPPER(NON_UNIQUE_VALUE) = UPPER(?)');
$sth->execute(@data_to_be_added);
3. Call fetchrow_array() once.
$sth->fetchrow_array;
4. If something is returned - don't add
if($sth->rows == 0) {
&add_data;
} else {
&data_already_exist_dont_add;
}
Thanks in advance
[Updated on: Tue, 12 February 2008 03:14] Report message to a moderator |
|
|
|
|
|
| Re: Perl DBI: Check is item exist and performance [message #299512 is a reply to message #299475] |
Tue, 12 February 2008 05:12  |
wakula
Messages: 150
Registered: February 2008
Location: Poland
|
Senior Member |
|
|
Is this OK?
Table T:
ID (NUMBER)
A (VARCHAR2(100B))
B (VARCHAR2(100B))
MERGE INTO T USING
(SELECT * FROM T WHERE rownum = 1)
ON (D.ID = 1 and D.A = 'xxx' and D.B = 'yyy')
WHEN NOT MATCHED THEN INSERT VALUES (1,'xxx','yyy')
How can I check if the value was already present and not inserted?
|
|
|
|
|
|
| Re: Perl DBI: Check is item exist and performance [message #299547 is a reply to message #299475] |
Tue, 12 February 2008 07:07 |
wakula
Messages: 150
Registered: February 2008
Location: Poland
|
Senior Member |
|
|
I am new to this so I'm afraid, that I didn't understand your instructions.
As I currently understand it:
MERGE INTO T - this tells what is the destination
USING (...) - this tells where to take the source data from (but I have no data yet in the database)
ON (...) - this tells when to do match or not-match procedure
WHEN NOT MATCHED (...) - this tells what to do if a match is done
And in the code given by me:
MERGE INTO T - tells where I want to put the data
USING (...) - will search the database for existing rows (1,'xxx','yyy')
ON (...) - will verify that the row is (1,'xxx','yyy')
WHEN NOT MATCHED (...) - this will be executed when row (1,'xxx','yyy') is not yet in the database
Please correct me if I am wrong somewhere here...
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
