Re: Bind variable has funny performance with LIKE

Cost in execution plan is calculated from the choosen operations. The cost of the second query is lower than the cost of the first query. That can only mean that the execution plan of the second query is different from the execution plan of the first query. I believe the optimizer will not use the functionbased index because he does not know the value of the bind-variable. The value could be '%blabla%'.

Cardinality=(selectivity) x (number of rows in table) Selectivity is a value between 0 and 1.
A selectivity of 0.1 means that 10% of the rows will be selected. Selectivity is calculated from the predicates in your query. Why is cardinality in second query lower than in first query, if the number of rows in the tables remain the same?
There can only be one reason. Selectivity is lower. So look again at you execution plans and compare cardinality of the different operations.

One final note.
For bind-variables, the CostBasedOptimizer uses default selectivity estimates (which is 1/(number of distinct values). This is because the CostBasedOptimizer assumes an even distribution. If this isn't true, calculate the statistics for those columns.

"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 Mon Jun 03 2002 - 15:19:20 CDT