If I create an index on column (a,b,c) then
a, ab, abc combination of columns in where clause will use the index.
bc, b, and c combinations of columns in where clause will not use the index.
The example is given below.
SQL> create table comp_tab(a number, b varchar2(10), c varchar2(10));
Table created.
SQL> begin
for i in 1 .. 10000
loop
insert into comp_tab values(i,'pc-'||round(dbms_random.value(1,20000),0),'pc-'||round(dbms_random.value(1,20000),0));
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> create index comp_tab_I on comp_tab(a,b,c);
Index created.
SQL> analyze table comp_tab estimate statistics;
Table analyzed.
SQL> set autot trace
a in where cluase will use the index as a is leading portion of index.
SQL> select a,c,b from comp_tab where a=565;
Execution Plan
----------------------------------------------------------
Plan hash value: 753992557
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| COMP_TAB_I | 1 | 19 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
ac combination will use the index.
SQL> select a,c,b from comp_tab where a=565 and c='pc-3722';
Execution Plan
----------------------------------------------------------
Plan hash value: 753992557
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| COMP_TAB_I | 1 | 19 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
bc combination is used in where clause so will not use index.
SQL> select b,c,a from comp_tab where b='pc-5895' and c='pc-2893';
Execution Plan
----------------------------------------------------------
Plan hash value: 3767570742
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 11 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| COMP_TAB | 1 | 19 | 11 (0)| 00:00:01 |
------------------------------------------------------------------------------
Only b combination is used so will not use the index.
SQL> select a,c,b from comp_tab where b='pc-6735';
Execution Plan
----------------------------------------------------------
Plan hash value: 3767570742
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 11 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| COMP_TAB | 1 | 19 | 11 (0)| 00:00:01 |
------------------------------------------------------------------------------
abc combination is used in where clause and so will use the index.
SQL> select b,c,a from comp_tab where b='pc-5895' and c='pc-2893' and a=564;
Execution Plan
----------------------------------------------------------
Plan hash value: 753992557
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| COMP_TAB_I | 1 | 19 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------
ab combination in where clause will use the index.
SQL> select c from comp_tab where b='pc-5895' and a=564;
Execution Plan
----------------------------------------------------------
Plan hash value: 753992557
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| COMP_TAB_I | 1 | 19 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Related Documents
http://arjudba.blogspot.com/2009/12/oracle-object-type-exercises-varray.html
http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html
http://arjudba.blogspot.com/2009/12/oracle-security-practices.html
http://arjudba.blogspot.com/2009/12/exercises-with-oracle-create-table-add.html
http://arjudba.blogspot.com/2009/12/oracle-database-creation-exercises.html
http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html
http://arjudba.blogspot.com/2009/08/format-model-modifiers-fx-and-fm.html
http://arjudba.blogspot.com/2009/08/number-format-models-in-oracle.html
http://arjudba.blogspot.com/2009/08/format-models-in-oracle.html
http://arjudba.blogspot.com/2009/07/sql-decode.html
http://arjudba.blogspot.com/2009/07/how-to-know-row-of-table-belong-to.html
http://arjudba.blogspot.com/2009/06/how-to-know-which-objects-are-being.html
http://arjudba.blogspot.com/2009/06/ddl-with-wait-option-in-11g.html
http://arjudba.blogspot.com/2009/06/ora-00939-too-many-arguments-when-case.html
http://arjudba.blogspot.com/2009/03/oracle-datatype-internal-code.html
http://arjudba.blogspot.com/2009/03/how-to-know-list-of-constraints-and.html
http://arjudba.blogspot.com/2009/02/how-to-know-dependent-objectswhich.html
http://arjudba.blogspot.com/2009/02/how-to-search-stringkey-value-from.html
http://arjudba.blogspot.com/2009/02/how-to-know-when-tableobjects-ddlcode.html
http://arjudba.blogspot.com/2009/02/ora-00920-invalid-relational-operator.html
http://arjudba.blogspot.com/2009/01/adding-default-value-to-column-on-table.html
http://arjudba.blogspot.com/2009/01/ora-12838-cannot-readmodify-object.html
http://arjudba.blogspot.com/2009/01/ora-01779-cannot-modify-column-which.html
http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/12/troubleshoot-ora-02292-ora-02449-and.html
http://arjudba.blogspot.com/2008/06/ora-00903-oracle-database-reserved.html
http://arjudba.blogspot.com/2008/06/hints-in-oracle.html
http://arjudba.blogspot.com/2008/06/examples-of-usage-of-composite-index.html
http://arjudba.blogspot.com/2008/06/find-indexes-and-assigned-columns-for.html
http://arjudba.blogspot.com/2008/06/reasons-for-using-alter-table-statement.html
http://arjudba.blogspot.com/2008/06/alter-table-rename-table-add-column.html
http://arjudba.blogspot.com/2008/06/ora-01830-date-format-picture-ends.html
http://arjudba.blogspot.com/2008/06/default-date-timestamp-and-timestamp.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
http://arjudba.blogspot.com/2008/06/example-of-antijoin-semijoin-curtesian.html
http://arjudba.blogspot.com/2008/12/ora-02297-cannot-disable-constraint.html
http://arjudba.blogspot.com/2008/10/convert-decimal-to-hexadecimal-on.html
http://arjudba.blogspot.com/2008/10/how-to-generate-fibonacci-series-in.html
http://arjudba.blogspot.com/2008/10/same-sounded-words-in-oracle.html
http://arjudba.blogspot.com/2008/09/type-of-constraint-in-oracle.html
http://arjudba.blogspot.com/2008/09/how-to-move-lob-data-to-another.html
http://arjudba.blogspot.com/2008/08/subqueries-in-oracle-with-example.html
http://arjudba.blogspot.com/2008/08/how-to-monitor-alert-log-file-in-oracle.html
http://arjudba.blogspot.com/2008/08/solution-of-ora-01873-leading-precision.html
http://arjudba.blogspot.com/2008/07/literals-and-literal-types-in-oracle.html
http://arjudba.blogspot.com/2008/07/ora-01722-invalid-number.html
http://arjudba.blogspot.com/2008/07/ora-00936-missing-expression.html
http://arjudba.blogspot.com/2008/07/ora-01756-quoted-string-not-properly.html
http://arjudba.blogspot.com/2008/07/pls-00428-into-clause-is-expected-in.html
http://arjudba.blogspot.com/2008/07/schema-object-naming-rules.html
http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes.html
http://arjudba.blogspot.com/2008/06/large-object-lob-datatypes-with-example.html
http://arjudba.blogspot.com/2008/06/history-of-sql.html
http://arjudba.blogspot.com/2008/06/what-is-sql.html
No comments:
Post a Comment