OraFAQ Forum: SQL & PL/SQL » Index on column name "number"!!!

Home » SQL & PL/SQL » SQL & PL/SQL » Index on column name "number"!!!

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Index on column name "number"!!! [message #609061]

Fri, 28 February 2014 14:02

vulcanjibe
Messages: 3
Registered: February 2014
Location: France

Junior Member

Hy,

I'm using a HP software (IT Service Management).
I ve a big performance issue because of this request : select * from table where 'number'=....

The software uses the keyword 'number' as a coulum name so i can't make an index on it!!!!!!

Any suggestion to create an index on this colum named 'number'???? (Of course I can't change the column name!!!!!!!!!)

Report message to a moderator

Re: Index on column name "number"!!! [message #609062 is a reply to message #609061]

Fri, 28 February 2014 14:10

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

I can only assume that you are using single quotes when you mean double quotes. Please use copy-paste in future to avoid any such confusion. Then there is no problem:

orclz> create table t1("number" number);

Table created.

orclz> create index i1 on t1("number");

Index created.

orclz>

Report message to a moderator

Re: Index on column name "number"!!! [message #609063 is a reply to message #609062]

Fri, 28 February 2014 15:16

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Unless of course, they actually named the column 'number' (with the single quotes, which is kind of a blockhead move) in which case John's response still holds, except you have to replace

"number"

with

"'number'"

.

We also do not know though if it was uppercase, lowercase or mixed case. you'll have to adapt the answer to your needs. That is why a cut and paste would have helped.

[Updated on: Fri, 28 February 2014 15:18]

Report message to a moderator

Re: Index on column name "number"!!! [message #609093 is a reply to message #609061]

Sat, 01 March 2014 08:06

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Without any more details on exactly how you got to this point, it appears you've violated to operating principles:

1) Do not use reserved/key words for object names
2) Do not try to over-ride oracle's default of upper-case for object names.

By violating either or both of the above, you simply create headaches for yourself, as you are now experiencing.

Report message to a moderator

Re: Index on column name "number"!!! [message #609094 is a reply to message #609093]

Sat, 01 March 2014 08:33

vulcanjibe
Messages: 3
Registered: February 2014
Location: France

Junior Member

Sadly, the soft has been developed by ****...g developers and i can't change the code!!!!

This soft makes automatic mapping between a model (designed by non developers) and a SGBD and Nobody has explained that there's some reserved keywords!

I will try the create index i1 on t1("number");

Thanks

Report message to a moderator