Home » Open Source » Programming Interfaces » PERL - bind_param (Oracle 10.2.0.3.0)
PERL - bind_param [message #295728] Wed, 23 January 2008 03:45
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
I'm a bit of a newbie when it comes to perl, so maybe I've overlooked something obvious.

Let me describe the situation: I got an input-file with about 7.000.000 records. Each record contains a number, but they are not necessarily numeric. It uniquely identifies a company.

CREATE TABLE CMP_NUM_IDX
(
  CMP_ID      NUMBER(9) NOT NULL,
  NUM_CD      CHAR(4 CHAR),
  NUM_IDX_TX  VARCHAR2(200 CHAR)
)
CREATE UNIQUE INDEX CMP_NUM_IDX_PK ON CMP_NUM_IDX (CMP_ID)
/
CREATE INDEX CMP_NUM_IDX_1 ON CMP_NUM_IDX
(NUM_IDX_TX,NUM_CD,CMP_ID)
/


In the perl-code there's these two SELECT-statements:
 SELECT cmp_id FROM cmp_num_idx WHERE num_idx_tx = ?

 SELECT cmp_id FROM cmp_num_idx WHERE num_idx_tx = TO_CHAR(?)


When using the first one, TOAD's explain plan on the statement tells me it is using an INDEX FULL SCAN INDEX. And each SELECT takes about 1 minute to complete

When using the second one, than plan changes to INDEX FAST SCAN and is much faster.

My guess is that a small change to the bind_param statement will make the first query as fast as the second one. But how?

 $sth->bind_param( 1 , $line ) ;


I've been googling around quite awhile now, but with no luck.
I hope some of you guys know the answer and is willing to share.

No need to start googling around for my part.
  • Attachment: perscript.pl
    (Size: 3.49KB, Downloaded 2030 times)
Previous Topic: Oracle and Ruby On Rails
Next Topic: help with Perl code to insert a long string (> 32512 chars) via stored procedure
Goto Forum:
  


Current Time: Fri Mar 29 10:22:36 CDT 2024