Why are Oracle table/column/index names limited to

问题:

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?

---

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 the database, 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'.

回答1:

I believe it's the ANSI standard.

EDIT:

Actually, I think it's the SQL-92 standard.

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

Search for "F391, Long identifiers" on this page... http://stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/ap_standard_sql001.htm

(Looking for a ref)

回答2:

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

  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%TYPE in 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 way more costs in developer time, QA time, and newly introduced bugs than it would generate benefits.

回答3:

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)

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.

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.

---

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)

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.

回答4:

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.

回答5:

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

回答6:

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.

回答7:

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.

回答8:

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.

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.

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.

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.

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.

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.

回答9:

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

回答10:

ok, the limitation exists....

but do you really NEED more than to 30 character to name a table/index/column??

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