<p>If I create an index on columns (A, B, C), in that order, my understanding is that the database will be able to use it even if I search only on (A), or (A and B), or (A and B and C), but not if I search only on (B), or (C), or (B and C). Is this correct?</p>

[{'answer_id': 59370, 'author': 'Ethan Post', 'author_id': 4527, 'author_profile': 'https://Stackoverflow.com/users/4527', 'pm_score': 3, 'selected': False, 'text': "<p>That is not correct. Always best to come up with a test case that represents your data and see for yourself. If you want to really understand the Oracle SQL Optimizer google Jonathan Lewis, read his books, read his blog, check out his website, the guy is amazing, and he always generates test cases.</p>\n\n<pre><code>create table mytab nologging as (\nselect mod(rownum, 3) x, rownum  y, mod(rownum, 3) z from all_objects, (select 'x' from user_tables where rownum &lt; 4)\n);\n\ncreate index i on mytab (x, y, z);\n\nexec dbms_stats.gather_table_stats(ownname=&gt;'DBADMIN',tabname=&gt;'MYTAB', cascade=&gt;true);\n\nset autot trace exp\n\nselect * from mytab where y=5000;\n\nExecution Plan\n----------------------------------------------------------\n   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=10)\n   1    0   INDEX (SKIP SCAN) OF 'I' (INDEX) (Cost=1 Card=1 Bytes=10)\n</code></pre>\n"}, {'answer_id': 62465, 'author': 'stuandgravy', 'author_id': 6699, 'author_profile': 'https://Stackoverflow.com/users/6699', 'pm_score': 3, 'selected': False, 'text': '<p><strong>Up to version Oracle 8</strong> an index will never be used unless the first column is included in the SQL. </p>\n\n<p>In Oracle 9i the <strong>Skip Scan Index Access</strong> feature was introduced, which lets the Oracle CBO attempt to use indexes even when the prefix column is not available. </p>\n\n<p>Good overview of how skip scan works here: <a href="http://www.quest-pipelines.com/newsletter-v5/1004_C.htm" rel="noreferrer">http://www.quest-pipelines.com/newsletter-v5/1004_C.htm</a></p>\n'}, {'answer_id': 66581, 'author': 'David Aldridge', 'author_id': 6742, 'author_profile': 'https://Stackoverflow.com/users/6742', 'pm_score': 5, 'selected': True, 'text': '<p>There are actually three index-based access methods that Oracle can use when a predicate is placed on a non-leading column of an index.</p>\n\n<p>i) Index skip-scan: <a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#PFGRF10105" rel="noreferrer">http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#PFGRF10105</a></p>\n\n<p>ii) Fast full index scan: <a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i52044" rel="noreferrer">http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i52044</a></p>\n\n<p>iii) Index full scan: <a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i82107" rel="noreferrer">http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i82107</a></p>\n\n<p>I\'ve most often seen the fast full index scan "in the wild", but all are possible.</p>\n'}]
['https://Stackoverflow.com/questions/57878', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/5295/']