-
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
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.
Last edited by kris123; 04-13-2005 at 05:15 AM.
-
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;
Last edited by kris123; 04-13-2005 at 05:36 AM.
-
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
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
-
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
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
|