
|
If you were logged in you would be able to see more operations.
|
|
|
| Planning Status: |
Unspecified
|
|
Test case:
select *
from rdb$relations r
join rdb$security_classes sc
on (r.rdb$security_class = sc.rdb$security_class)
-- Good plan:
-- PLAN JOIN (R NATURAL, SC INDEX (RDB$INDEX_7))
select *
from rdb$relations r
join rdb$security_classes sc
on (r.rdb$security_class = sc.rdb$security_class
or r.rdb$default_class = sc.rdb$security_class)
-- Bad plan:
-- PLAN JOIN (R NATURAL, SC NATURAL)
-- Expected plan:
-- PLAN JOIN (R NATURAL, SC INDEX (RDB$INDEX_7, RDB$INDEX_7))
An attempt to specify the expected plan manually throws an error:
-- index RDB$INDEX_7 cannot be used in the specified plan
A prerequisite is that the slave index must be unique.
One more example is below. In fact, this is a bit different issue, although related and thus can be described by the same ticket.
select *
from rdb$relations r
join rdb$security_classes sc
on (r.rdb$security_class = sc.rdb$security_class and r.rdb$relation_id = 0)
or (r.rdb$default_class = sc.rdb$security_class and r.rdb$relation_id = 1)
-- Reported (bad) plan:
-- PLAN JOIN (R INDEX (RDB$INDEX_1, RDB$INDEX_1), SC NATURAL)
-- Expected plan:
-- PLAN JOIN (R INDEX (RDB$INDEX_1, RDB$INDEX_1), SC INDEX (RDB$INDEX_7, RDB$INDEX_7))
This time the prerequisite is that the OR predicate has two ANDed sub-conditions and one of them (filter) is indexable for the master table.
|
|
Description
|
Test case:
select *
from rdb$relations r
join rdb$security_classes sc
on (r.rdb$security_class = sc.rdb$security_class)
-- Good plan:
-- PLAN JOIN (R NATURAL, SC INDEX (RDB$INDEX_7))
select *
from rdb$relations r
join rdb$security_classes sc
on (r.rdb$security_class = sc.rdb$security_class
or r.rdb$default_class = sc.rdb$security_class)
-- Bad plan:
-- PLAN JOIN (R NATURAL, SC NATURAL)
-- Expected plan:
-- PLAN JOIN (R NATURAL, SC INDEX (RDB$INDEX_7, RDB$INDEX_7))
An attempt to specify the expected plan manually throws an error:
-- index RDB$INDEX_7 cannot be used in the specified plan
A prerequisite is that the slave index must be unique.
One more example is below. In fact, this is a bit different issue, although related and thus can be described by the same ticket.
select *
from rdb$relations r
join rdb$security_classes sc
on (r.rdb$security_class = sc.rdb$security_class and r.rdb$relation_id = 0)
or (r.rdb$default_class = sc.rdb$security_class and r.rdb$relation_id = 1)
-- Reported (bad) plan:
-- PLAN JOIN (R INDEX (RDB$INDEX_1, RDB$INDEX_1), SC NATURAL)
-- Expected plan:
-- PLAN JOIN (R INDEX (RDB$INDEX_1, RDB$INDEX_1), SC INDEX (RDB$INDEX_7, RDB$INDEX_7))
This time the prerequisite is that the OR predicate has two ANDed sub-conditions and one of them (filter) is indexable for the master table. |
Show » |
| There are no comments yet on this issue.
|
|