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 2595 times)
|
|
|
Goto Forum:
Current Time: Thu Feb 20 20:56:59 CST 2025
|