ORACLE ORA-01445 报错分析 JION的问题

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.