title: Open-Sql translated to native oracle sql chooses arbitrary indices by where clause?
body: <p>We spotted a strange behaviour during a customer project. The underlying database is oracle, so this question is flagged for as oracle, because we only operate at the open-sql-layer, and this makes our knowledge of the underlying database-architecture/processes/optimizations equal to zero.</p>
  
  <p><strong>Let me describe the design as it is:</strong></p>
  
  <ol>
  <li>We have two tables , one is the core-table, the other is the 
  optional additional table</li>
  <li>The core table is always populated, the other table not</li>
  <li>The core table has the keys MANDT, WERKS and our own running ID</li>
  <li>The optional table has the same keys, which are set up as a set
   of foreing keys, referring to the core-table</li>
  <li>The foreign-key-setup is: Control desired, no message, kind of foreign keys is "not specified", no cardinality is set.</li>
  </ol>
  
  <p><strong>Let me now specify, what we do/want to do:</strong></p>
  
  <ol>
  <li>We have an own query builder, which allows us to create select-statements almost by a drag and drop technique.</li>
  <li>We want to select the core table with an inner join to the optional table ( yes, in this specific case master-data tells us, that this optional table is always populated, too), based on the three keyfields(two actually).</li>
  <li>We have some indices on both tables, in the core we use the indexed ERDAT( creation date ) and in the optional table we use an an char20-field, also indexed.</li>
  <li>We want to select excactly this way.</li>
  <li>We debug...</li>
  </ol>
  
  <p><strong>Let me describe, what we saw:</strong></p>
  
  <ol>
  <li>We analyzed the created select statement in the debugger, and verified, that
  it looked, as we want it to be, meaning, the where clause first covers ERDAT from the core table and the second line covers the char20-field of the optional table</li>
  <li>We made runtime analyzis (providing other values for the where-criteria), running ST05 etc... though the underlying database being a black-box to us, apparently ST05 could show the generated native SQL-index-order, which was used.</li>
  <li>The result: The index CHAR20 of the optional table is used first, and the index on ERDAT of thecore table is used as second.</li>
  </ol>
  
  <p><strong>Let me ask:</strong></p>
  
  <ol>
  <li>Can anybody explain, why ? (if more information is necessary, I will try to provide them ).</li>
  </ol>