Join 到頭暈怎麼辦？

如果您看到一個很長的 SQL join 例如：

SELECT
*
FROM
 mtl_parameters ship_from_org
,hz_cust_site_uses_all ship_su
,hz_party_sites ship_ps
,hz_locations ship_loc
,hz_cust_acct_sites_all ship_cas
,hz_cust_site_uses_all bill_su
,hz_party_sites bill_ps
,hz_locations bill_loc
,hz_cust_acct_sites_all bill_cas
,hz_parties party
,hz_cust_accounts cust_acct
,ra_terms_tl term
,oe_order_headers h
,hz_cust_account_roles sold_roles
,hz_parties sold_party
,hz_org_contacts sold_cont
,hz_party_relationships sold_rel
,hz_cust_account_roles ship_roles
,hz_parties ship_party
,hz_org_contacts ship_cont
,hz_party_relationships ship_rel
,hz_cust_account_roles invoice_roles
,hz_parties invoice_party
,hz_org_contacts invoice_cont
,hz_party_relationships invoice_rel
,fnd_currencies fndcur
,oe_transaction_types_tl ot
,qp_list_headers_tl pl
,ra_rules invrule
,ra_rules accrule 
WHERE
 h.order_type_id = ot.transaction_type_id 
AND ot.language = userenv('LANG') 
AND h.price_list_id = pl.list_header_id(+) 
AND pl.language(+) = userenv('LANG') 
AND h.invoicing_rule_id = invrule.rule_id(+) 
AND h.accounting_rule_id = accrule.rule_id(+) 
AND h.payment_term_id = term.term_id(+) 
AND TERM.Language(+) =userenv('LANG') 
AND h.transactional_curr_code = fndcur.currency_code 
AND h.sold_to_org_id = cust_acct.cust_account_id(+) 
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+) 
AND h.ship_from_org_id = ship_from_org.organization_id (+) 
AND h.ship_to_org_id = ship_su.site_use_id(+) 
AND ship_su.cust_acct_site_id= ship_cas.cust_acct_site_id(+) 
AND ship_cas.party_site_id = ship_ps.party_site_id(+) 
AND ship_loc.location_id(+) = ship_ps.location_id 
AND h.invoice_to_org_id = bill_su.site_use_id(+) 
AND bill_su.cust_acct_site_id= bill_cas.cust_acct_site_id(+) 
AND bill_cas.party_site_id = bill_ps.party_site_id(+) 
AND bill_loc.location_id(+) = bill_ps.location_id 
AND h.sold_to_contact_id = sold_roles.cust_account_role_id(+) 
AND sold_roles.party_id = sold_rel.party_id(+) 
AND sold_roles.role_type(+) = 'CONTACT' 
AND sold_cont.party_relationship_id(+)= sold_rel.party_relationship_id 
AND sold_rel.subject_id = sold_party.party_id(+) 
AND h.ship_to_contact_id = ship_roles.cust_account_role_id(+) 
AND ship_roles.party_id = ship_rel.party_id(+) 
AND ship_roles.role_type(+) = 'CONTACT' 
AND ship_cont.party_relationship_id(+)= ship_rel.party_relationship_id 
AND ship_rel.subject_id = ship_party.party_id(+) 
AND h.invoice_to_contact_id = invoice_roles.cust_account_role_id(+) 
AND invoice_roles.party_id = invoice_rel.party_id(+) 
AND invoice_roles.role_type(+) = 'CONTACT' 
AND invoice_cont.party_relationship_id(+)=invoice_rel.party_relationship_id 
AND invoice_rel.subject_id = invoice_party.party_id(+)

您可試著把它圖像化，例如：

http://www.hkln.net/blog/2008-05-03/out.png

這幅圖是用 Graphviz 畫的，以下 Perl 程式讀入 SQL 的 WHERE 子句，
然後產生 dot 語言，送給Graphviz 畫圖。
（把 WHERE 子句內容貼在 __DATA__ 之下）

use strict;

my ($text, @predicates);

$text = join '', ;

$text =~ s/\n//g; 

@predicates = split /and/is, $text;

print "digraph G { \n rankdir=LR;\n";

foreach my $predicate (@predicates) {
 my ($left, $right, $left_table, $left_column, $right_table, $right_column);
 my ($left_is_outer, $right_is_outer);
 my ($arrowhead, $arrowtail, $color, $prop);

 $predicate = trim($predicate);

 ($left, $right) = split /=/, $predicate;

 $left = trim($left);
 $right = trim($right);

 if ($left =~ /\(\+\)/ ) {
  $left_is_outer = 1;
 }
 if ($right =~ /\(\+\)/ ) {
  $right_is_outer = 1;
 }

 ($left_table, $left_column) = split /\./, $left;
 ($right_table, $right_column) = split /\./, $right;


 if ($right_column ne '') {
  $right_table = lc( $right_table );
 }
 if ($left_column ne '') {
  $left_table = lc( $left_table );
 }

 $arrowhead = 'none';
 $arrowtail = 'none';
 $color = 'black';

 if ($left_is_outer) {
  $arrowtail = 'odot';
  $color = 'red';
 }
 if ($right_is_outer) {
  $arrowhead = 'odot';
  $color = 'red';

 }

 $prop = qq{arrowhead=$arrowhead,arrowtail=$arrowtail, color=$color, label="$predicate"};

 print qq{ "$left_table"\t->\t"$right_table"\t[$prop] ; \n}
}


print "}";


sub trim
{
 my $string = shift;
 $string =~ s/^\s+//;
 $string =~ s/\s+$//;
 return $string;
}



__DATA__

 h.order_type_id = ot.transaction_type_id 
AND ot.language = userenv('LANG')