try
AND upper(name.first_name) LIKE upper(:3)
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Bob Maggio" <rmaggio_at_NOSPAMcourts.state.ny.usNOSPAM> wrote in message
news:3CFBAD89.9DA87F8B_at_NOSPAMcourts.state.ny.usNOSPAM...
> 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 Tue Jun 04 2002 - 03:48:35 CDT
