We have a query which seems to be slowed down using bind variables.
However, it slows down by scanning another table, seemingly unrelated
to the bind.
The query looks like this normally with the
bind variables etc...
SELECT
DISTINCT role.role_id, name.last_name,
name.first_name
FROM role_category, docket_status_type,
docket, role_type, role, name, entity,
role_type_category
WHERE docket.docket_status_type_id =
docket_status_type.docket_status_type_id
AND role.docket_id=docket.docket_id
AND role.entity_id=name.entity_id
AND
role.role_type_id=role_type.role_type_id
AND
role_type_category.role_type_category_id =
role_category.role_type_category_id
AND role_type.role_type_id =
role_type_category.role_type_id
AND docket.case_type_id IN (3, 6, 9, 12,
15, 18, 21, 24, 27, 1, 4, 7, 10,
13, 16, 19, 22, 25, 2, 5, 8, 11, 14,
17, 20, 23, 26)
AND docket.court_entity_id = 98
AND docket_status_type.description <> :1
AND role_category.category_name = :2
AND upper(name.first_name) LIKE :3
AND upper(name.last_name) LIKE :4
and name.entity_id=role.entity_id
and entity.entity_id=name.entity_id
ORDER BY upper(name.last_name) ASC,
upper(name.first_name) ASC
The cost of this query is 2598 not terrible
but not great and results come back in 15-20 seconds. and always does a
full table scan of
Role no matter what I try. What I have found
is that if I don't bind the "LIKE" variables the query optimizer works
much better as in:
SELECT
DISTINCT role.role_id, name.last_name,
name.first_name
FROM role_category, docket_status_type,
docket, role_type, role, name, entity,
role_type_category
WHERE docket.docket_status_type_id =
docket_status_type.docket_status_type_id
AND role.docket_id=docket.docket_id
AND role.entity_id=name.entity_id
AND
role.role_type_id=role_type.role_type_id
AND
role_type_category.role_type_category_id =
role_category.role_type_category_id
AND role_type.role_type_id =
role_type_category.role_type_id
AND docket.case_type_id IN (3, 6, 9, 12,
15, 18, 21, 24, 27, 1, 4, 7, 10,
13, 16, 19, 22, 25, 2, 5, 8, 11, 14,
17, 20, 23, 26)
AND docket.court_entity_id = 98
AND docket_status_type.description <> :1
AND role_category.category_name = :2
AND upper(name.first_name) LIKE 'T%'
AND upper(name.last_name) LIKE 'JONES%'
and name.entity_id=role.entity_id
and entity.entity_id=name.entity_id
ORDER BY upper(name.last_name) ASC,
upper(name.first_name) ASC
The cost of the query is now between 60 and
125 depending upon whether it uses the first or last name uppercase
function based
indexes, it always uses the role index and
comes back in 3-5 seconds!! I guess it makes sense that Oracle is
better able to
determine how much of the name table it needs
to scan etc, but just breaks our normal rules for binding everything we
can. Any ideas
why this seems to be this way with LIKE's???
Bob
Received on Mon Jun 03 2002 - 12:53:42 CDT
