DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: sqlplus vs pl/sql

  1. #1
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639

    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

  2. #2
    Join Date
    Apr 2003
    Posts
    353
    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;

  3. #3
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    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.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    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?

  5. #5
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    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.

  6. #6
    Join Date
    Apr 2003
    Posts
    353
    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.

  7. #7
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    Wow!!! It worked!!!!!
    Thanks a lot dear engiri/davey mwuahhhh!

  8. #8
    Join Date
    Apr 2003
    Posts
    353
    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.

  9. #9
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    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

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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
  •  


Click Here to Expand Forum to Full Width