Re: This same query - diffrerent time to execute...

I see a spare letter got into my post - the "and index" wasn't intended as an instruction to create an index, it should have read
"disabling an index".

I won't ask how strange the results get on BINARY, but the scale of the difference, and the NLS_LANGUAGE feature do suggest that the problem is that one setting uses an index and the other doesn't.

First confirm this by re-running the query with the two difference settings in turn but:

    set autotrace traceonly explain
first.
(If you haven't previously created a plan_table you will also need to execute
$ORACLE_HOME/rdbms/admin/utlxplan.sql

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



Ariel wrote in message ...


>> It would be appropriate to tell us how much difference -

>> 3 seconds to 15 minutes is worth looking at

>> 0.5 seconds to 0.51 seconds is not.

>

>OK.

>On "ansi" time is ~5 sec.

>On "binary" time is ~1.5 sec.

>

>> First step in your investigation - check the execution path.

>> It is possible that by forcing Oracle to do ANSI comparison

>> instead of binary comparison you are effectively disabling

>> and index which helps to accelerate the BINARY path.

>

>I use NLS_LANGUAGE=Polish

>and when I set NLS_COMP=binary I get strange results on

>results using "special - polish" letters.

>

>I try to use

>CREATE INDEX pl_i1 ON table(NLSSORT(col, 'NLS_SORT=Polish')),

>but does'nt help this.

>

>

>

>