Oracle. Rename column named "count"

Question

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.

What is the actual case of your `count` column? Is it really all lowercase. Can you include the create table output? — Tim Biegeleisen, Nov 06 '19 at 07:24
```CREATE TABLE t ( id NUMBER(10, 0), search_date DATE, sid NUMBER(10, 0), search_id NUMBER(8, 0), count NUMBER(5, 0), client_ip VARCHAR2(15 BYTE) )``` I was under the impression that case doesn't matter in Oracle — Sergey Benzenko, Nov 06 '19 at 07:28
By default, Oracle identifiers (table names, column names, etc.) are case-insensitive. — VBoka, Nov 06 '19 at 07:29
The double quote should work IMHO, that is the standard Oracle escaping for columns. Just like here: https://stackoverflow.com/questions/26565917/how-can-i-select-a-column-named-date-in-oracle — gaborsch, Nov 06 '19 at 07:30
ALTER TABLE t RENAME COLUMN count TO search_count works https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=3ae0ae7cbe3b6ac52f71f74c83624b00 — Zaynul Abadin Tuhin, Nov 06 '19 at 07:34
Workaround alternative solution: create a new column `search_count`, update all values there to the value of the `count` field, and specify the index with that column - then Oracle will use it. You can add triggers to keep the compatibility with the legacy code, maintaining that these two columns are equal. But that's the last resort, I hope you will find a way to rename. — gaborsch, Nov 06 '19 at 07:38
Sergey, can you give us the result of this query: select TABLE_NAME, COLUMN_NAME, DATA_TYPE from USER_TAB_COLS where table_name = 'T'; — VBoka, Nov 06 '19 at 07:38
`T ID NUMBER` `T SEARCH_DATE DATE` `T SID NUMBER` `T SEARCH_ID NUMBER` `T COUNT NUMBER` `T CLIENT_IP VARCHAR2` — Sergey Benzenko, Nov 06 '19 at 07:58
Thanks gaborsch, I will try that. At least it seems like I can drop column named `"COUNT"`. — Sergey Benzenko, Nov 06 '19 at 08:04
Please **describe you root problem**. This sentence gives no information: *Queries to that table take too long even thought it has an index*. I suspect the column name is *not your problem* — Marmite Bomber, Nov 06 '19 at 08:19
Go to sql of the table (where CREATE TABLE SQL IS) and see if this is your problem: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=00a8829af1be30abe1681c2e07fdad6b — VBoka, Nov 06 '19 at 08:26
@MarmiteBomber, the problem is that Oracle uses full table scan if the column is named `count` in the table and in the index. I tested solution suggested by gaborsch (using partial data from original table) and in the same query but with column named `search_count` Oracle correctly uses index range scan. gaborsch thanks again. — Sergey Benzenko, Nov 06 '19 at 10:17

score 1 · Accepted Answer · answered Nov 06 '19 at 10:20

1

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.

answered Nov 06 '19 at 10:20

gaborsch

15,408
6
37
48

This is an *elegant workaround*, but the problem seems to be not evident and well documented.. Oracle *can rename* any column you may legally create and tehre is not skip list of column names *not used* in index;) – Marmite Bomber Nov 06 '19 at 13:09
I agree, I also mentioned in a comment that he should be able to rename. But we don't know what's under the hood, it is safe to recreate the suspicious parts. Also, he may have another root problem somewhere deep, but this one is also a valid problem. I don't want to resolve the issue that he's going to be fired if he doesn't speed up the old applocation :) – gaborsch Nov 06 '19 at 13:22

score 0 · Answer 2 · answered Nov 06 '19 at 12:03

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".

Popeye · Answer 3 · 2019-11-06T08:07:59.583

-1

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!!

edited Nov 06 '19 at 08:07

answered Nov 06 '19 at 07:31

Popeye

35,427
4
10
31

1

By default, Oracle identifiers (table names, column names, etc.) are case-insensitive. – VBoka Nov 06 '19 at 07:32
That is what IMP note suggests. – Popeye Nov 06 '19 at 07:32
Still do not agree whit propositions and comment in the IMP: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=530d5fde75e80d3bf3acfba2f357f7d2 – VBoka Nov 06 '19 at 07:34
Thanks. But uppercase `"COUNT"` fails with `ORA-00900: invalid SQL statement` too((( – Sergey Benzenko Nov 06 '19 at 07:34
Or this also: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=78cba759f2038912e12bdb432bc95908 – VBoka Nov 06 '19 at 07:34
This is a valid answer, but of course do not work e.g. for column name `"Count"` the case used must be the same as used in the create table statement @VBokšić @SergeyBenzenko – Marmite Bomber Nov 06 '19 at 13:12
@MarmiteBomber I do not agree because: "Names are converted to upper case when they are used without double quotes in oracle." This, it is not a correct statement. When you create table with "COUNT" or "count" then you can rename it with COUNT or count. The problem is when you call it "Count" as you said and that is why I believe this is not a correct statement(they are not converted they are only case-sensitive), not a good example and not correct answer. You can see in the comments,my last comment with this demo: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=00a8829af1be30abe1681c2e07fdad6b – VBoka Nov 06 '19 at 13:43

Oracle. Rename column named "count"

3 Answers3