[20221010]使用toad管理索引改名問題.txt

[20221010]使用toad管理索引改名問題.txt

--//今天在最佳化sql語句時發現的問題.做一個記錄.

1.環境:
SYS@192.168.100.235:1521/orcl> @ prxx
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.問題:
SYS@192.168.100.235:1521/orcl> @ 6q67qzn442k68.sql9_0
PL/SQL procedure successfully completed.
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  aac9ayg7mv1x5, child number 0
-------------------------------------
select item_id from com_item_inst where Allow_Null=1 and
inst_id=:inst_id
Plan hash value: 4227739359
-------------------------------------------------------------------------------------------------------------------------------------------------
|Id|Operation                           |Name                           |Starts|E-Rows|E-Bytes|Cost (%CPU)|E-Time  |A-Rows|   A-Time   |Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT                    |                               |     1|      |       |   81 (100)|        |     0|00:00:00.01 |     72 |
|*1| TABLE ACCESS BY INDEX ROWID BATCHED|COM_ITEM_INST                  |     1|     2|    24 |   81   (0)|00:00:01|     0|00:00:00.01 |     72 |
|*2|  INDEX RANGE SCAN                  |I_COM_ITEM_INST_INST_ID_ITEM_ID|     1|    97|       |    2   (0)|00:00:01|    97|00:00:00.01 |      2 |
-------------------------------------------------------------------------------------------------------------------------------------------------
--//當我在toad介面檢視索引時發現看到的索引名字為I_COM_ITEM_INST_INST_ID_ITEM_I.很明顯,toad看到的是錯誤的.
--//12c支援更長字元的索引名以及表名,最大128個字元.
--//I_COM_ITEM_INST_INST_ID_ITEM_I 正好30個字元. 很明顯我使用toad版本可能有點低(12.6.0.53),僅僅顯示30個字元.
--//實際上在toad下建立索引名字超過30個字元是沒有問題的,但是顯示僅僅30個字元.
--//補充說明:我的測試在toad修改索引大於30個字元也是沒有問題的.

SYS@192.168.100.235:1521/orcl> select index_name from dba_indexes where length(index_name)>30;
INDEX_NAME
-------------------------------
I_COM_ITEM_INST_INST_ID_ITEM_ID

--//由於這個原因,修改索引名字長度(大於30個字元)在toad下也是不行的,因為toad介面下僅僅看到前面30個字元,只能在sqlplus下修改.

SYS@192.168.100.235:1521/orcl> rename lis.I_COM_ITEM_INST_INST_ID_ITEM_ID to lis.I_COM_ITEM_INST_INST_ID_ITEM_I;
rename lis.I_COM_ITEM_INST_INST_ID_ITEM_ID to lis.I_COM_ITEM_INST_INST_ID_ITEM_I
       *
ERROR at line 1:
ORA-01765: specifying owner's name of the table is not allowed

SYS@192.168.100.235:1521/orcl> @ cs lis
alter session set current_schema=lis
Session altered.

SYS@192.168.100.235:1521/orcl> rename I_COM_ITEM_INST_INST_ID_ITEM_ID to I_COM_ITEM_INST_INST_ID_ITEM_I;
rename I_COM_ITEM_INST_INST_ID_ITEM_ID to I_COM_ITEM_INST_INST_ID_ITEM_I
*
ERROR at line 1:
ORA-04043: object I_COM_ITEM_INST_INST_ID_ITEM_ID does not exist

SYS@192.168.100.235:1521/orcl> alter index I_COM_ITEM_INST_INST_ID_ITEM_ID rename to I_COM_ITEM_INST_INST_ID_ITEM_I;
Index altered.

SYS@192.168.100.235:1521/orcl> @ cs sys
alter session set current_schema=sys
Session altered.

--//另外我發現在toad下改名超過30個字元是可以的,但是反過來無法修改回來.除非採用上面的命令方式.
--//這給維護運維提供一個注意,建立索引以及表做好還是不要超過30個字元,便於目前工具的使用以及維護的需要.