Indexes on two different table space( Associated to different schema)

Question: Can we create indexes on two different table spaces. ( Associated to different schema)

Comments: The answer is “Yes”. We can create a table in one table space and index in another table space.

Now the question is some thinking different. Can we create an index on some other user’s table space (Demo2_TS) which is not associated to the current user (Demo2_TS)? (Where table has created)

Scripts:
sql> conn demo1/demo1 ;
sql> create table table_in_demo1 ( col1 number, col2 number );
sql> insert into table_in_demo1 values (10,11);
sql> insert into table_in_demo1 values (20,21);
sql> insert into table_in_demo1 values (30,31);
sql> select * from table_in_demo1;

COL1 COL2
----- ------

10 11
20 21
30 31

sql> grant all on table_in_demo1 to demo2
sql> conn demo2/demo2
sql> select * from demo1.table_in_demo1
COL1 COL2
------ -----
10 11
20 21
30 31
Comments: Now the Demo2 is having the access to “table_in_demo1”. Can we create a index on table_in_demo1 table from DEMO2?

The table is available in the Demo1_TS table space. Will it allow to create a index in the
Demo2_TS. (The demo2 is associated with the Demo2_TS).

Let’s check in the Demo2 schema

Scripts:
sql> conn demo2/demo2
sql> create index tab_idx on demo1.table_in_demo1(col2);

sql> select owner,index_name,table_owner from all_indexes
where table_name = 'TABLE_IN_DEMO1';

Solution:
It is allowing programmer to create an index in Demo2 schema.

sql> conn demo1/demo1
sql> select * from table_in_demo1 where col2 = 21;
Explain plan from Demo1 schema:

So,

We can create an index on some other user’s table space (Demo2_TS) which is not associated to the current user (Demo1_TS)

Comments:
One more question. What will happen if we revoke the permission from Demo1 schema?

No. Two more question because of the above question.

Question 1: will it allow programmer to revoke the permission?
Question 2: if it allows programmer to revote, will it use the query use that index?

Answer for Question 1:sql> conn demo1/demo1
sql> revoke all on table_in_demo1 from demo2;
Yes. It is allowing programmer to revoke.

Answer for Question 2:
sql> select * from table_in_demo1 where col2 = 21;
Explain plan from Demo1 schema:

Wow! What a surprise! Still it is using Demo2 index.

We try to insert one record.

sql> conn demo1/demo1
sql> insert into table_in_demo1 values (40,41);
sql> select * from table_in_demo1;
COL1 COL2
----- ------
10 11
20 21
30 31
40 41
Yes. It is allowing programmer to insert.