Geek asks and answers

Oracle Index with multiple columns querying a single column

Our Oracle installation table has a table with an index on two columns (X and Y). If I make a query in a table with a where clause that only applies to column X, will Oracle be able to use the index?

For instance:

Table Y: col_a, Col_B, Col_C,

Pointer exists (Col_A, Col_B)

SELECT * FROM Table_Y WHERE Col_A = 'STACKOVERFLOW';

Will the index be used or will the table be scanned?

+5
source share
1 answer

It depends.

You can verify this by providing Oracle with an explanation of the execution plan:

EXPLAIN PLAN FOR 
   SELECT * FROM Table_Y WHERE Col_A = 'STACKOVERFLOW';

and then

select * from table(dbms_xplan.display);

So, for example, with

create table table_y (
  col_a varchar2(30),
  col_b varchar2(30),
  col_c varchar2(30)
);

create unique index table_y_ix on table_y (col_a, col_b);

and then a

explain plan for
  select * from table_y
  where col_a = 'STACKOVERFLOW';

select * from table(dbms_xplan.display);

The plan (for my installation) is as follows:

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    51 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE_Y    |     1 |    51 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TABLE_Y_IX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COL_A"='STACKOVERFLOW')

ID 2 shows you what the index is TABLE_Y_IXreally used for index range scan.

Oracle , . Oracle, .

. , ( , Oracle , + index_asc(...) (. )

, -

SELECT /*+ index_asc(TABLE_Y TABLE_Y_IX) */ * 
  FROM Table_Y 
 WHERE Col_A = 'STACKOVERFLOW';

, , .

select last_analyzed from dba_tables where table_name = 'TABLE_Y';


select column_name, last_analyzed from dba_tab_columns where table_name = 'TABLE_Y';

, dbms_stats, ​​.

, .

+9

More articles:


All Articles