08 August, 2009

Using an Index created by a different user

Can a query by user "C" on a table owned by user "A" actually use an Index owned and created by "B", although on "A"'s table ?
YES. "C" needs the SELECT privilege on "A"'s table as would be necessary to run the query but does not need any reference/privilege on "B"'s index.


Here's an example :

Table owner : HEMANT
Index owner : AN_INDEX_OWNER
Query run by : A_QUERY_USER

Account "A_QUERY_USER" has SELECT on the table "MY_COPY_OF_OBJECTS" in HEMANT's schema but no privileges on the index in AN_INDEX_OWNER's schema (there's no such thing as granting SELECT on an Index)
Account "AN_INDEX_OWNER" has SELECT on "MY_COPY_OF_OBJECTS" in HEMANT's schema and the privilege to create an Index

When "A_QUERY_USER" runs a query on HEMANT's table, the query does use the Index created by "AN_INDEX_OWNER" !



SQL> connect hemant/hemant
Connected.
SQL> create table my_copy_of_objects as select * from dba_objects;

Table created.

SQL> exec dbms_stats.gather_table_stats('','MY_COPY_OF_OBJECTS',estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL>
SQL> create user a_query_user identified by a_query_user;

User created.

SQL> grant create session to a_query_user;

Grant succeeded.

SQL> grant plustrace to a_query_user;

Grant succeeded.

SQL> grant select on my_copy_of_objects to a_query_user;

Grant succeeded.

SQL>
SQL> create user an_index_owner identified by an_index_owner;

User created.

SQL> -- needs "CREATE ANY INDEX" and SELECT to be able to create an index
SQL> grant create session,create any index to an_index_owner;

Grant succeeded.

SQL> grant select on my_copy_of_objects to an_index_owner;

Grant succeeded.

SQL> alter user an_index_owner default tablespace users;

User altered.

SQL> alter user an_index_owner quota unlimited on users;

User altered.

SQL>
SQL> connect an_index_owner/an_index_owner
Connected.
SQL> create index hemant_m_c_o_o_ndx_1 on hemant.my_copy_of_objects(object_id);

Index created.

SQL> create index hemant_m_c_o_o_ndx_2 on hemant.my_copy_of_objects(owner);

Index created.

SQL>
SQL> -- Verify indexes owned by AN_INDEX_OWNER
SQL> select index_name, table_owner, table_name from user_indexes order by 1;

INDEX_NAME TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------ ------------------------------
HEMANT_M_C_O_O_NDX_1 HEMANT MY_COPY_OF_OBJECTS
HEMANT_M_C_O_O_NDX_2 HEMANT MY_COPY_OF_OBJECTS

SQL>
SQL> -- Verify that indexes on HEMANT's tables are owned by AN_INDEX_OWNER
SQL> connect / as sysdba
Connected.
SQL> select owner, index_name, table_owner, table_name from dba_indexes where table_name = 'MY_COPY_OF_OBJECTS' order by 1,2;

OWNER INDEX_NAME TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
AN_INDEX_OWNER HEMANT_M_C_O_O_NDX_1 HEMANT MY_COPY_OF_OBJECTS
AN_INDEX_OWNER HEMANT_M_C_O_O_NDX_2 HEMANT MY_COPY_OF_OBJECTS

SQL>
SQL> REM REM REM ################
SQL>
SQL> -- now we query the table
SQL> connect a_query_user/a_query_user
Connected.
SQL> select owner, object_name, object_id from hemant.my_copy_of_objects where object_id > 54087;

OWNER OBJECT_NAME OBJECT_ID
------------------------------ ------------------------------ ----------
HEMANT MY_COPY_OF_OBJECTS 54123

SQL> select count(*) from hemant.my_copy_of_objects where owner = 'HEMANT';

COUNT(*)
----------
17

SQL>
SQL> -- rerun the queries, we avoid parse overheads now
SQL>
SQL> set autotrace on
SQL> select owner, object_name, object_id from hemant.my_copy_of_objects where object_id > 54087;

OWNER OBJECT_NAME OBJECT_ID
------------------------------ ------------------------------ ----------
HEMANT MY_COPY_OF_OBJECTS 54123


Execution Plan
----------------------------------------------------------
Plan hash value: 2159204631

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 1224 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MY_COPY_OF_OBJECTS | 34 | 1224 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | HEMANT_M_C_O_O_NDX_1 | 34 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

2 - access("OBJECT_ID">54087)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
676 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(*) from hemant.my_copy_of_objects where owner = 'HEMANT';

COUNT(*)
----------
17


Execution Plan
----------------------------------------------------------
Plan hash value: 360370019

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX RANGE SCAN| HEMANT_M_C_O_O_NDX_2 | 1876 | 11256 | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

2 - access("OWNER"='HEMANT')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

.
.
.

8 comments:

Anand said...

Hi Hemant Sir,

Read the blog.Its interesting.I thought it won't,but it did use the index.Good post!!

Regards,
Anand

Brian Tkatch said...

Interesting.

Thanx for the lesson Hemant!

PrazY said...

Good post indeed!!! Tx...

Anonymous said...

Will this work on a RULE based optimizer setting? i mean we do not have any statistics for any objects.

Hemant K Chitale said...

Anonymous,
Yes, it should work on RBO as well -- as long as the index *is* the correct index for your query.

Please revert if you find that it doesn't work.

Anonymous said...

Nice

Ricardo said...

Hi Hemant,

Sorry for asking off-topic question.

My current 10g database has WE8ISO8859P1 character set. I want to install a fresh 11g database and use exp/imp utility to move the data.
I notice that WE8ISO8859P1 is not available in 11g hence I have to use WE8MSWIN1252.
We have some chinese characters in the database therefore I am not sure whether I should use WE8MSWIN1252 or unicode (AL32UTF8)

What is the disadvantage of using WE8ISO8859P1 as the database character set and using AL16UTF16 as the National character set ?
In this case we will need to use Nchar datatype to store chinese characters

Thanks in advance

Hemant K Chitale said...

Ricardo,

You wouldn't be able to *store* Chinese characters (which are multibyte) properly in an 8-bit characterset (WE8ISO8859P1 OR WE8MSWIN1252).

AL32UTF8 is the recommended character set.

See http://download.oracle.com/docs/cd/E11882_01/server.112/e10729/applocaledata.htm#i635016

Hemant K Chitale