Re: Bind Variables ignoring Index on 10.2.0.4

"Pat" <pat.casey_at_service-now.com> schreef in bericht news:02a7a4df-73f5-4aef-82bb-b58686d53f3b_at_8g2000hsu.googlegroups.com...
>
>>
>> With the risk of making a stupid remark (I don't know anything about
>> NLS_SORT in create_index):
>> Is there any reason why you do
>> (NLSSORT("sys_id",'nls_sort=''BINARY_CI'''))
>> (double quotes, case DOES matter here)
>> and not
>> (NLSSORT(SYS_ID,'nls_sort=''BINARY_CI'''));?? (no double quotes, case
>> doesn't matter now)
>> To my knowledge (but again...) a column name in lower char and double
>> quotes
>> does not resolve to the actual column.
>>
>> I may be wrong, but your remark about a typo made me wonder....
>>
>> BTW Same goes for "BINARY_CI": why those double quotes?
>>
>> Shakespeare
>
> No worries at all about pointing out a stupid error on my part.
> Wouldn't be the least bit shocked if that isn't the underlying
> problem, and if it is I'll throw a party celebrating my own stupidity
> as long as it fixes the problem :).
>
> The reason sys_id is being quoted is that the actual column name is
> (lower case) sys_id.
>
> This is an old mySQL app that got ported to Oracle and the column
> names are all lower cased there since thats "more or less" the norm in
> mySQL land.
>
> Hence when it came to oracle, the column names were, unfortunately
> still lower cased, and hence the necessity of quoting all references
> to them, which makes working with them on the CLI rather obnoxious.
>
> SQL> describe sys_trigger;
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> sys_id NOT NULL CHAR(32)
> name VARCHAR2(40)
> trigger_class VARCHAR2(40)
> state NUMBER(38)
> next_action TIMESTAMP(6)
> document VARCHAR2(40)
> document_key CHAR(32)
> job_id CHAR(32)
> job_context VARCHAR2(4000)
> calendar CHAR(32)
> log NUMBER(1)
> maintenance NUMBER(1)
> trigger_type NUMBER(38)
> repeat TIMESTAMP(6)
> run_time TIMESTAMP(6)
> run_dayofweek NUMBER(38)
> run_dayofmonth NUMBER(38)
> run_weekinmonth NUMBER(38)
> run_month NUMBER(38)
> application VARCHAR2(40)
> script CLOB
> system_id VARCHAR2(100)
> claimed_by VARCHAR2(100)
> sys_updated_by VARCHAR2(40)
> sys_updated_on TIMESTAMP(6)
> sys_created_by VARCHAR2(40)
> sys_created_on TIMESTAMP(6)
> sys_mod_count NUMBER(38)

Ok, I see. In that "case" the quotes should be there, and since it worked in previous versions it should not be the cause of the problem I think. It's just that I have seen these double quotes so many times in queries where they should not be there at all...

Shakespeare Received on Mon Apr 07 2008 - 13:27:44 CDT