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