Home » Open Source » Programming Interfaces » Perl DBI: Check is item exist and performance
icon7.gif  Perl DBI: Check is item exist and performance [message #299475] Tue, 12 February 2008 03:13 Go to next message
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 #299492 is a reply to message #299475] Tue, 12 February 2008 03:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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 Go to previous messageGo to next message
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 #299541 is a reply to message #299512] Tue, 12 February 2008 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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 Go to previous messageGo to next message
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...
Re: Perl DBI: Check is item exist and performance [message #299552 is a reply to message #299547] Tue, 12 February 2008 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior 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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior 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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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 Go to previous message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior 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.
Previous Topic: why date field type change into datetime using odbc connect to oracle XE
Next Topic: Error while trying to retrieve text for error ORA-01019
Goto Forum:
  


Current Time: Fri Mar 29 07:01:08 CDT 2024