View previous topic :: View next topic
|
Author |
Message |
sreekusr Warnings : 1 New User
Joined: 28 Aug 2006 Posts: 54 Location: Madrid
|
|
|
|
For our project,we have a cursor defenition as shown below. For opening this cursor the CPU time required is quite high. Can someone suggest and alternative approach to reduce the time taken for the query to execute. We would prefer to get rid of the NOT EXISTS in the query
Code: |
SELECT TEST_ROL_ID
FROM VIEW1 A,
VIEW2 B
WHERE TEST_STRT_TS < :WS-TODAY-TS AND
TEST_EXPIRY_TS > :WS-TODAY-TS-1 AND
TEST_EXPIRY_TS <= :WS-TODAY-TS AND
TEST_ALWD_ROL_S_TS < :WS-TODAY-TS AND
TEST_ALWD_ROL_E_TS > :WS-TODAY-TS AND
TEST_ROL_ID > :WS-CP-ROL-ID AND
A.TEST_SEC_SEG_ID = B.TEST_SEC_SEG_ID AND
B.TEST_USR_TYP_ID = 1 AND
TEST_VFN_ST = 'A' AND
NOT EXISTS
(SELECT * FROM VIEW3 C
WHERE C.TEST_ROL_ID = A.TEST_ROL_ID AND
C.TEST_SEC_SEG_ID =
A.TEST_SEC_SEG_ID AND
C.TEST_VFN_ST = A.TEST_VFN_ST AND
C.TEST_STRT_TS <
CURRENT TIMESTAMP AND
C.TEST_EXPIRY_TS >
CURRENT TIMESTAMP AND
C.TEST_ALWD_ROL_S_TS <
CURRENT TIMESTAMP AND
C.TEST_ALWD_ROL_E_TS >
CURRENT TIMESTAMP)
ORDER BY TEST_ROL_ID ASC; |
Thanks,
Sree |
|
Back to top |
|
|
Itanium
Active User
Joined: 22 Jan 2006 Posts: 114 Location: India
|
|
|
|
Best suggestion would be to ask the DBA of your shop. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
just remove the NOT EXISTS from your CURSOR DECLARATION
and after every FETCH,
do a singleton select against VIEW3
(with all the where clauses from the subselect) -
you would have to recode the singleton to use host variables instead of VIEW1 column references.
Also, the WS-TODAY-TS and WS-TODAY-TS-1 -
if they are host variable timestamps, how did you generate them?
If you generated them thru COLUMNAR functions in an earlier SQL,
use the COLUMNAR functions to replace the host variables.
remove as many host variables as you can from your cursor declaration.
and index as much as possible.
then EXPLAIN your SQL.
then talk with your DBA's. |
|
Back to top |
|
|
|