index usage in conjunction with delete 
Author Message
 index usage in conjunction with delete

Can anyone shed some light on whether indexes are utilized when
performing a delete?

We are running Sybase SQL Server version 11.0.2 on a Dec Alpha running
Digital Unix 3.2G.  One of our tables has approximately 25 million
rows.  This table has several columns, including field1, field2, field3
and date.  We have created a unique, nonclustered index on those
columns.

I happened upon a situation as follows:

(1) Upon executing a statement like the following

select count(*) from MyTable where field1="abc" and field2="def" and
field3="ghi" and date between "1/1/93"  and "1/1/98"

I get an instantaneous result of zero, which is correct since none of
these records exist.  However...

(2) Upon executing the next statement with identical criteria

delete from MyTable where field1="abc" and field2="def" and field3="ghi"
and date between "1/1/93"  and "1/1/98"

I just seem to hang.  I have not been patient enough to see if it will
ever return, but have killed the command.

The question is why doesn't the delete return immediately (with no rows
affected) since there are no records that meet that criteria and I can
get the zero count immediately?  Is there any way for me to force the
delete to use an index?

Thanks for any light on this subject.



Tue, 29 Aug 2000 03:00:00 GMT
 index usage in conjunction with delete

Quote:

> Can anyone shed some light on whether indexes are utilized when
> performing a delete?

> We are running Sybase SQL Server version 11.0.2 on a Dec Alpha running
> Digital Unix 3.2G.  One of our tables has approximately 25 million
> rows.  This table has several columns, including field1, field2, field3
> and date.  We have created a unique, nonclustered index on those
> columns.

> I happened upon a situation as follows:

> (1) Upon executing a statement like the following

> select count(*) from MyTable where field1="abc" and field2="def" and
> field3="ghi" and date between "1/1/93"  and "1/1/98"

> I get an instantaneous result of zero, which is correct since none of
> these records exist.  However...

> (2) Upon executing the next statement with identical criteria

> delete from MyTable where field1="abc" and field2="def" and field3="ghi"
> and date between "1/1/93"  and "1/1/98"

> I just seem to hang.  I have not been patient enough to see if it will
> ever return, but have killed the command.

> The question is why doesn't the delete return immediately (with no rows
> affected) since there are no records that meet that criteria and I can
> get the zero count immediately?  Is there any way for me to force the
> delete to use an index?

> Thanks for any light on this subject.

There is no way to use forceindex syntax on a delete.

The thing to do is 1) confirm that the delete is not simply being
blocked by another process, then 2) check the output of "set showplan
on" and traceflag 302 for clues.

It is probably more than you want, but check out "Analyzing and
Resolving Optimizer Problems/Symptoms" at http://techinfo.sybase.com
[docid 2602] which goes into great detail about how to analyze the
output.

--
Bret Halford                    Imagine my disappointment
Sybase Technical Support        in learning the true nature            
3665 Discovery Drive            of rec.humor.oracle...
Boulder, CO 80303



Tue, 29 Aug 2000 03:00:00 GMT
 index usage in conjunction with delete

Quote:

> Can anyone shed some light on whether indexes are utilized when
> performing a delete?

> We are running Sybase SQL Server version 11.0.2 on a Dec Alpha running
> Digital Unix 3.2G.  One of our tables has approximately 25 million
> rows.  This table has several columns, including field1, field2, field3
> and date.  We have created a unique, nonclustered index on those
> columns.

> delete from MyTable where field1="abc" and field2="def" and field3="ghi"
> and date between "1/1/93"  and "1/1/98"

> I just seem to hang.  I have not been patient enough to see if it will
> ever return, but have killed the command.

> The question is why doesn't the delete return immediately (with no rows
> affected) since there are no records that meet that criteria and I can
> get the zero count immediately?  Is there any way for me to force the
> delete to use an index?

> Thanks for any light on this subject.

 Try:

set transaction isolation level 0
go
delete MyTable
from MyTable (index MyTable_clustered_unique_index) where field1="abc" and
field2="def" and field3="ghi"
and date between "1/1/93"  and "1/1/98"
go



Wed, 30 Aug 2000 03:00:00 GMT
 index usage in conjunction with delete


writes:

...

Quote:
>> The question is why doesn't the delete return immediately (with no rows
>> affected) since there are no records that meet that criteria and I can
>> get the zero count immediately?  Is there any way for me to force the
>> delete to use an index?

>> Thanks for any light on this subject.

>There is no way to use forceindex syntax on a delete.

Actually, I was able to accomplish this a while ago (11.0.x i think)
by creating a simple view of the table that uses the forceindex
syntax.  Then, issue your deletes against the view.  It works for
forcing updates also.  We needed to do this for a data loading
application.

For example:

create view delete_tasks
as
select task_id, task_name ...
from tasks (index task_pk_index)

delete from delete_tasks
where task_id = 12345

Use "showplan" to verify that it works.  I'm pretty sure we
did this on a flavor of 11.0.x.

-----------------------------------------------------
Kevin Sherlock
USWEST Dex
Omaha, NE

-----------------------------------------------------



Fri, 01 Sep 2000 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Using Inserted and Deleted tables in Conjunction in a Trigger

2. Index Space Usage more than Data Space Usage

3. Optimizer depth - Index usage dependant on name of index

4. remove index from field or delete a indexed field

5. Index dees not match the table, delete the index file and re-create

6. High CPU usage with cascading deletes

7. Minimizing lock usage with $DELETE

8. Connections Piling up using WebLogic JDBC Driver in conjunction with SQL Server 7.0

9. ROWCOUNT in conjunction with sum

10. VB Database in conjunction with outline control

11. getObject possible bug in conjunction with jdbc 2.0 features

12. MS ACCESS in conjunction with the web?>


 
Powered by phpBB® Forum Software