-
sqlplus vs pl/sql
Hi Friends,
I have a partitioned table DEPARTURE_ALL partitioned by
DEPARTURE_DATE and a global index on FULLNAME. The table have
50 million rows.
Then I created the following sqlplus code and
run against it.
select surname,firStname from departure_all
where ((fullname like 'TOM%WELLING%') or (fullname like 'WELLING%TOM%'))
and departure_date>'31-DEC-04' and rownum<2;
The response time is fast for the above code. But when I translated it to pl/sql
code which is the following:
select surname,firstname into x_surname,x_firstname from departure_all
where ((fullname like p_surname||'%'||p_firstname||'%') or
(fullname like p_firstname||'%'||p_surname||'%'))
and departure_date>'31-DEC-04' and rownum<2;
It took very long time, and it is doing a full table scan.
Is there something wrong with my program translation?
I try removing the condition [departure_date>'31-DEC-04'] and
the program works fine. What is wrong with this partitioned key pls?
Thanks a lot
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|