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

Pass the command same as the sql command and try.

Create two variables and generate the values there.
V_1:=p_surname||'%'||p_firstname||'%';
v_2:=p_firstname||'%'||p_surname||'%';

Run the changed sql.
select surname,firstname into x_surname,x_firstname from departure_all
where ((fullname like v_1) or
(fullname like v_2))
and departure_date>'31-DEC-04' and rownum<2;

Thanks dear,

But it is still doing full table scan:confused:

Im puzzled about the departure_date, why is it that when i remove it
the response is fast(in pl/sql mode), But while on the sqlplus mode
with or without the departure_date the performance is ok.

look at nls settings, you should really use to_date in the query as well (as you were told last time).

Why isnt there an index on that column?

Yes dear, i tried also ...
where departure_date>to_date('31-DEC-2004','DD-MON-YYYY')
and same poor performance i got.

I did not create an index on the departure_date coz of its low
cardinality and we only query by fullname. Its primary purpose
is to group the departure table by year.

What puzzled me is that does sqlplus and pl/sql have different
costbased optimizer interpretation?

I also observed that if i used the partition for searching
with the global indexed FULLNAME it will not use the index too.

example1: (this uses the global index FULLNAME)
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;

example2: (this does a full table scan)
select surname,firStname from departure_all partition(depart2005)
where ((fullname like 'TOM%WELLING%') or (fullname like 'WELLING%TOM%'))
and departure_date>'31-DEC-04' and rownum<2;

select surname,firstname into x_surname,x_firstname from departure_all
where ((fullname like v_1) or
(fullname like v_2))
and trunc(departure_date+0) >'31-DEC-04' and rownum<2

Flush your shared pool before this. I know this column is not having index. But just a try.

Wow!!! It worked!!!!!
Thanks a lot dear engiri/davey:) mwuahhhh!

May be genius dba's give explanation, which I dont know for
this behaviour.

But when same kind of situation when we faced, after long analysis,
I found some of these kind of things worked.

Well i guess in the pl/sql env, the optimizer is prioritizing the partitioned key as against the global index key:D

By the way would the logic be the same if i use
trunc(departure_date) as against trunc(departure_date+0)? Why add 0

But NOTE also that putting the TRUNC on date w/o the v_1 and
v_2 variables does not work also. lol...nice u got that idea too

thanks

Quote:

---

Originally posted by engiri
May be genius dba's give explanation, which I dont know for
this behaviour.

But when same kind of situation when we faced, after long analysis,
I found some of these kind of things worked.

---

In SQLPLUS he is using hard coded values and may be its going for index scans on fullname, but if u notice in PL/SQL block its a variable and oracle is not choosing for index scans but for partition full scans. when you put a trunc on that column you are actually forcing oracle to use index scans on the col Fullname..

suggestion : Collect stats on columns full name and departure_date.. may be it will help.

Abhay.

Quote:

---

Originally posted by kris123
By the way would the logic be the same if i use
trunc(departure_date) as against trunc(departure_date+0)? Why add 0
thanks

---

Pls try and feedback.
I have done more than fifty testing, when I had this problem, for me also the data is huge.

How are the partitions defined? I'm thinking that is must be range based, and that the predicate departure_date>to_date('31-DEC-2004','DD-MON-YYYY') would actually be better as departure_date>=TO_DATE('01-JAN-2005','DD-MON-YYYY')

This would be because your current predicate is not fully eliminating the possibility of there being some results required from the 2004 partition. How does that sound?

Hi dear, I guess thats a better option:)
The table is partitioned by range like...
TABLE DEPARTURE_ALL
PARTITION BY RANGE ("EXIT_DATE" )
(PARTITION "DEPART1993" VALUES LESS THAN (TO_DATE(' 1994-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PARTITION "DEPART1994" VALUES LESS THAN (TO_DATE(' 1995-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PARTITION "DEPART1995" VALUES LESS THAN (TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
...etc

Quote:

---

Originally posted by engiri
Pls try and feedback.
I have done more than fifty testing, when I had this problem, for me also the data is huge.

---

Yes dear, i guess adding 0 does the trick too. I tested
trunc(departure_date) only but after sometime it hanged, i dont know
if its just a coincidence only but when i change it to
trunc(departure_date+0) it runs smoothly:D