Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>It would probably help to use inner joins instead.</p> <pre><code>Select A, B from TableA inner join TableB on TableA.A = TableB.C inner join TableC on TableB.C = TableC.D </code></pre> <p>Databases were designed for joins, but the optimizer might not figure out that it can use an index for a sub-query. Instead it will probably try to run the sub-query, hold the results in memory, and then do a linear search to evaluate the IN operator for every record.</p> <p>Now, you say that you have all of the necessary indexes. Consider this for a moment.</p> <p>If one optional condition is TableC.E = 'E' and another optional condition is TableC.F = 'F', then a query with both would need an index on fields TableC.E <strong>AND</strong> TableC.F. Many young programmers today think they can have one index on TableC.E and one index on TableC.F, and that's all they need. In fact, if you have both fields in the query, you need an index on both fields.</p> <p>So, for 100+ combinations, "all of the necessary indexes" could require 100+ indexes.</p> <p>Now an index on TableC.E, TableC.F could be use in a query with a TableC.E condition and no TableC.F condition, but could not be use when there is a TableC.F condition and no TableC.E condition.</p> <p>Hundreds of indexes? What am I going to do?</p> <p>In practice it's not that bad. Let's say you have N optional conditions which are either in the where clause or not. The number of combinations is 2 to the nth, or for hundreds of combinations, N is log2 of the number of combinations, which is between 6 and 10. Also, those log2 conditions are spread across three tables. Some databases support multiple table indexes, but I'm not sure DB2 does, so I'd stick with single table indexes.</p> <p>So, what I am saying is, say for the TableC.E, and TableC.F example, it's not enough to have just the following indexes:</p> <pre><code>TableB ON C TableC ON D TableC ON E TableC ON F </code></pre> <p>For one thing, the optimizer has to pick among which <em>one</em> of the last three indexes to use. Better would be to include the D field in the last two indexes, which gives us</p> <pre><code>TableB ON C TableC ON D, E TableC ON D, F </code></pre> <p>Here, if neither field E nor F is in the query, it can still index on D, but if either one is in the query, it can index on both D and one other field.</p> <p>Now suppose you have an index for 10 fields which may or may not be in the query. Why ever have just one field in the index? Why not add other fields in descending order of likelihood of being in the query?</p> <p>Consider that when planning your indexes.</p>
    singulars
    1. This table or related slice is empty.
    1. POSeveral layers of nested subqueries with Exists/In, best performance?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMarlin Pierce
    1. USMarlin Pierce
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COThe indexes are quite thorough and an execute plan currently shows only index scans. Going with joins the whole way through may require further indexes, but they're sufficient for the current queries.
      singulars
      1. PO
      1. USuser1017413
    2. CODB2 is probably re-writing the `IN` as a `JOIN` "under the covers", so the result is probably the same, either way. Have a look at [this Info Center entry](http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.perf.doc/doc/c0005292.html).
      singulars
      1. PO
      1. USbhamby
    3. CO@bhamby yes I know that database optimizers rewrite queries, and can often figure out that a query is equivalent to another query which can be indexed. I'm just saying the inner join is more obvious to an optimizer, and less likely to miss being about to use an index.
      singulars
      1. PO
      1. USMarlin Pierce
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload