Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Very wide topic.</p> <p>From Oracle documentation <code>"When using hints, in some cases, you might need to specify a full set of hints in order to ensure the optimal execution plan. For example, if you have a very complex query, which consists of many table joins, and if you specify only the INDEX hint for a given table, then the optimizer needs to determine the remaining access paths to be used, as well as the corresponding join methods. Therefore, even though you gave the INDEX hint, the optimizer might not necessarily use that hint, because the optimizer might have determined that the requested index cannot be used due to the join methods and access paths selected by the optimizer."</code></p> <p>It avoids indexes and go for a FTS.</p> <p>This has exceptions, like all general concepts. A full table scan can be less expensive than an index scan followed by table access by rowid - sometimes much less expensive.</p> <p>An excerpt <a href="https://dba.stackexchange.com/questions/27874/why-does-my-query-run-faster-after-i-force-full-table-scan">from</a>:</p> <p>Index blocks need to be read too, in addition to the table's blocks. If the index is large (a sizable percentage of the table's size), the pressure on the SGA (and associates latches) might be noticeable If the sort order of the index doesn't match the way the data is stored in the actual table, the number of logical I/Os necessary to fulfill the query could be (potentially much) more than the number of LIOs to do the full table scan. (The index's clustering factor is one of the indicators you can look at to estimate this.)</p> <p>Essentially, if scanning via your index makes the data block I/O requests jump all over your table, the overall cost is going to be higher than if you could do large sequential reads. An FTS is more likely to use multi-block direct-path reads, bypassing the SGA entirely, which is also potentially good - no "cache thrashing", less latching.</p> <p>If you have a covering index, chances are that's going to beat a full table scan all the time. If not, it's going to depend on what percentage of actual blocks (data + index) will need to be processed (the index's selectivity for that query), and how well they're "physically sorted" with respect to each-other.</p> <p>As to why the optimizer is picking the "wrong" path for you here: hard to tell. Stale statistics on the index or table could be an issue like always, the estimate calculated base on the LIKE might be off for certain patterns, instance parameters could favor indexes a bit too much, ... If this is the only query that's misbehaving, and your stats are up to date, using a /*+ full */ hint doesn't sound too bad.</p> <p>Also read Stack <a href="https://web.archive.org/web/20170915130327/https://stackoverflow.com/documentation/oracle/1490/hints" rel="nofollow noreferrer">documentation</a></p>
    singulars
    1. This table or related slice is empty.
    1. POOracle SQL when to use the full hint
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMT0
    1. USSriniV
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POOracle SQL when to use the full hint
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. 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