Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 помогите убрать HASH JOIN и CARTESIAN  [new]
HelpMePls
Member

Откуда:
Сообщений: 345
Всем добрый день!


проблема в следующем: есть запрос который запускается на

1. Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

2. Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options


на 9ке получается HASH JOIN и CARTESIAN, на 10ке одни NESTED LOOPS.

прилагаю 2 файла планов из TOAD.


Вопрос: как можно хинтами решить данную проблему.


Благодарю за помощь!
25 дек 08, 10:46    [6618003]     Ответить | Цитировать Сообщить модератору
 Re: помогите убрать HASH JOIN и CARTESIAN  [new]
HelpMePls
Member

Откуда:
Сообщений: 345
план для 9ки


<ExplainPlan>
<PlanElement object_ID="0" id="0" operation="SELECT STATEMENT" optimizer="HINT: ALL_ROWS" cost="8,530" cardinality="1" bytes="792">
<PlanElements>
<PlanElement object_ID="0" id="1" operation="FILTER">
<PlanElements>
<PlanElement object_ID="0" id="2" operation="NESTED LOOPS" option="OUTER">
<PlanElements>
<PlanElement object_ID="0" id="3" operation="NESTED LOOPS" option="OUTER" cost="8,528" cardinality="1" bytes="763">
<PlanElements>
<PlanElement object_ID="0" id="4" operation="NESTED LOOPS" option="OUTER" cost="8,527" cardinality="1" bytes="717">
<PlanElements>
<PlanElement object_ID="0" id="5" operation="NESTED LOOPS" option="OUTER" cost="8,526" cardinality="1" bytes="702">
<PlanElements>
<PlanElement object_ID="0" id="6" operation="NESTED LOOPS" cost="8,523" cardinality="1" bytes="658">
<PlanElements>
<PlanElement object_ID="0" id="7" operation="NESTED LOOPS" cost="8,521" cardinality="1" bytes="639">
<PlanElements>
<PlanElement object_ID="0" id="8" operation="NESTED LOOPS" cost="8,518" cardinality="1" bytes="543">
<PlanElements>
<PlanElement object_ID="0" id="9" operation="NESTED LOOPS" cost="8,517" cardinality="1" bytes="538">
<PlanElements>
<PlanElement object_ID="0" id="10" operation="NESTED LOOPS" cost="8,515" cardinality="1" bytes="528">
<PlanElements>
<PlanElement object_ID="0" id="11" operation="NESTED LOOPS" option="OUTER" cost="8,512" cardinality="1" bytes="485">
<PlanElements>
<PlanElement object_ID="0" id="12" operation="HASH JOIN" cost="8,510" cardinality="1" bytes="455">
<PlanElements>
<PlanElement object_ID="0" id="13" operation="MERGE JOIN" option="CARTESIAN" cost="3,750" cardinality="171,698" bytes="35,369,788">
<PlanElements>
<PlanElement object_ID="1" id="14" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNICUS_B" object_name="OPERDOC" object_instance="12" cost="25" cardinality="13" bytes="1,573">
<PlanElements>
<PlanElement object_ID="0" id="15" operation="NESTED LOOPS" cost="27" cardinality="17" bytes="2,482">
<PlanElements>
<PlanElement object_ID="2" id="16" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="UNICUS_B" object_name="CONTRACT_STATUS_TYPE" object_instance="15" cost="2" cardinality="1" bytes="25"/>
<PlanElement object_ID="3" id="17" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="UNICUS_B" object_name="INDX_DOC_DATE" object_type="NON-UNIQUE" search_columns="1" cost="1" cardinality="67"/>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
<PlanElement object_ID="0" id="18" operation="BUFFER" option="SORT" cost="3,725" cardinality="9,974" bytes="598,440">
<PlanElements>
<PlanElement object_ID="4" id="19" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="UNICUS_B" object_name="CONTRACT_VARIANT" object_instance="2" cost="219" cardinality="9,974" bytes="598,440"/>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
<PlanElement object_ID="5" id="20" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="UNICUS_B" object_name="CONTRACT" object_instance="1" cost="751" cardinality="1,110,253" bytes="276,452,997"/>
</PlanElements>
</PlanElement>
<PlanElement object_ID="6" id="21" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNICUS_B" object_name="CONTRACT_RESTRICTION_TER" object_instance="14" cost="2" cardinality="1" bytes="30">
<PlanElements>
<PlanElement object_ID="7" id="22" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="UNICUS_B" object_name="FK_CONTVARIANT_RESTR_TER" object_type="NON-UNIQUE" search_columns="1" cost="1" cardinality="1"/>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
<PlanElement object_ID="8" id="23" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNICUS_B" object_name="CONTRACT_SUBJECT" object_instance="6" cost="3" cardinality="1" bytes="43">
<PlanElements>
<PlanElement object_ID="9" id="24" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="UNICUS_B" object_name="FK_CONTRACT_REF_CONT_SUBJ" object_type="NON-UNIQUE" search_columns="1" cost="2" cardinality="1"/>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
<PlanElement object_ID="10" id="25" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNICUS_B" object_name="SUBJECT_RELATION" object_instance="7" cost="2" cardinality="1" bytes="10">
<PlanElements>
<PlanElement object_ID="11" id="26" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="UNICUS_B" object_name="RELATION_2602_FK" object_type="NON-UNIQUE" search_columns="1" cost="1" cardinality="2"/>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
<PlanElement object_ID="12" id="27" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="UNICUS_B" object_name="PK_SUBJECT" object_type="UNIQUE" search_columns="1" cost="1" cardinality="1" bytes="5"/>
</PlanElements>
</PlanElement>
<PlanElement object_ID="13" id="28" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNICUS_B" object_name="INSURANCE_OBJECT" object_instance="4" cost="3" cardinality="1" bytes="96">
<PlanElements>
<PlanElement object_ID="14" id="29" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="UNICUS_B" object_name="RELATION_256_FK" object_type="NON-UNIQUE" search_columns="1" cost="2" cardinality="1"/>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
<PlanElement object_ID="15" id="30" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNICUS_B" object_name="CONTRACT_CONDITION" object_instance="3" cost="2" cardinality="1" bytes="19">
<PlanElements>
<PlanElement object_ID="16" id="31" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="UNICUS_B" object_name="PK_CONTRACT_CONDITION" object_type="UNIQUE" search_columns="1" cost="1" cardinality="1"/>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
<PlanElement object_ID="17" id="32" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNICUS_B" object_name="CONTRACT_CONDITION_SPEC" object_instance="9" cost="3" cardinality="1" bytes="44">
<PlanElements>
<PlanElement object_ID="18" id="33" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="UNICUS_B" object_name="FK_CONTRACT_RFERENCE_CONTRACT" object_type="NON-UNIQUE" search_columns="1" cost="2" cardinality="1"/>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
<PlanElement object_ID="19" id="34" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNICUS_B" object_name="PRODUCT_CONDITION" object_instance="10" cost="1" cardinality="1" bytes="15">
<PlanElements>
<PlanElement object_ID="20" id="35" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="UNICUS_B" object_name="PK_PRODUCT_CONDITION" object_type="UNIQUE" search_columns="1" cardinality="1"/>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
<PlanElement object_ID="21" id="36" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNICUS_B" object_name="INSURANCE_RULE_SPEC" object_instance="11" cost="1" cardinality="1" bytes="46">
<PlanElements>
<PlanElement object_ID="22" id="37" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="UNICUS_B" object_name="PK_INSURANCE_RULE_SPEC" object_type="UNIQUE" search_columns="1" cardinality="1"/>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
<PlanElement object_ID="23" id="38" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNICUS_B" object_name="OPERDOC_ROW1" object_instance="13" cost="2" cardinality="1" bytes="29">
<PlanElements>
<PlanElement object_ID="24" id="39" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="UNICUS_B" object_name="OPERDOCROW1_PREMIUM_FK" object_type="NON-UNIQUE" search_columns="1" cost="1" cardinality="1"/>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
</ExplainPlan>

К сообщению приложен файл (9i.XML - 12Kb) cкачать
25 дек 08, 10:48    [6618014]     Ответить | Цитировать Сообщить модератору
 Re: помогите убрать HASH JOIN и CARTESIAN  [new]
HelpMePls
Member

Откуда:
Сообщений: 345
план для 10ки

<ExplainPlan>
<PlanElement object_ID="0" id="0" operation="SELECT STATEMENT" optimizer="HINT: ALL_ROWS" cost="160" cardinality="1" bytes="833">
<PlanElements>
<PlanElement object_ID="0" id="1" operation="NESTED LOOPS" option="OUTER" cost="160" cardinality="1" bytes="833">
<PlanElements>
<PlanElement object_ID="0" id="2" operation="NESTED LOOPS" option="OUTER" cost="159" cardinality="1" bytes="787">
<PlanElements>
<PlanElement object_ID="0" id="3" operation="NESTED LOOPS" option="OUTER" cost="158" cardinality="1" bytes="772">
<PlanElements>
<PlanElement object_ID="0" id="4" operation="NESTED LOOPS" cost="155" cardinality="1" bytes="725">
<PlanElements>
<PlanElement object_ID="0" id="5" operation="NESTED LOOPS" cost="153" cardinality="1" bytes="705">
<PlanElements>
<PlanElement object_ID="0" id="6" operation="NESTED LOOPS" cost="150" cardinality="1" bytes="601">
<PlanElements>
<PlanElement object_ID="0" id="7" operation="NESTED LOOPS" cost="149" cardinality="1" bytes="596">
<PlanElements>
<PlanElement object_ID="0" id="8" operation="NESTED LOOPS" cost="146" cardinality="2" bytes="1,172">
<PlanElements>
<PlanElement object_ID="0" id="9" operation="NESTED LOOPS" option="OUTER" cost="143" cardinality="1" bytes="543">
<PlanElements>
<PlanElement object_ID="0" id="10" operation="NESTED LOOPS" cost="141" cardinality="1" bytes="513">
<PlanElements>
<PlanElement object_ID="0" id="11" operation="NESTED LOOPS" cost="140" cardinality="1" bytes="244">
<PlanElements>
<PlanElement object_ID="0" id="12" operation="NESTED LOOPS" cost="54" cardinality="85" bytes="15,045">
<PlanElements>
<PlanElement object_ID="0" id="13" operation="NESTED LOOPS" cost="29" cardinality="18" bytes="2,628">
<PlanElements>
<PlanElement object_ID="1" id="14" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="CONTRACT_STATUS_TYPE" object_type="TABLE" object_instance="15" cost="3" cardinality="1" bytes="25"/>
<PlanElement object_ID="2" id="15" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="OPERDOC" object_type="TABLE" object_instance="12" cost="26" cardinality="13" bytes="1,573">
<PlanElements>
<PlanElement object_ID="3" id="16" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="INDX_DOC_DATE" object_type="INDEX" search_columns="1" cost="1" cardinality="68"/>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
<PlanElement object_ID="4" id="17" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="OPERDOC_ROW1" object_type="TABLE" object_instance="13" cost="2" cardinality="5" bytes="155">
<PlanElements>
<PlanElement object_ID="5" id="18" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="OPERDOCROW1_OPERDOC_FK" object_type="INDEX" search_columns="1" cost="1" cardinality="5"/>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
<PlanElement object_ID="6" id="19" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="CONTRACT_VARIANT" object_type="TABLE" object_instance="2" cost="1" cardinality="1" bytes="67">
<PlanElements>
<PlanElement object_ID="7" id="20" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="PK_CONTRACT_VARIANT" object_type="INDEX (UNIQUE)" search_columns="1" cost="0" cardinality="1"/>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
<PlanElement object_ID="8" id="21" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="CONTRACT" object_type="TABLE" object_instance="1" cost="1" cardinality="1" bytes="269">
<PlanElements>
<PlanElement object_ID="9" id="22" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="PK_CONTRACT" object_type="INDEX (UNIQUE)" search_columns="1" cost="0" cardinality="1"/>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
<PlanElement object_ID="10" id="23" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="CONTRACT_RESTRICTION_TER" object_type="TABLE" object_instance="14" cost="2" cardinality="1" bytes="30">
<PlanElements>
<PlanElement object_ID="11" id="24" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="FK_CONTVARIANT_RESTR_TER" object_type="INDEX" search_columns="1" cost="1" cardinality="1"/>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
<PlanElement object_ID="12" id="25" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="CONTRACT_SUBJECT" object_type="TABLE" object_instance="6" cost="3" cardinality="1" bytes="43">
<PlanElements>
<PlanElement object_ID="13" id="26" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="FK_CONTRACT_REF_CONT_SUBJ" object_type="INDEX" search_columns="1" cost="2" cardinality="1"/>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
<PlanElement object_ID="14" id="27" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="SUBJECT_RELATION" object_type="TABLE" object_instance="7" cost="2" cardinality="1" bytes="10">
<PlanElements>
<PlanElement object_ID="15" id="28" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="RELATION_2602_FK" object_type="INDEX" search_columns="1" cost="1" cardinality="2"/>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
<PlanElement object_ID="16" id="29" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="PK_SUBJECT" object_type="INDEX (UNIQUE)" search_columns="1" cost="1" cardinality="1" bytes="5"/>
</PlanElements>
</PlanElement>
<PlanElement object_ID="17" id="30" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="INSURANCE_OBJECT" object_type="TABLE" object_instance="4" cost="3" cardinality="1" bytes="104">
<PlanElements>
<PlanElement object_ID="18" id="31" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="RELATION_256_FK" object_type="INDEX" search_columns="1" cost="2" cardinality="1"/>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
<PlanElement object_ID="19" id="32" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="CONTRACT_CONDITION" object_type="TABLE" object_instance="3" cost="2" cardinality="1" bytes="20">
<PlanElements>
<PlanElement object_ID="20" id="33" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="PK_CONTRACT_CONDITION" object_type="INDEX (UNIQUE)" search_columns="1" cost="1" cardinality="1"/>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
<PlanElement object_ID="21" id="34" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="CONTRACT_CONDITION_SPEC" object_type="TABLE" object_instance="9" cost="3" cardinality="1" bytes="47">
<PlanElements>
<PlanElement object_ID="22" id="35" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="FK_CONTRACT_RFERENCE_CONTRACT" object_type="INDEX" search_columns="1" cost="2" cardinality="1"/>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
<PlanElement object_ID="23" id="36" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="PRODUCT_CONDITION" object_type="TABLE" object_instance="10" cost="1" cardinality="1" bytes="15">
<PlanElements>
<PlanElement object_ID="24" id="37" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="PK_PRODUCT_CONDITION" object_type="INDEX (UNIQUE)" search_columns="1" cost="0" cardinality="1"/>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
<PlanElement object_ID="25" id="38" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="INSURANCE_RULE_SPEC" object_type="TABLE" object_instance="11" cost="1" cardinality="1" bytes="46">
<PlanElements>
<PlanElement object_ID="26" id="39" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="PK_INSURANCE_RULE_SPEC" object_type="INDEX (UNIQUE)" search_columns="1" cost="0" cardinality="1"/>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
</PlanElements>
</PlanElement>
</ExplainPlan>

К сообщению приложен файл (10g.XML - 12Kb) cкачать
25 дек 08, 10:48    [6618017]     Ответить | Цитировать Сообщить модератору
 Re: помогите убрать HASH JOIN и CARTESIAN  [new]
Добрый Э - Эх
Guest
[quot HelpMePls]план для 10ки

<ExplainPlan>
  <PlanElement object_ID="0" id="0" operation="SELECT STATEMENT" optimizer="HINT: ALL_ROWS" cost="160" cardinality="1" bytes="833">
    <PlanElements>
      <PlanElement object_ID="0" id="1" operation="NESTED LOOPS" option="OUTER" cost="160" cardinality="1" bytes="833">
        <PlanElements>
          <PlanElement object_ID="0" id="2" operation="NESTED LOOPS" option="OUTER" cost="159" cardinality="1" bytes="787">
            <PlanElements>
              <PlanElement object_ID="0" id="3" operation="NESTED LOOPS" option="OUTER" cost="158" cardinality="1" bytes="772">
                <PlanElements>
                  <PlanElement object_ID="0" id="4" operation="NESTED LOOPS" cost="155" cardinality="1" bytes="725">
                    <PlanElements>
                      <PlanElement object_ID="0" id="5" operation="NESTED LOOPS" cost="153" cardinality="1" bytes="705">
                        <PlanElements>
                          <PlanElement object_ID="0" id="6" operation="NESTED LOOPS" cost="150" cardinality="1" bytes="601">
                            <PlanElements>
                              <PlanElement object_ID="0" id="7" operation="NESTED LOOPS" cost="149" cardinality="1" bytes="596">
                                <PlanElements>
                                  <PlanElement object_ID="0" id="8" operation="NESTED LOOPS" cost="146" cardinality="2" bytes="1,172">
                                    <PlanElements>
                                      <PlanElement object_ID="0" id="9" operation="NESTED LOOPS" option="OUTER" cost="143" cardinality="1" bytes="543">
                                        <PlanElements>
                                          <PlanElement object_ID="0" id="10" operation="NESTED LOOPS" cost="141" cardinality="1" bytes="513">
                                            <PlanElements>
                                              <PlanElement object_ID="0" id="11" operation="NESTED LOOPS" cost="140" cardinality="1" bytes="244">
                                                <PlanElements>
                                                  <PlanElement object_ID="0" id="12" operation="NESTED LOOPS" cost="54" cardinality="85" bytes="15,045">
                                                    <PlanElements>
                                                      <PlanElement object_ID="0" id="13" operation="NESTED LOOPS" cost="29" cardinality="18" bytes="2,628">
                                                        <PlanElements>
                                                          <PlanElement object_ID="1" id="14" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="CONTRACT_STATUS_TYPE" object_type="TABLE" object_instance="15" cost="3" cardinality="1" bytes="25"/>
                                                          <PlanElement object_ID="2" id="15" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="OPERDOC" object_type="TABLE" object_instance="12" cost="26" cardinality="13" bytes="1,573">
                                                            <PlanElements>
                                                              <PlanElement object_ID="3" id="16" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="INDX_DOC_DATE" object_type="INDEX" search_columns="1" cost="1" cardinality="68"/>
                                                            </PlanElements>
                                                          </PlanElement>
                                                        </PlanElements>
                                                      </PlanElement>
                                                      <PlanElement object_ID="4" id="17" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="OPERDOC_ROW1" object_type="TABLE" object_instance="13" cost="2" cardinality="5" bytes="155">
                                                        <PlanElements>
                                                          <PlanElement object_ID="5" id="18" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="OPERDOCROW1_OPERDOC_FK" object_type="INDEX" search_columns="1" cost="1" cardinality="5"/>
                                                        </PlanElements>
                                                      </PlanElement>
                                                    </PlanElements>
                                                  </PlanElement>
                                                  <PlanElement object_ID="6" id="19" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="CONTRACT_VARIANT" object_type="TABLE" object_instance="2" cost="1" cardinality="1" bytes="67">
                                                    <PlanElements>
                                                      <PlanElement object_ID="7" id="20" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="PK_CONTRACT_VARIANT" object_type="INDEX (UNIQUE)" search_columns="1" cost="0" cardinality="1"/>
                                                    </PlanElements>
                                                  </PlanElement>
                                                </PlanElements>
                                              </PlanElement>
                                              <PlanElement object_ID="8" id="21" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="CONTRACT" object_type="TABLE" object_instance="1" cost="1" cardinality="1" bytes="269">
                                                <PlanElements>
                                                  <PlanElement object_ID="9" id="22" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="PK_CONTRACT" object_type="INDEX (UNIQUE)" search_columns="1" cost="0" cardinality="1"/>
                                                </PlanElements>
                                              </PlanElement>
                                            </PlanElements>
                                          </PlanElement>
                                          <PlanElement object_ID="10" id="23" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="CONTRACT_RESTRICTION_TER" object_type="TABLE" object_instance="14" cost="2" cardinality="1" bytes="30">
                                            <PlanElements>
                                              <PlanElement object_ID="11" id="24" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="FK_CONTVARIANT_RESTR_TER" object_type="INDEX" search_columns="1" cost="1" cardinality="1"/>
                                            </PlanElements>
                                          </PlanElement>
                                        </PlanElements>
                                      </PlanElement>
                                      <PlanElement object_ID="12" id="25" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="CONTRACT_SUBJECT" object_type="TABLE" object_instance="6" cost="3" cardinality="1" bytes="43">
                                        <PlanElements>
                                          <PlanElement object_ID="13" id="26" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="FK_CONTRACT_REF_CONT_SUBJ" object_type="INDEX" search_columns="1" cost="2" cardinality="1"/>
                                        </PlanElements>
                                      </PlanElement>
                                    </PlanElements>
                                  </PlanElement>
                                  <PlanElement object_ID="14" id="27" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="SUBJECT_RELATION" object_type="TABLE" object_instance="7" cost="2" cardinality="1" bytes="10">
                                    <PlanElements>
                                      <PlanElement object_ID="15" id="28" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="RELATION_2602_FK" object_type="INDEX" search_columns="1" cost="1" cardinality="2"/>
                                    </PlanElements>
                                  </PlanElement>
                                </PlanElements>
                              </PlanElement>
                              <PlanElement object_ID="16" id="29" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="PK_SUBJECT" object_type="INDEX (UNIQUE)" search_columns="1" cost="1" cardinality="1" bytes="5"/>
                            </PlanElements>
                          </PlanElement>
                          <PlanElement object_ID="17" id="30" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="INSURANCE_OBJECT" object_type="TABLE" object_instance="4" cost="3" cardinality="1" bytes="104">
                            <PlanElements>
                              <PlanElement object_ID="18" id="31" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="RELATION_256_FK" object_type="INDEX" search_columns="1" cost="2" cardinality="1"/>
                            </PlanElements>
                          </PlanElement>
                        </PlanElements>
                      </PlanElement>
                      <PlanElement object_ID="19" id="32" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="CONTRACT_CONDITION" object_type="TABLE" object_instance="3" cost="2" cardinality="1" bytes="20">
                        <PlanElements>
                          <PlanElement object_ID="20" id="33" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="PK_CONTRACT_CONDITION" object_type="INDEX (UNIQUE)" search_columns="1" cost="1" cardinality="1"/>
                        </PlanElements>
                      </PlanElement>
                    </PlanElements>
                  </PlanElement>
                  <PlanElement object_ID="21" id="34" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="CONTRACT_CONDITION_SPEC" object_type="TABLE" object_instance="9" cost="3" cardinality="1" bytes="47">
                    <PlanElements>
                      <PlanElement object_ID="22" id="35" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="FK_CONTRACT_RFERENCE_CONTRACT" object_type="INDEX" search_columns="1" cost="2" cardinality="1"/>
                    </PlanElements>
                  </PlanElement>
                </PlanElements>
              </PlanElement>
              <PlanElement object_ID="23" id="36" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="PRODUCT_CONDITION" object_type="TABLE" object_instance="10" cost="1" cardinality="1" bytes="15">
                <PlanElements>
                  <PlanElement object_ID="24" id="37" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="PK_PRODUCT_CONDITION" object_type="INDEX (UNIQUE)" search_columns="1" cost="0" cardinality="1"/>
                </PlanElements>
              </PlanElement>
            </PlanElements>
          </PlanElement>
          <PlanElement object_ID="25" id="38" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="INSURANCE_RULE_SPEC" object_type="TABLE" object_instance="11" cost="1" cardinality="1" bytes="46">
            <PlanElements>
              <PlanElement object_ID="26" id="39" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="UNC10G_B" object_name="PK_INSURANCE_RULE_SPEC" object_type="INDEX (UNIQUE)" search_columns="1" cost="0" cardinality="1"/>
            </PlanElements>
          </PlanElement>
        </PlanElements>
      </PlanElement>
    </PlanElements>
  </PlanElement>
</ExplainPlan>[/quot]
Сделай нормальный текстовый план... Такую портянку читать нереально...
25 дек 08, 10:50    [6618027]     Ответить | Цитировать Сообщить модератору
 Re: помогите убрать HASH JOIN и CARTESIAN  [new]
HelpMePls
Member

Откуда:
Сообщений: 345
я файлы прикрепил к каждому плану.

файлы в XML сделаны из TOAD
25 дек 08, 11:01    [6618112]     Ответить | Цитировать Сообщить модератору
 Re: помогите убрать HASH JOIN и CARTESIAN  [new]
HelpMePls
Member

Откуда:
Сообщений: 345
+ORDERED то декартово подымается вверх.

а таблицы contract и contract_variant в HASH JOIN становятся


BUFFER SORT идет по таблице operdoc

К сообщению приложен файл (ORDERED.XML - 12Kb) cкачать
25 дек 08, 11:41    [6618410]     Ответить | Цитировать Сообщить модератору
 Re: помогите убрать HASH JOIN и CARTESIAN  [new]
HelpMePls
Member

Откуда:
Сообщений: 345
лишний + который оптимизатор сам убирал. (старый был запрос ктото когда то писал.)

тема закрыта. спасибо за просмотры :)
25 дек 08, 11:49    [6618512]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить