为什么 Oracle 表/列/索引名称限制为 30 个字符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1378133/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why are Oracle table/column/index names limited to 30 characters?
提问by Chris Gill
I can understand that many years ago there would be this kind of limitation, but nowadays surely this limit could easily be increased. We have naming conventions for objects, but there is always a case that turns up where we hit this limit - especially in naming foreign keys.
我可以理解很多年前会有这种限制，但现在这个限制肯定可以很容易地增加。我们有对象的命名约定，但总有一种情况会出现在我们达到这个限制的地方——尤其是在命名外键时。
Does anybody actually know why this isn't a bigger size - or is it bigger in 11g?
有没有人真的知道为什么这不是更大的尺寸 - 还是 11g 更大？
Apparently the answer is that it will break currently scripts that aren't defensively coded. I say that is a very worrying thing, Oracle is trying to be thedatabase, surely this is the kind of thing that you must constantly improve, otherwise your product will die the death of a thousand cuts.
显然，答案是它会破坏当前未进行防御性编码的脚本。我说这是一个非常令人担忧的事情，甲骨文正在试图成为该数据库，想必这是什么样的，你必须不断提高的东西，否则你的产品会死一千削减死亡。
Whenever I see this kind of objection in-house, I think it is time to bite the bullet and sort it out. If people are running scripts that they do not check or maintain when they upgrade Oracle versions, then let them suffer the consequences of that choice. Provide them a compatibility flag, up the size to 4000, then save me the wasted time when I'm creating objects of having to constantly count to 30 to check the name is 'OK'.
每当我在内部看到这种反对意见时，我认为是时候硬着头皮解决了。如果人们在升级 Oracle 版本时运行了他们不检查或维护的脚本，那么让他们承担该选择的后果。为他们提供一个兼容性标志，将大小提高到 4000，然后节省我在创建对象时必须不断数到 30 以检查名称是否“OK”的浪费时间。
采纳答案by cagcowboy
I believe it's the ANSI standard.
我相信这是ANSI标准。
EDIT:
编辑：
Actually, I think it's the SQL-92 standard.
实际上，我认为这是 SQL-92 标准。
A later version of the standard appears to optionally allow for 128 character names, but Oracle doesn't yet support this (or has partial support for it, insofar as it allows 30 characters. Hmmm.)
该标准的更高版本似乎可以选择允许 128 个字符的名称，但 Oracle 尚不支持此功能（或部分支持它，只要它允许 30 个字符。嗯。）
Search for "F391, Long identifiers" on this page... http://stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/ap_standard_sql001.htm
在此页面上搜索“F391，长标识符”... http://stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/ap_standard_sql001.htm
(Looking for a ref)
（寻找参考）
回答by Justin Cave
In addition to cagcowboy's point that it derives from the SQL standard (historically, I suspect that Oracle's decision lead to the SQL standard since Oracle predated the standardization of SQL), I would wager that a large part of the reluctance to allow longer identifiers comes from the realization that there are millions of DBAs with millions of custom scripts that all assume that identifiers are 30 characters long. Allowing every line of code that goes something like
除了 cagcowboy 认为它源自 SQL 标准的观点（从历史上看，我怀疑 Oracle 的决定导致了 SQL 标准，因为 Oracle 早于 SQL 的标准化），我敢打赌，不愿允许更长的标识符的很大一部分来自意识到有数百万 DBA 拥有数百万个自定义脚本，它们都假设标识符长度为 30 个字符。允许每一行代码类似于
l_table_name VARCHAR2(30);
BEGIN
SELECT table_name
INTO l_table_name
FROM dba_tables
WHERE ...
to suddenly break because the DBA 15 years ago used VARCHAR2(30) rather than DBA_TABLES.TABLE_NAME%TYPEin the script would cause massive revolt. I would wager that Oracle alone has thousands of places where this sort of thing has been done over the years in various packages and components. Retrofitting all that existing code to support longer identifiers would be a tremendous project that would almost certainly generate waymore costs in developer time, QA time, and newly introduced bugs than it would generate benefits.
因为 15 年前 DBA 使用 VARCHAR2(30) 而不是DBA_TABLES.TABLE_NAME%TYPE在脚本中而突然中断会引起大规模反抗。我敢打赌，多年来，仅 Oracle 就有数千个地方在各种包和组件中完成了此类事情。改造所有现有的代码，以支持更长的标识符将是一个巨大的工程，几乎肯定会产生办法的开发时间更多的成本，质量保证时间，和新引进的错误比这将产生效益。
回答by Kanmuri
I was looking this up and found this question via Google, but also found out that as of Oracle 12c Release 2 (12.2), this is no longer strictly the case. (https://oracle-base.com/articles/12c/long-identifiers-12cr2)
我正在查找并通过 Google 找到了这个问题，但也发现从 Oracle 12c 第 2 版（12.2）开始，情况不再严格如此。( https://oracle-base.com/articles/12c/long-identifiers-12cr2)
At some point every DBA or developer will have hit a point where the 30 character limit for object names has caused a problem. This limit can be extremely painful when doing migration projects from SQL Server or MySQL to Oracle. In Oracle Database 12cR2, the maximum length of most identifiers is now 128 characters.
在某些时候，每个 DBA 或开发人员都会遇到对象名称的 30 个字符限制导致问题的情况。在进行从 SQL Server 或 MySQL 到 Oracle 的迁移项目时，此限制可能会非常痛苦。在 Oracle Database 12cR2 中，大多数标识符的最大长度现在为 128 个字符。
This is a new feature in 12.2, according to (http://blog.dbi-services.com/oracle-12cr2-long-identifiers/). According to that post, 12.1 was still limited to 30 characters.
根据 ( http://blog.dbi-services.com/oracle-12cr2-long-identifiers/) ，这是 12.2 中的一项新功能。根据该帖子，12.1 仍然限制为 30 个字符。
Edit: Here's a link to the official Oracle documentation explaining the change. (https://docs.oracle.com/cloud/latest/exadataexpress-cloud/CSDBF/longer-identifier-names.htm#CSDBF-GUID-F4CA155F-5A37-4705-8443-0A8C9E3F875C)
编辑：这是解释更改的官方 Oracle 文档的链接。( https://docs.oracle.com/cloud/latest/exadataexpress-cloud/CSDBF/longer-identifier-names.htm#CSDBF-GUID-F4CA155F-5A37-4705-8443-0A8C9E3F875C)
Starting with Oracle Database 12c Release 2 (12.2), the maximum length of identifier names for most types of database objects has been increased to 128 bytes.
从 Oracle Database 12c 第 2 版 (12.2) 开始，大多数类型的数据库对象的标识符名称的最大长度已增加到 128 个字节。
回答by Lorenzo Gatti
Given the practical necessity of identifier length limits, good design restricts the length of actual names to avoid hitting the ceiling when the names are combined with each other and with prefixes and suffixes.
考虑到标识符长度限制的实际必要性，良好的设计会限制实际名称的长度，以避免在名称相互组合以及前缀和后缀组合时达到上限。
For example, a convention of naming foreign key constraints
例如，命名外键约束的约定
FK_<table1>_<table2>
limits table names to 13 characters or less; most databases are going to need more prefixes and suffixes, further limiting the length of table names.
将表名限制为 13 个字符或更少；大多数数据库将需要更多的前缀和后缀，从而进一步限制了表名的长度。
回答by Gary Myers
Constraint violations get reported in SQLERRM which is limited to 255 characters, and which most clients use to make errors visible. I suspect increasing the allowable size of constraint names significantly would impact the ability to report on the violations (especially where a constraint violation has been bubbled up through a few layers of PL/SQL code).
SQLERRM 中会报告约束违规，该 SQLERRM 限制为 255 个字符，并且大多数客户端使用它来使错误可见。我怀疑显着增加约束名称的允许大小会影响报告违规的能力（特别是在约束违规通过几层 PL/SQL 代码冒泡的情况下）。
回答by Mac
All of these 'constraints' are left over responses to limitations imposed by processor architectures that hail from the 70s. Since that time processors have evolved to the point that these limitations are no longer necessary; they are just left over. However, changing them is a BIG deal for the writers of the RDBMS. Since these length limitatons affect everything downstream changing it willy nilly to accomodate say a longer procedure name can and probably will break a lot of other stuff such as exeception reporting, the data dictionary, etc., so forth and so on. I would require a major re-write of the Oracle RDBMS.
所有这些“约束”都是对来自 70 年代的处理器架构施加的限制的响应。从那时起，处理器已经发展到不再需要这些限制的地步。他们只是剩下的。然而，改变它们对于 RDBMS 的作者来说是一件大事。由于这些长度限制会影响下游的所有内容，因此无法适应说更长的过程名称可能并且可能会破坏许多其他内容，例如异常报告、数据字典等，等等。我需要对 Oracle RDBMS 进行重大重写。
回答by Michael Dillon
I believe that the 30 character identifier length comes from COBOL which was standardised in the late 1950s. Since COBOL programs were the main user of SQL (and SEQUEL before that (and QUEL before that)), this must have seemed like a reasonable number for the identifier length.
我相信 30 个字符的标识符长度来自于 1950 年代后期标准化的 COBOL。由于 COBOL 程序是 SQL 的主要用户（以及之前的 SEQUEL（以及之前的 QUEL）），这对于标识符长度来说似乎是一个合理的数字。
回答by atconsul
The direct answer to the question is that Oracle style is inherited from older ideas in which 30 seemed a lot, and much more would have increased the risk of unpinning the dictionary cache from real memory in typical databases.
对这个问题的直接回答是，Oracle 风格是从旧的想法继承而来的，其中 30 似乎很多，而且更多会增加从典型数据库中的真实内存中取消字典缓存的风险。
In contrast, ODBC namespace comes from a very different place, where data sets are extracted rapidly by parsing a table in an Excel sheet and automatically build database tables with column names taken from sheet table headings. Thinking like that leads you to allowing identifiers that even contain embedded carriage returns, and of course special characters and mixed case. It's a sensible abstraction because it models the way today's data analysts think.
相比之下，ODBC 命名空间来自一个非常不同的地方，通过解析 Excel 工作表中的表格来快速提取数据集，并使用取自工作表表格标题的列名自动构建数据库表。这样的想法会导致您允许标识符甚至包含嵌入的回车符，当然还有特殊字符和大小写混合。这是一个明智的抽象，因为它模拟了当今数据分析师的思维方式。
Never mind SQL92, it's ODBC compliance that really matters to today's universal database, and other vendors have addressed this better than Oracle. Even Teradata, for example, which isn't seen by many as a pervasive player, caters for TWO namespaces, with and without the quotes, the former with a 30 char limit, the latter a full ODBC implementation where weird long identifiers are catered for.
不用管 SQL92，对于当今的通用数据库来说，真正重要的是 ODBC 合规性，并且其他供应商已经比 Oracle 更好地解决了这个问题。例如，即使 Teradata，它不被许多人视为普遍存在的播放器，也迎合了两个命名空间，带引号和不带引号，前者有 30 个字符限制，后者是一个完整的 ODBC 实现，其中处理了奇怪的长标识符.
Even in the traditional large database arena, 30 characters is often a problem where names are to remain meaningful, consistent and memorable. Once you start to design specialising structures with role-named inheritance you start abbreviating abbreviations, and consistency soon dies, because for example the same root identifier rendered as a table name or a column name will in one case need further abbreviation and in the other not. If real users in significant numbers are invited on to such layers the consequences are very poor usability, and fortunately for any ageing database the main drive now is to separate user from database via object layers and BI tools.
即使在传统的大型数据库领域，30 个字符也常常是一个问题，因为名称要保持有意义、一致和令人难忘。一旦您开始设计具有角色命名继承的专门结构，您就开始缩写缩写，并且一致性很快就会消失，因为例如呈现为表名或列名的相同根标识符在一种情况下需要进一步缩写，而在另一种情况下则不需要. 如果大量真实用户被邀请到这些层，结果是可用性非常差，幸运的是，对于任何老化的数据库，现在的主要驱动力是通过对象层和 BI 工具将用户与数据库分离。
This leaves the database layer to the DBA and the data architect teams, who are perhaps not that bothered. Working out abbreviation schemes is still a job for life, it seems.
这将数据库层留给 DBA 和数据架构师团队，他们可能不会那么烦恼。制定缩写方案似乎仍然是一项终生的工作。
That Oracle has not addressed this old limitation perhaps reflects mostly on the fact that it is not (yet) losing much business to its competition when it can't directly port database designs built using longer identifiers.
Oracle 没有解决这个旧的限制可能主要反映了这样一个事实，即当它不能直接移植使用更长标识符构建的数据库设计时，它并没有（还）在竞争中失去很多业务。
回答by Raphael
All the above comments are right, BUT you need to keep in mind the performance cost of longer names. In the early 1990's, when Informix set up huge billboard "Informix Faster Than Oracle!" on route 101 next to Oracle headquarters, Informix allowed table names only shorter than 18 characters! The reason is obvious -- table names in their literal form (i.e. as actual names rather than 't138577321'or something like that) are stored in the Data Dictionary. Longer names equal larger Data Dictionary, and since the Data Dictionary is read each time a query requires a hard parse, a larger data dictionary equals poor performance...
以上所有评论都是正确的，但您需要记住较长名称的性能成本。1990 年代初期，Informix 设立了巨大的广告牌“Informix Faster Than Oracle！” 在 Oracle 总部旁边的 101 号路线上，Informix 只允许少于 18 个字符的表名！原因很明显——表名以其字面形式（即作为实际名称而不是“t138577321”或类似名称）存储在数据字典中。较长的名称等于较大的数据字典，并且由于每次查询需要硬解析时都会读取数据字典，因此较大的数据字典等于较差的性能...
回答by user173422
ok, the limitation exists....
好吧，限制存在......
but do you really NEED more than to 30 character to name a table/index/column??
但是你真的需要超过 30 个字符来命名表/索引/列吗？
when writing queries, with that limitation I STILL find some column/table names annoying. If the limit were higher I might run into tables that required a query like:
在编写查询时，由于这个限制，我仍然发现一些列/表名称很烦人。如果限制更高，我可能会遇到需要查询的表，例如：
select unique_identifier_column,
time_when_the_user_remembered_to_change_the_row_in_the_receipt_table,
foreign_key_to_the_ap_invoice_distributions_history_table_related_to_the_all_rows_table
from ap_invoices_really_really_all_all_rows_present_in_this_ebs_table.
I apologize for the huge words :P
我为这些大话道歉：P
