Explain Plan between 2 Queries

[st2000]

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

[marist89]

You need to create a function based index for trim(prd_lvl_number)

[marist89]

Plus, the syntax of your hint is wrong. /*+INDEX(prdmstee prdmsteei2) */

[st2000]

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

[sreddy]

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

[Shestakov]

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.

[st2000]

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

[pando]

never ever trust cost, it is used internall by Oracle not by us