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.
] 
Blog
Search
Help
Members
Register
Login
Home
