SQL Anywhere database - ASA 9.0.2 Using index hints in DELETE FROM statement.

Author

ASA 9.0.2 Using index hints in DELETE FROM statement.

Andrei Iliev

2005-06-24, 7:23 am

ASA 9.0.2 documentation states:
"ASA SQL Reference / SQL Statements / FROM clause /Description
Use this clause to specify the database tables or views involved in a
SELECT, UPDATE, or DELETE statement.
The WITH ( INDEX ( index-name ) ) or the equivalent FORCE INDEX (
index-name ) clauses specify index hints for the table."

Actually it doesn't works for DELETE statement, ISQL reports the following
error: Syntax error near 'with' on line 1. Is it a bug in documentation or
in DB engine? If it is a bug in documentation, please add WITH ( INDEX (
index-name ) ) clause to DELETE FROM statement.

Recently I faced with a performance issue. After applying recent EBF, some
of delete statement run almost forever. After some digging, I found that sql
statement DELETE FROM ObjInMsg WHERE id_msg=someval, uses full-table scan
instead of FK-index as before EBF.

Table definition:
CREATE TABLE ObjInMsg
(
"id_msg" integer NOT NULL ,
"url" long varchar NOT NULL ,
"tag" long varchar NOT NULL ,
"id_src" "uniqueidentifierstr" NULL ,
PRIMARY KEY ("id_msg", "url"),
)
ALTER TABLE ObjInMsg ADD FOREIGN KEY "SrcFileInfo" ("id_src") REFERENCES
"company"."SrcFileInfo" ("id_src") on delete set null;

ALTER TABLE ObjInMsg ADD FOREIGN KEY "Msg" ("id_msg") REFERENCES
"company"."Msg" ("id_msg") on delete cascade;

Plan for sql statement:
DELETE FROM ObjInMsg WHERE id_msg=someval,
shows full table scan, despite the fact that id_msg is a FK!

Plan for sql statement SELECT * FROM ObjInMsg WHERE id_msg=somevel
also showes full table scan.

Since
DELETE FROM ObjInMsg with(index(Msg)) WHERE id_msg=someval, doesn't work
(got errors as I mentioned above),
I tried another way to foul up optimizer - explicit selectivity estimates:
SET OPTION USER_ESTIMATES=ENABL
ED;
DELETE FROM ObjInMsg WHERE (id_msg=someval,1)
- no progress, still got table scan.

I found a workaround:
1) first run query select * FROM ObjInMsg with(index(Msg)) WHERE
id_msg=someval
2) After that all
(SELECT | DELETE) * FROM ObjInMsg WHERE id_msg uses FK index.

Ivan T. Bowman

2005-06-24, 9:23 am

If you want to use index hints in a DELETE you must use two "FROM" clauses:

delete from employee
from employee e with( index(employee) )
where e.emp_id > 12

It sounds like the optimizer is estimating that it is cheaper to do a
sequential scan than to use the index. You could look at the optimizers
estimate of the number of matching rows vs. actual. If the estimates match,
then it may be that there is clustering on this index, and the optimizer
would then benefit from having the index declared as clustered. If the
estimated number of rows is very different, that indicates a problem with
selectivity estimatation.

The user estimate of 1 (1%) is actually pretty high for using an index if
there is no clustering. With no clustering and a table with about 100 rows
per page, this could lead to searching the whole table using the index. With
your user estimate, the optimizer probably still considers that the index
scan is more expensive than a sequential scan. When you "prime the cache"
with another query first, you are loading data pages into the cache, which
the optimizer takes into account when choosing an access path. The optimizer
is more willing to choose an index when part of the table is in cache.

The graphical plan can be used to show you how many table pages the
optimizer estimates are in the cache.

--
Ivan Bowman
ASA Query Processing
iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

"Andrei Iliev" <xzxz@mail.ru> wrote in message
news:42bbf9db$1@foru
ms-2-dub...
>
> ASA 9.0.2 documentation states:
> "ASA SQL Reference / SQL Statements / FROM clause /Description
> Use this clause to specify the database tables or views involved in a
> SELECT, UPDATE, or DELETE statement.
> The WITH ( INDEX ( index-name ) ) or the equivalent FORCE INDEX (
> index-name ) clauses specify index hints for the table."
>
> Actually it doesn't works for DELETE statement, ISQL reports the following
> error: Syntax error near 'with' on line 1. Is it a bug in documentation or
> in DB engine? If it is a bug in documentation, please add WITH ( INDEX (
> index-name ) ) clause to DELETE FROM statement.
>
> Recently I faced with a performance issue. After applying recent EBF,
some
> of delete statement run almost forever. After some digging, I found that
sql
> statement DELETE FROM ObjInMsg WHERE id_msg=someval, uses full-table scan
> instead of FK-index as before EBF.
>
> Table definition:
> CREATE TABLE ObjInMsg
> (
> "id_msg" integer NOT NULL ,
> "url" long varchar NOT NULL ,
> "tag" long varchar NOT NULL ,
> "id_src" "uniqueidentifierstr" NULL ,
> PRIMARY KEY ("id_msg", "url"),
> )
> ALTER TABLE ObjInMsg ADD FOREIGN KEY "SrcFileInfo" ("id_src") REFERENCES
> "company"."SrcFileInfo" ("id_src") on delete set null;
>
> ALTER TABLE ObjInMsg ADD FOREIGN KEY "Msg" ("id_msg") REFERENCES
> "company"."Msg" ("id_msg") on delete cascade;
>
> Plan for sql statement:
> DELETE FROM ObjInMsg WHERE id_msg=someval,
> shows full table scan, despite the fact that id_msg is a FK!
>
> Plan for sql statement SELECT * FROM ObjInMsg WHERE id_msg=somevel
> also showes full table scan.
>
> Since
> DELETE FROM ObjInMsg with(index(Msg)) WHERE id_msg=someval, doesn't work
> (got errors as I mentioned above),
> I tried another way to foul up optimizer - explicit selectivity
estimates:
> SET OPTION USER_ESTIMATES=ENABL
ED;
> DELETE FROM ObjInMsg WHERE (id_msg=someval,1)
> - no progress, still got table scan.
>
> I found a workaround:
> 1) first run query select * FROM ObjInMsg with(index(Msg)) WHERE
> id_msg=someval
> 2) After that all
> (SELECT | DELETE) * FROM ObjInMsg WHERE id_msg uses FK index.
>
>
>
>

Sponsored Links