StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>UPDATE:</strong> Try making the col column NOT NULL. That is the reason it's not using the index. When it's not null, here's the plan.</p> <pre><code>SELECT STATEMENT, GOAL = ALL_ROWS 69 10 30 HASH GROUP BY 69 10 30 INDEX FAST FULL SCAN SANDBOX TEST_INDEX 56 98072 294216 </code></pre> <p>If the optimizer determines that it's more efficient NOT to use the index (maybe due to rewriting the query), then it won't. Optimizer hints are just that, namely, hints to tell Oracle an index you'd <strong>like</strong> it to use. You can think of them as suggestions. But if the optimizer determines that it's better not to use the index (again, as result of query rewrite for example), then it's not going to.</p> <p>Refer to this link: <a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm" rel="nofollow noreferrer">http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm</a> "Specifying one of these hints causes the optimizer to choose the specified access path only if the access path is available based on the existence of an index or cluster and on the syntactic constructs of the SQL statement. If a hint specifies an unavailable access path, then the optimizer ignores it."</p> <p>Since you are running a count(*) operation, the optimizer has determined that it's more efficient to just scan the whole table and hash instead of using your index.</p> <p>Here's another handy link on hints: <a href="http://www.dba-oracle.com/t_hint_ignored.htm" rel="nofollow noreferrer">http://www.dba-oracle.com/t_hint_ignored.htm</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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