View previous topic :: View next topic
|
Author |
Message |
TS70363
New User
Joined: 07 Apr 2010 Posts: 94 Location: Bangalore, India
|
|
|
|
Hi...
I have the below SQL as a Cursor declaration in my application (COBOL)
Could any one please help refine it?
Code: |
SELECT COLUMN1, COLUMN2 from TABLE1
Where
(COLUMN3 = :input1 value AND "N" = :WS-flag)
Or
(COLUMN4 = :input2 value AND "Y" = :WS-flag) |
COLUMN3 and COLUMN4 are 2 different indexes on TABLE1.
When I checked the EXPLAIN, the access type is M, MX, MI.
COBOL logic behind WS-flag derivation
Code: |
IF INPUT3 = 'valid value'
MOVE 'Y' TO WS-flag
ELSE
MOVE 'N' TO WS-flag |
Thanks... |
|
Back to top |
|
|
rajesh1183
New User
Joined: 07 Jan 2008 Posts: 98 Location: Hyderabad
|
|
|
|
is it possible to split the query into two.. such that the optimizer would pick the required index based on your query which improves the performance..
Code: |
Cursor1:
SELECT COLUMN1, COLUMN2 from TABLE1
Where
(COLUMN3 = :input1 value )
|
Code: |
Cursor2:
SELECT COLUMN1, COLUMN2 from TABLE1
Where
(COLUMN4 = :input2 value )
|
Code: |
IF INPUT3 = 'valid value'
OPEN, FETCH -- CURSOR2
ELSE
OPEN, FETCH -- CURSOR1
END-IF
|
|
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Quote: |
is it possible to split the query into two.. |
why not?
Try
instead of
Also try something like this too, it works and we implemented it.
Code: |
('N' = :WS-flag OR :WS-flag = ' ') |
|
|
Back to top |
|
|
TS70363
New User
Joined: 07 Apr 2010 Posts: 94 Location: Bangalore, India
|
|
|
|
Hi...Rajesh..
Thanks.
Yes. I can split the queries into 2 which I know is the best possible way and was also thought of before posting the query.
The only reason I am not planning to split is because I have 3 such cursors in the production version of application program. And implementing a split would result in declaration, open, fetch, close of 3 more cursors. Can still be implemented if I do not find any other way.
Hi Rohit,
Can you please elaborate? |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Quote: |
And implementing a split would result in declaration, open, fetch, close of 3 more cursors. Can still be implemented if I do not find any other way. |
So what ? At a moment you are only going to work with one cursor and not 2 or 3 together. |
|
Back to top |
|
|
|