|
View previous topic :: View next topic
|
| Author |
Message |
prakashbk
New User
Joined: 16 Apr 2009
Posts: 4
Location: chennai
|
|
|
|
i have a query Q in my cobol program which acess table X thru index I.
X has 5 primary keys(partition no,id, timestamp,p4,p5) and index I has all these 5 colums.
currently only 2 primary keys(partion number and id) are used in where conditon of the query Q.
Partition number is assumed using the timestamp i.e for every day a new partition number will be available, so these partition numbers is got from another table for the timestamp interval between say'2009-04-01-00.00.00.00000' to '2009-04-01-23.59.00.00000'.So timestamp filter is indirectly applied to the Q with partition no.
when i use only (partition no and id ) in where condition , Q consumes lot of CPU time(verified thru visual explain). But when i add timestamp interval ('2009-04-01-00.00.00.00000' to '2009-04-01-23.59.00.00000' )in Q,CPU time reduces drastically(visual explain tool).
Shortly will adding all the primary key in where condition of an query which acess thru primary key index improve CPU performace eventhough some of the where conditions are useless. |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Quote: |
| eventhough some of the where conditions are useless. |
why will they be useless? |
|
| Back to top |
|
|
muthuvel
Active User
Joined: 29 Nov 2005
Posts: 217
Location: Canada
|
|
|
|
| Quote: |
| will adding all the primary key in where condition of an query which acess thru primary key index improve CPU performace |
Yes.It will reduce time.
| Quote: |
eventhough some of the where conditions are useless. |
How do u say they are useless?will adding this in Where clause cause discrimination of any unwanted record?
What was the outcome cost using all the keys in EXPLAIN tool? |
|
| Back to top |
|
|
prakashbk
New User
Joined: 16 Apr 2009
Posts: 4
Location: chennai
|
|
|
|
| muthuvel wrote: |
| Quote: |
| will adding all the primary key in where condition of an query which acess thru primary key index improve CPU performace |
Yes.It will reduce time.
| Quote: |
eventhough some of the where conditions are useless. |
How do u say they are useless?will adding this in Where clause cause discrimination of any unwanted record?
What was the outcome cost using all the keys in EXPLAIN tool? |
the rows returned for the first 2 where conditions will 100 % satisfy the next three where condition i am going to add.
Improvemt was around 90% by adding all the keys in where condition compared to having only 2 keys in where |
|
| Back to top |
|
|
prakashbk
New User
Joined: 16 Apr 2009
Posts: 4
Location: chennai
|
|
|
|
| dbzTHEdinosauer wrote: |
| Quote: |
| eventhough some of the where conditions are useless. |
why will they be useless? |
the rows returned for the first 2 where conditions will 100% satisfy the next three where condition i am going to add. |
|
| Back to top |
|
|
prakashbk
New User
Joined: 16 Apr 2009
Posts: 4
Location: chennai
|
|
|
|
the rows returned for the first 2 where conditions will 100 % satisfy the next three where condition i am going to add.
Improvemt was around 90% by adding all the keys in where condition compared to having only 2 keys in where |
|
| Back to top |
|
|
|
|
