Optimizer using an index not present in the current schema

Question

CONNECT alll/all SELECT /*+ FIRST_ROWS(25) */ employee_id, department_id FROM hr.employees WHERE department_id > 50; Execution Plan Plan hash value: 2056577954 | Id | Operation | Name | Rows | Bytes | | 0 | SELECT STATEMENT | | 25 | 200 | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 25 | 200 |* 2 | INDEX RANGE SCAN | **EMP_DEPARTMENT_IX** | | SQL> select * from user_indexes where index_name = 'EMP_DEPARTMENT_IX'; no rows selected

NOTE: There is an index with the same name on the DEPARTMENT column of the EMPLOYEES table in some other schema. And when that index is dropped a FULL TABLE SCAN on EMPLOYEES is performed.

Can the optimizer use that other index from some other schema over here?

Alex Poole · Accepted Answer · 2015-06-16 14:17:07Z

You're connected as user ALLL, but you're querying a table in the HR schema:

SELECT /*+ FIRST_ROWS(25) */ employee_id, department_id FROM hr.employees WHERE department_id > 50;

You stressed other schema in the question, but seem to have overlooked that the table you're querying is also in another schema. The employees table won't appear in user_tables either.

The index being used is associated with that table, so it's likely to be in the same HR schema. You can see it in all_indexes or dba_indexes; the optimiser will use it even if you can't see it though. And it doesn't have to be in the same schema as the table, though it usually will be; in those views you might notice separate owner and table owner columns.

The schema model would break down if you could only utilise indexes in your own schema when accessing a table in someone else's. Every user would have to create their own copies of the indexes, which would be untenable.

You don't even necessarily have to be able to see the table - if you query a view that hides the underlying table from you (so you have select privs on the view only) the index will still be used in the background. And you might not always be explicitly using the schema prefix, if there is a synonym for the table, or you change your default schema.

TobyLL · Accepted Answer · 2015-06-16 12:48:57Z

Try looking in SYS.INDEXES:

select * from SYS.INDEXES where IXNAME = 'EMP_DEPARTMENT_IX'

Sounds like you are not the owner of the index, as you have noted. As long as your user can access the table data, then the index should be used by the optimizer.

The Oracle equivalent would be all_indexes or dba_indexes, and querying on index_name.

Collectives™ on Stack Overflow

Optimizer using an index not present in the current schema

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related