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.

RENAME COLUMN statement, Internally, the rename column syntax adjusts the Oracle data dictionary only, since the column names are not stored directly inside the segment itself: SQL> desc� The RENAME COLUMN statement allows you to rename an existing column in an existing table in any schema(except the schema SYS). To rename a column, you must eitherbe the database owner or the table owner. Other types of table alterations are possible;see ALTER TABLE statementfor more information. Syntax.

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

Rename column in an Oracle table, SQL> create table a(id number); Table created. SQL> alter table a rename column id to new_id; Table altered. SQL> desc a Name Null? Rename column name in SQL Rename column name in MySQL, MariaDB, Oracle, and PostgreSQL. To rename a column name in MySQL, MariaDB, Oracle, and PostgreSQL, you can follow the below syntax: Syntax ALTER TABLE TableName RENAME COLUMN OldColumnName TO NewColumnName; Example: Write a query to rename the column name “BID” to “BooksID”.

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

How to rename a table column in Oracle 10g, This Oracle ALTER TABLE example will drop the column called customer_name from the table called customers. Rename column in table (NEW in Oracle 9i� In Oracle 9i and above we have a simple rename column command that makes it easy to rename any table column. Internally, the rename column syntax adjusts the Oracle data dictionary only, since the column names are not stored directly inside the segment itself: SQL> desc sales Name Null? Type

Oracle / PLSQL: ALTER TABLE Statement, Table of Contents. Oracle Rename Column table syntax: Oracle Rename Column in a table query example; Oracle Rename table name syntax:� @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.

Oracle Rename Column Using ALTER Query & Rename Table , ORACLE-BASE - Renaming Columns And Constraints. ADD ( 2 CONSTRAINT test1_pk PRIMARY KEY (col1)); Table altered. SQL> DESC test1 Name Null? In Oracle9ir2, Oracle provides "alter table" syntax to rename data columns in-place in this form: alter table. table_name. rename column. old_column_name. TO. new_column_name; Here are some examples of Oracle "alter table" syntax to rename data columns. alter table.

Renaming Columns And Constraints, The rename column option allows the user to type in a new name for the column being renamed. The tool then generates and can execute the SQL to rename� To RENAME A COLUMN in an existing table, the Oracle ALTER TABLE syntax is: ALTER TABLE table_name RENAME COLUMN old_name TO new_name; Example. Let's look at an example that shows how to rename a column in an Oracle table using the ALTER TABLE statement. For example: ALTER TABLE customers RENAME COLUMN customer_name TO cname;

Comments
  • The double quote should work IMHO, that is the standard Oracle escaping for columns. Just like here: stackoverflow.com/questions/26565917/…
  • 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.
  • Thanks gaborsch, I will try that. At least it seems like I can drop column named "COUNT".
  • 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
  • @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.
  • 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;)
  • 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 :)
  • By default, Oracle identifiers (table names, column names, etc.) are case-insensitive.
  • That is what IMP note suggests.
  • Still do not agree whit propositions and comment in the IMP: dbfiddle.uk/…
  • Thanks. But uppercase "COUNT" fails with ORA-00900: invalid SQL statement too(((
  • Or this also: dbfiddle.uk/…