SQL & PL/SQL

All,

I couldn't find someone with this problem, so I create a new thread.

I have this problem where Oracle uses an index when I log on using 'as sysdba', and refuses to use the index when I log on without the 'as sysdba' option.

I use oracle 9.2.0.6, and have a XML schema registered in the documents table. The schema defines a root element 'customerId' which is a number. There is a 1-N relationship from customers to documents.

create table customers (id number(19,0) primary key, name varchar2(10));
alter table documents add constraint pk_docid primary key (xmldata."documentId");
alter table documents add constraint fk_customer foreign key (xmldata."customerId") references customers(id);

create index customerid_index on documents (xmldata."customerId");

The following is a transcript from executing a query on the table structure:

SQL> conn scott/tiger@database as sysdba
SQL> set autotrace on
SQL> select count(*)
2 from scott.customers c
3 left join scott.documents d on c.id = d.xmldata."customerId"
4 where c.id > 100 and c.id < 200;

COUNT(*)
----------
2


Uitvoeringspan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=26)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (OUTER) (Cost=5 Card=8 Bytes=208)
3 2 INDEX (RANGE SCAN) OF 'SYS_C007603' (UNIQUE) (Cost=2 Card=7 Bytes=91)
4 2 INDEX (RANGE SCAN) OF 'CUSTOMERID_INDEX' (NON-UNIQUE) (Cost=2 Card=294 Bytes=3822)

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

SQL> conn scott/tiger@database
Verbonden.
SQL> set autotrace on
SQL> select count(*)
2 from scott.customers c
3 left join scott.documents d on c.id = d.xmldata."customerId"
4 where c.id > 100 and c.id < 200;

COUNT(*)
----------
2


Uitvoeringspan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=143 Card=1 Bytes=113075)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (OUTER) (Cost=143 Card=7 Bytes=791525)
3 2 INDEX (RANGE SCAN) OF 'SYS_C007603' (UNIQUE) (Cost=2 Card=7 Bytes=91)
4 2 TABLE ACCESS (FULL) OF 'DOCUMENTS' (Cost=140 Card=3 Bytes=339186)

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

SQL>

How is it possible that the SYSDBA privilege makes the index available to the query?

I also tried granting SYSDBA to scott, but that didn't work. I still had to logon using

SQL> conn scott/tiger@database as sysdba

for Oracle to use the index.

Can anyone help?