Board index » delphi » PLAN

PLAN

I'm trying to learn how the query optimizer works, sending a SELECT
statement to the server and inspecting the PLAN that the optimizer
generates. There is one think I don't understand:

When I use a WHERE clause I got this plan:

  PLAN (CLIENTES INDEX (RDB$PRIMARY9))

When I use an ORDER BY clause I got this plan:

  PLAN (CLIENTES ORDER I_CLIENTES_RAZONSOCIAL)

When I use both a WHERE clause and an ORDER BY clause I got this plan:

  PLAN (CLIENTES ORDER I_CLIENTES_RAZONSOCIAL)

Does this mean that an index is not used in the WHERE clause?

TIA,

Joan

 

Re:PLAN


There is a topic in IB help about how optimizer works.
Sequential scan is generally less expensive than sorting result set (depends
on what percent of table is the result set)

--
----------------------
Regards
Robert Cerny
Remove both qwe when replying
email: robert.qwe.ce...@neosys.xrs.qwe.si

No questions via email, unless explicitly invited.

Quote
Joan Ortanobas wrote in message <8a2j8a$44...@bornews.borland.com>...
>I'm trying to learn how the query optimizer works, sending a SELECT
>statement to the server and inspecting the PLAN that the optimizer
>generates. There is one think I don't understand:

>When I use a WHERE clause I got this plan:

>  PLAN (CLIENTES INDEX (RDB$PRIMARY9))

>When I use an ORDER BY clause I got this plan:

>  PLAN (CLIENTES ORDER I_CLIENTES_RAZONSOCIAL)

>When I use both a WHERE clause and an ORDER BY clause I got this plan:

>  PLAN (CLIENTES ORDER I_CLIENTES_RAZONSOCIAL)

>Does this mean that an index is not used in the WHERE clause?

>TIA,

>Joan

Re:PLAN


Thanks for your answer Robert,

Quote
> Sequential scan is generally less expensive than sorting result set
(depends
> on what percent of table is the result set)

You mean that when both WHERE and ORDER BY clauses are used in a statement
the server only uses an available index for the ORDER BY, performing a table
scan for the WHERE clause, being this less expensite than using one for the
WHERE clause and performing the ordering without using an index?

Forgive my persistence but it is an important point for me to understand...

Many thanks,

Joan

Re:PLAN


Quote
Joan Ortanobas wrote in message <8a50fu$6...@bornews.borland.com>...
>Thanks for your answer Robert,

>> Sequential scan is generally less expensive than sorting result set
>(depends
>> on what percent of table is the result set)

>You mean that when both WHERE and ORDER BY clauses are used in a statement
>the server only uses an available index for the ORDER BY, performing a
table
>scan for the WHERE clause, being this less expensite than using one for the
>WHERE clause and performing the ordering without using an index?

>Forgive my persistence but it is an important point for me to understand...

>Many thanks,

>Joan

Yes, if it estimates over 15% of hits, server uses this method.
If if uses index for WHERE, it has to filter the whole resultset and then
sort this
resulting table, but if it uses index for ORDER BY, it can start returning
records while filtering them which results in faster responce and no final
sorting is performed.

Naturally, if result set is small (1% of table), if will more probably use
index for WHERE, and sort result.

Exact plan heavily depends on server, available indexes, complexity of joins
and accuracy of internal statistics.
Some bulk operations (insert/delete million records) may corrupt statistics,
so it's good idea to drop and recreate indexes.

--
----------------------
Regards
Robert Cerny
Remove both qwe when replying
email: robert.qwe.ce...@neosys.xrs.qwe.si

No questions via email, unless explicitly invited.

Re:PLAN


Thanks you very much Robert!

Joan

Other Threads