CODEWITHSUNDEEP
sqloracle-database
Sergey Benzenko
Sergey Benzenko

Reputation: 280

Oracle. Rename column named "count"

I faced a problem with some legacy code. I have a log table with several hundred million records. Queries to that table take too long even thought it has an index. I dig this issue down to the column named "count". It seems like because of that name, Oracle doesn't use index data and uses a full table scan. So I tried to rename the column, but it fails. I tried:

ALTER TABLE t RENAME COLUMN count TO search_count; ORA-00900: invalid SQL statement

ALTER TABLE t RENAME COLUMN "count" TO "search_count"; ORA-00904: "count": invalid identifier (same with any other quotes)

ALTER TABLE t RENAME COLUMN t.count TO t.search_count; ORA-01748: only simple column names allowed here

Creating a new table and moving data there takes too much time and eventually fails with "can't extend tablespace" error.

Could anyone suggest some other method?

Thanks in advance.

Upvotes: 0

Views: 805

Answers (3)

Marmite Bomber
Marmite Bomber

Reputation: 21095

Oracle time to time does not use an index that we would like to, but to my best knowledge there are no column names, that would be inhibit index access.

Example of Index Access on Column "count"

create table my_count as
select rownum "count",
rownum "COUNT",
lpad('x',100,'y') pad
from dual connect by level <= 1000
;

create index my_count_idx1 on my_count ("count");
create index my_count_idx2 on my_count ("COUNT");

Query Using Index

select * from my_count where "count" = 1;

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    78 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MY_COUNT      |     1 |    78 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MY_COUNT_IDX1 |     4 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("count"=1)

The same access is performed fro the index on column "COUNT".

Upvotes: 0

gaborsch
gaborsch

Reputation: 15758

An alternative workaround solution:

  • Create a new column called search_count
  • Update all values there to the value of the count field
  • Specify the index with that column
  • Drop the previous index and the original column

In this case Oracle will use the newly created index.

Upvotes: 1

Popeye
Popeye

Reputation: 35920

I think it is the issue of case sensitivity of column name.

You would be able to see the column name if it is in upper case or lower case using desc t command.

If you have lower case then execute

ALTER TABLE t RENAME COLUMN "count" TO search_count;

But I think your column name is in upper case as per the error you are receiving so try following:

ALTER TABLE t RENAME COLUMN "COUNT" TO search_count;

IMP: Names are converted to upper case when they are used without double quotes in oracle.(case insensitive) also they are stored in metadata like this.

Cheers!!

Upvotes: -1

Related Questions