 Perl DBI: Check is item exist and performance [message #299475] |
Tue, 12 February 2008 03:13  |
wakula
Messages: 18
Registered: February 2008 |
Junior 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]
|
|
|
| Re: Perl DBI: Check is item exist and performance [message #299492 is a reply to message #299475 ] |
Tue, 12 February 2008 03:54  |
Michel Cadot
Messages: 16994
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
Use MERGE statement.
Regards
Michel
|
|
|
| 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: 18
Registered: February 2008 |
Junior 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 #299541 is a reply to message #299512 ] |
Tue, 12 February 2008 06:33 |
Michel Cadot
Messages: 16994
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
You don't have to check it, Oracle does it for you.
I don't understand your "SELECT * FROM T WHERE rownum = 1", use also T and join with your matching conditions (what determines if the row is already there or not).
You can also add a unique key and try to insert ignoring the ORA-00001 error.
Regards
Michel
|
|
|
| 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: 18
Registered: February 2008 |
Junior 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...
|
|
|
| Re: Perl DBI: Check is item exist and performance [message #299552 is a reply to message #299547 ] |
Tue, 12 February 2008 07:22 |
Michel Cadot
Messages: 16994
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
Where does your data comes from?
If it is not already in database (or in flat file) then you can't use MERGE.
Regards
Michel
|
|
|
| Re: Perl DBI: Check is item exist and performance [message #299557 is a reply to message #299475 ] |
Tue, 12 February 2008 07:37 |
wakula
Messages: 18
Registered: February 2008 |
Junior Member |
|
|
INPUT: some .CSV file
OUTPUT: existing Oracle database with some existing data
Data to be added: CSV after some parsing.
|
|
|
| Re: Perl DBI: Check is item exist and performance [message #299562 is a reply to message #299557 ] |
Tue, 12 February 2008 07:47 |
Michel Cadot
Messages: 16994
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
So MERGE into already existed table USING external table pointing to your csv file USING your matching conditions.
Easy.
Regards
Michel
|
|
|
| Re: Perl DBI: Check is item exist and performance [message #299566 is a reply to message #299475 ] |
Tue, 12 February 2008 07:52 |
wakula
Messages: 18
Registered: February 2008 |
Junior Member |
|
|
|
The CSV fiel is read using Text:CSV, not DBD:CSV. So how can I point to the CSV as a source?
|
|
|
| Re: Perl DBI: Check is item exist and performance [message #299580 is a reply to message #299566 ] |
Tue, 12 February 2008 08:20 |
Michel Cadot
Messages: 16994
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
Don't read the file with Perl, let Oracle read it for you using an external table (but this implies the file is in the instance server).
Regards
Michel
|
|
|
| Re: Perl DBI: Check is item exist and performance [message #299581 is a reply to message #299475 ] |
Tue, 12 February 2008 08:23 |
wakula
Messages: 18
Registered: February 2008 |
Junior Member |
|
|
|
Thanks or your help Michel. Although I will probably leave this as it is currently - there is too much that I need to learn about Oracle DB before doing this.
|
|
|
]
Members
Search
Help
Register
Login
Home
