Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Something to note about indexes is that they are precomputed values based on the row order and the data in the field. In this specific case you say that field1 is indexed and you are using it in the query as follows:</p> <pre><code> where field1_ like '%someGenericString%' and field1_ &lt;&gt; 'someSpecificString' </code></pre> <p>In the query snippet above the filter is on both a variable piece of data since the percent (%) character cradles the string and then on another specific string. This means that the default Oracle optimization that doesn't use an optimizer hint will try to find the string inside the indexed field first and also find if the data it is a sub-string of the data in the field, then it will check that the data doesn't match another specific string. After the index is checked the other columns are then checked. This is a very slow process if repeated.</p> <p>The NO_INDEX hint proposed by the DBA removes the optimizer's preference to use an index and will likely allow the optimizer to choose the faster comparisons first and not necessarily force index comparison first and then compare other columns.</p> <p>The following is slow because it compares the string and its sub-strings:</p> <pre><code> field1_ like '%someGenericString%' </code></pre> <p>While the following is faster because it is specific:</p> <pre><code> field1_ like 'someSpecificString' </code></pre> <p>So the reason to use the NO_INDEX hint is if you have comparisons on the index that slow things down. If the index field is compared against more specific data then the index comparison is <strong>usually</strong> faster.</p> <p>I say <strong>usually</strong> because when the indexed field contains more redundant data like in the example @Atish mentions above, it will have to go through a long list of comparison negatives before a positive comparison is returned. Hints produce varying results because both the database design and the data in the tables affect how fast a query performs. So in order to apply hints you need to know if the individual comparisons you hint to the optimizer will be faster on your data set. There are no shortcuts in this process. Applying hints should happen after proper SQL queries have been written because hints should be based on the real data.</p> <p>Check out this hints reference: <a href="http://docs.oracle.com/cd/B19306_01/server.102/b14211/hintsref.htm" rel="nofollow">http://docs.oracle.com/cd/B19306_01/server.102/b14211/hintsref.htm</a></p>
    singulars
    1. This table or related slice is empty.
    1. POOracle - Understanding the no_index hint
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USwalid
    1. USwalid
    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
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

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