Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>No, this can be incorrect.</p> <p>If you have a large table, <code>Oracle</code> can prefer deriving the fields from the indexes rather than from the table, even there is no single index that covers all values.</p> <p>In the latest article in my blog:</p> <ul> <li><a href="http://explainextended.com/2009/09/17/not-in-vs-not-exists-vs-left-join-is-null-oracle/" rel="noreferrer"><strong><code>NOT IN</code> vs. <code>NOT EXISTS</code> vs. <code>LEFT JOIN / IS NULL</code>: <code>Oracle</code></strong></a></li> </ul> <p>, there is a query in which <code>Oracle</code> does not use full table scan but rather joins two indexes to get the column values:</p> <pre><code>SELECT l.id, l.value FROM t_left l WHERE NOT EXISTS ( SELECT value FROM t_right r WHERE r.value = l.value ) </code></pre> <p>The plan is:</p> <pre><code>SELECT STATEMENT HASH JOIN ANTI VIEW , 20090917_anti.index$_join$_001 HASH JOIN INDEX FAST FULL SCAN, 20090917_anti.PK_LEFT_ID INDEX FAST FULL SCAN, 20090917_anti.IX_LEFT_VALUE INDEX FAST FULL SCAN, 20090917_anti.IX_RIGHT_VALUE </code></pre> <p>As you can see, there is no <code>TABLE SCAN</code> on <code>t_left</code> here.</p> <p>Instead, <code>Oracle</code> takes the indexes on <code>id</code> and <code>value</code>, joins them on <code>rowid</code> and gets the <code>(id, value)</code> pairs from the join result.</p> <p>Now, to your query:</p> <pre><code>SELECT * FROM some_table WHERE field_one is not null and field_two = ? GROUP BY field_three, field_four, field_five </code></pre> <p>First, it will not compile, since you are selecting <code>*</code> from a table with a <code>GROUP BY</code> clause.</p> <p>You need to replace <code>*</code> with expressions based on the grouping columns and aggregates of the non-grouping columns.</p> <p>You will most probably benefit from the following index:</p> <pre><code>CREATE INDEX ix_sometable_23451 ON some_table (field_two, field_three, field_four, field_five, field_one) </code></pre> <p>, since it will contain everything for both filtering on <code>field_two</code>, sorting on <code>field_three, field_four, field_five</code> (useful for <code>GROUP BY</code>) and making sure that <code>field_one</code> is <code>NOT NULL</code>.</p>
    singulars
    1. This table or related slice is empty.
    1. POCreating Indexes for Group By Fields?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USQuassnoi
    1. USQuassnoi
    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. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COVery interesting - I don't think I've ever seen that before (where Oracle would join two indexes and avoid the table entirely)
      singulars
      1. PO
      1. USEric Petroelje
    2. CO`@Eric Petroelje`: there is a special hint, `INDEX_JOIN`, to force this method.
      singulars
      1. PO
      1. USQuassnoi
 

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