|
View previous topic :: View next topic
|
| Author |
Message |
r_rakesh_r
New User
Joined: 10 Aug 2006
Posts: 5
|
|
|
|
Hi all,
I have a delete statement in one of my programs,
DELETE * from table 1
where user = :host variable
I have defined an index on the field "user". But when the program runs, it uses a tablespace scan and as a result it is taking a lot of time.
The explain report also says that this query is using table space scan. I am not able to identify the issue.
Please help.
Thanks
Rakesh |
|
| Back to top |
|
 |
MFRASHEED
Active User
Joined: 14 Jun 2005
Posts: 186
Location: USA
|
|
|
|
| How many rows qualify for this delete? if large number of rows qualify then that explains. |
|
| Back to top |
|
|
r_rakesh_r
New User
Joined: 10 Aug 2006
Posts: 5
|
|
|
|
| Even if the number of records qualifying is Zero, it is taking a lot of time. |
|
| Back to top |
|
|
ravi17s
Warnings : 1
New User
Joined: 15 Aug 2003
Posts: 57
|
|
|
|
When was the Last Runstats done on these Table Space.
If the statistics are not upto date,it affects the Access Path
Thx,
Ravi |
|
| Back to top |
|
|
r_rakesh_r
New User
Joined: 10 Aug 2006
Posts: 5
|
|
|
|
I have run REORG, RUNSTATS, REBIND.
RUNSTATS say that the query is using Index but still using Table space scan. |
|
| Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
Hello r_rakesh_r,
We experienced a problem, somewhat similar to what you are describing, where the Query should be using an Index Scan but was using in fact a tablespace scan. After some time pulling our hair we turned it over to the DBAs and some Brilliant DBA has us put in ?WHERE KEY = KEY?. ??????? It worked. Please don?t ask me why??
Try
| Code: |
DELETE * from table 1
WHERE USER = USER
and user = :host variable
|
Dave |
|
| Back to top |
|
|
kvivek
New User
.jpg)
Joined: 09 May 2005
Posts: 51
Location: Singapore
|
|
|
|
| Quote: |
| Even if the number of records qualifying is Zero, it is taking a lot of time. |
We had the similar issue.
1) Index field in the where class is spaces across the entire table (Like user field in your query)
2) There are no matching records for the query.
For the above reasons each query went into the entire table scan even though index has been defined in the particular field.
Once the index field is populated with valid values in the table and after REORG, RUNSTATS, REBIND index has been picked.
Regards,
Vivek |
|
| Back to top |
|
|
|
|
