OracleでANSI SQL結合構文が正常に動作しないケース
参照元:When ANSI SQL Join Syntax Does Not Work in Oracle < Eddie Awad’s Blog
— beginning of translation —
私はOracleのANSI SQL構文に自信をなくしてしまいました。Ericは、基本に戻ろう: アンチジョインとセミジョインのコメントで、次のような注意を促してくれました。
ウェアハウス用に非正規化されているデータのように、多くの表から 結果を取得し、しかもその結合表の列数の合計が1050を超える場合、 常にORA-01445が発生します。この現象は9iから10g2で発生します。 そして、本来ならばOracleでANSI構文を使いたいところなのですが、 しばしばこの現象に出くわすため、少数の表を問合せる以外では ANSI構文を使いません。
そして、この現象は私の環境でも発生しました。以下に示す２つの問合せは、どちらもOracle E-Business Suite (11.5.10)の標準的な表を使用しており、最初の例がANSI構文、２番目の例がOracle構文を使用しているという点以外は同一の文です。そして、お分かりのように、ANSI構文を使った結合文のほうは、次のようなエラーで無残にも失敗してしまいました:
ORA-01445: キー保存表なしに結合ビューからROWID を選択できません。
これは私にはバグのように見えます。面白いことに、問合せ文のSELECT NULLという箇所をSELECT COUNT(*)に変えると正常に動きます。
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
今度は従来のOracle構文を使った正しい書き方です:
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.
さらに興味深いことに、ORA-01445エラーについて、Oracle Databaseエラー・メッセージ 10g リリース1（10.1）および、リリース2（10.2）のどこにも言及されていないのです。しかしながら、以下のサイトで参照することができました:
* Andrew Channels Dexter Pinion: ORA-01445
* Oracle-l: limit on number of tables in join
* OraFAQ Forum: Urgent-ORA-01445
* Outer join - same query, different results
* Oracle bug no. 5967612 on Metalink
— end of translation —