-
CPU Usage 100%
Dear Friend,
I m generating a report in which there is a nested select query with union clause using 4 tables. when the report starts the CPU usage goes to 100% which remains for some time. Because this affects other work I have to then Kill the job. What shall I do to keep the CPU usage minimum. I m sendin the query.
SELECT DISTINCT "GRN_DTL"."ITM_ITM_ID"
FROM "GRN_DTL"
WHERE "GRN_DTL"."BU_ID" = :a_store_id
AND "GRN_DTL"."ITM_ITM_ID" NOT IN
(SELECT "ITM_TRXN_DTL"."ITM_ITM_ID"
FROM "ITM_TRXN_DTL", "ITM_TRXN_HDR"
WHERE ("ITM_TRXN_HDR"."ITM_TRXN_ID" =
"ITM_TRXN_DTL"."ITMTRXNHDR_ITM_TRXN_ID"
)
AND ("ITM_TRXN_HDR"."FROM_STORE_ID" = :a_store_id)
AND ("ITM_TRXN_HDR"."ITM_TRXN_TYP" = 'ICN')
AND TO_CHAR (
"ITM_TRXN_HDR"."ITM_TRXN_DT",
'RRRR/MM/DD HH24:MI:SS'
) >= TO_CHAR (
:adt_start_date,
'RRRR/MM/DD HH24:MI:SS'
)
AND TO_CHAR (
"ITM_TRXN_HDR"."ITM_TRXN_DT",
'RRRR/MM/DD HH24:MI:SS'
) <= TO_CHAR (
:adt_end_date,
'RRRR/MM/DD HH24:MI:SS'
))
UNION
SELECT DISTINCT "ITM_TRXN_DTL"."ITM_ITM_ID"
FROM "ITM_TRXN_DTL", "ITM_TRXN_HDR"
WHERE ("ITM_TRXN_HDR"."ITM_TRXN_ID" =
"ITM_TRXN_DTL"."ITMTRXNHDR_ITM_TRXN_ID"
)
AND ("ITM_TRXN_DTL"."ITM_ITM_ID" NOT IN
(SELECT "ITM_TRXN_DTL"."ITM_ITM_ID"
FROM "ITM_TRXN_DTL", "ITM_TRXN_HDR"
WHERE ("ITM_TRXN_HDR"."ITM_TRXN_ID" =
"ITM_TRXN_DTL"."ITMTRXNHDR_ITM_TRXN_ID"
)
AND ("ITM_TRXN_HDR"."FROM_STORE_ID" = :a_store_id)
AND ("ITM_TRXN_HDR"."ITM_TRXN_TYP" = 'ICN')
AND TO_CHAR (
"ITM_TRXN_HDR"."ITM_TRXN_DT",
'RRRR/MM/DD HH24:MI:SS'
) >= TO_CHAR (
:adt_start_date,
'RRRR/MM/DD HH24:MI:SS'
)
AND TO_CHAR (
"ITM_TRXN_HDR"."ITM_TRXN_DT",
'RRRR/MM/DD HH24:MI:SS'
) <= TO_CHAR (
:adt_end_date,
'RRRR/MM/DD HH24:MI:SS'
))
)
AND ( ("ITM_TRXN_HDR"."FROM_STORE_ID" = :a_store_id)
OR ("ITM_TRXN_HDR"."TO_STORE_ID" = :a_store_id)
)
-
CPU usage 100%
Dear Friend,
I would like to provide u with more info. I m using Oracle 8 on Win NT
-
-
AND TO_CHAR ("ITM_TRXN_HDR"."ITM_TRXN_DT",'RRRR/MM/DD HH24:MI:SS') <= TO_CHAR(:adt_end_date,'RRRR/MM/DD HH24:MI:SS')
Why are you going this? Why not ...
Code:
AND "ITM_TRXN_HDR"."ITM_TRXN_DT" <= :adt_end_date
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|