Explain Plan between 2 Queries
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Explain Plan between 2 Queries

  1. #1
    Join Date
    Oct 2000
    Posts
    449
    Oracle 817 on Unix Rs/6000

    Table has Non Unique index on both columns selected

    This is only an extract from a cursor..

    SELECT /*+ index prdmstee.prdmsteei2 */
    prd_name_full
    FROM prdmstee
    WHERE trim(prd_lvl_number) = '123123'
    and prd_lvl_id =0;

    Explain Plan results as
    select stmnt HINT=Choose, rows 130, cost=93
    Table Access full, obj prdmstee, rows 130, bytes 6k, rows 130

    When I change the query a little bit to NOT using trim function, then it is very good..

    Select stmnt Hint=Choose, rows 1, cost 2
    - Table Access by index rowid prdmstee rows1 bytes51 cost2
    --- Index range scan prdmsteei2 rows1 cost1

    I have to use the trim function, since it is 2 different interfaces results I am comparing and the field is a charecter and for few thousands of rows. It is taking very very long time. The programmer is looking for some alternate solution. What can make this better?

    Thanks, ST2000

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    You need to create a function based index for trim(prd_lvl_number)
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Plus, the syntax of your hint is wrong. /*+INDEX(prdmstee prdmsteei2) */
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Oct 2000
    Posts
    449
    SELECT /*+ index(prdmstee prdmsteei2) */
    prd_name_full
    --select prd_name_full
    FROM prdmstee
    WHERE trim(prd_lvl_number) = '123123'
    and prd_lvl_id =0

    select statement hint=choose rows 130 cost 458
    -- table access by index rowid rows 130 bytes 6k cost 458
    ---- index fullscan rows 130 cost 458

    I guess the cost has gone up tremendously with the correct index. I checked the wrong one.. and it was showing same results for not mentioining any hint.. Can you please suggest further.. Can u give me more insight on function based index. What is it and how can it be done?

    Thanks, ST2000

  5. #5
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    You create an index on the predicate you are using in the where clause. See the link below.

    http://otn.oracle.com/docs/products/...dexes.htm#2943
    Reddy,Sam

  6. #6
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    for function based index with experssion :

    TRIM(field_name)

    u MUST have db blocksize = 8K (no less)
    because TRIM create intermediate result with 2k chars length.

    if u have 2-4K db block u have to use expression:

    substr(TRIM(field_name) ,1, 255)

    because SUBSTR create intermediate result with LLL chars length.
    where in this case lll=255 bytes.

  7. #7
    Join Date
    Oct 2000
    Posts
    449
    What does the cost in explain plan actually mean?

    I was hoping, higher the cost, higher the time it takes. But my user is saying that with the cost high, sometimes the query seems to run faster.

    After making the index (creating function based index or fbi), should i still use trim(field_name) along with the hint to use the fbi.... in my query.. Somehow the results were not impressive with fbi and trim(fieldname) and hint Vs using trim alone and doing full tablescans..

    The alternate way was to simply update the referencing table with trim(fieldname) and committing and running the process.. I still have to find out answers for my user..

    Thanks, ST2000

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    never ever trust cost, it is used internall by Oracle not by us

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