ORACLE ORA-01445 报错分析 JION的问题-优快云博客

本文介绍了在Oracle 9i到10g2版本数据库中遇到ORA-01445错误的原因及解决方法。当使用JOIN关键字连接多个表时，若所有表的字段总数超过1050个，则会出现该错误。文章对比了使用ANSI语法和传统Oracle语法的不同，并通过具体示例展示了如何避免这一问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

If you are writing queries that select from many tables, like denormalizing data for warehousing, and the sum of the columns in those tables exceeds 1,050, youâ€™ll always get ORA-01445. This occurs from 9i through 10g2, and has happened often enough to me that I avoid my preference for ANSI syntax on Oracle databases, unless Iâ€™m just selecting from a few tables.

在ORACLE 9I - 10G2 这些版本中，用JION关键字连接的表，这些表的字段数量和要<1050个，不然会报ORA-01445，所以最好用逗号的连接方式。

APPS@sigma> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Prod
PL/SQL Release 10.1.0.5.0 - Production
CORE    10.1.0.5.0      Production
TNS for Linux: Version 10.1.0.5.0 - Production
NLSRTL Version 10.1.0.5.0 - Production

APPS@sigma> SELECT NULL
  2    FROM oe_order_headers_all header INNER JOIN oe_order_lines_all line
  3         ON header.header_id = line.header_id
  4         INNER JOIN ra_customers cust ON header.sold_to_org_id = cust.customer_id
  5         INNER JOIN mtl_system_items_b item
  6         ON item.inventory_item_id = line.inventory_item_id
  7       AND item.organization_id = line.ship_from_org_id
  8         INNER JOIN org_organization_definitions org
  9         ON org.organization_id = line.ship_from_org_id
 10         LEFT OUTER JOIN ra_salesreps_all salesrep
 11         ON salesrep.salesrep_id = header.salesrep_id
 12       AND salesrep.org_id = header.org_id
 13         LEFT OUTER JOIN hr_locations_all sales_loc
 14         ON sales_loc.location_id = salesrep.attribute1
 15   WHERE header.orig_sys_document_ref = 'OE_ORDER_HEADERS_ALL74846'
 16     AND header.order_source_id = 0;

       LEFT OUTER JOIN hr_locations_all sales_loc
                       *
ERROR at line 13:
ORA-01445: cannot select ROWID from, or sample, a join view without a
key-preserved table

Now, using the good old Oracle SQL syntax:

APPS@sigma> SELECT NULL
  2    FROM oe_order_headers_all header,
  3         oe_order_lines_all line,
  4         ra_customers cust,
  5         mtl_system_items_b item,
  6         org_organization_definitions org,
  7         ra_salesreps_all salesrep,
  8         hr_locations_all sales_loc
  9   WHERE header.header_id = line.header_id
 10     AND header.sold_to_org_id = cust.customer_id
 11     AND item.inventory_item_id = line.inventory_item_id
 12     AND item.organization_id = line.ship_from_org_id
 13     AND org.organization_id = line.ship_from_org_id
 14     AND salesrep.salesrep_id(+) = header.salesrep_id
 15     AND salesrep.org_id(+) = header.org_id
 16     AND sales_loc.location_id(+) = salesrep.attribute1
 17     AND header.orig_sys_document_ref = 'OE_ORDER_HEADERS_ALL74846'
 18     AND header.order_source_id = 0;

N
-


2 rows selected.