Wednesday, 25 September 2013

Oracle: join local index to global index results in error?

Oracle: join local index to global index results in error?

Suppose you have two partitioned tables:
table1:
- id (primary key = global index)
- created (timestamp of creation)
table2:
- id (primary key = global index)
- t1_id (foreign key to table1.id, a local index was created for this
field)
The partitioning is done on table1.created and table2 is partitioned by
reference on table2.t1_id.
Now if I run this query:
select * from table1 t1 join table2 t2 on t1.id=t2.t1_id
I get the ever awesome error: ORA-03113
I do not have access to the actual server running the oracle instance so I
can't check the alert logs. Google turned up nothing useful so after some
fiddling I thought that maybe the local index was the problem. I tried
adding the necessary clauses to limit the range of partitions that should
be targeted to no avail.
I replaced the local index on table2.t1_id with a global index and the
query works.
Now my question: is this a feature/bug? Or a logical consequence of how
local indexes work? Is it some other problem entirely which I accidently
fixed by recreating the index?
Oracle version: 11.2.0.2.0 - 64bit

No comments:

Post a Comment