Optimization_CLOB File
Hi Expert,
i'm using below query for fetching data from table which contains colume CLOB datatype and it took around 29 min for fetching 3500 rows . please suggest how can i optimize the query so i can get data in lesser time .
SELECT prereserva1_.RMR_ID AS col_0_0_,
prereserva1_.DOC_TYP AS col_1_0_,
prereserva1_.DOC_PRFX AS col_2_0_,
prereserva1_.DOC_NUM AS col_3_0_,
prereserva1_.ORIG AS col_4_0_,
prereserva1_.DEST AS col_5_0_,
prereserva1_.PCS AS col_6_0_,
prereserva1_.GROSS_WT_BU AS col_7_0_,
prereserva1_.GROSS_VOL_BU AS col_8_0_,
prereserva1_.PRD_CODE AS col_9_0_,
prereserva1_.SVC_CODE AS col_10_0_,
prereserva1_.MSG_TEXT AS col_11_0_, -- CLOB data
prereserva2_.CUST_CODE AS col_12_0_
FROM SBH_PRE_MSK reservatio0_,
SBH_PRE_RES prereserva1_,
SBH_PRE_RES_CUST prereserva2_,
SBH_PRE_RES prereserva3_
WHERE reservatio0_.MSK_ID=preres
AND prereserva3_.RMR_ID=preres
AND prereserva1_.RMR_ID=preres
AND prereserva2_.CUST_TYP='AGT
AND prereserva1_.ORIG='ZRH'
AND prereserva1_.OWNR_CARR_COD
AND (prereserva1_.MSG_TEXT IS NOT NULL) --MSG_TEXT is contain CLOB
ORDER BY reservatio0_.CREATED_DATE DESC;
i'm using below query for fetching data from table which contains colume CLOB datatype and it took around 29 min for fetching 3500 rows . please suggest how can i optimize the query so i can get data in lesser time .
SELECT prereserva1_.RMR_ID AS col_0_0_,
prereserva1_.DOC_TYP AS col_1_0_,
prereserva1_.DOC_PRFX AS col_2_0_,
prereserva1_.DOC_NUM AS col_3_0_,
prereserva1_.ORIG AS col_4_0_,
prereserva1_.DEST AS col_5_0_,
prereserva1_.PCS AS col_6_0_,
prereserva1_.GROSS_WT_BU AS col_7_0_,
prereserva1_.GROSS_VOL_BU AS col_8_0_,
prereserva1_.PRD_CODE AS col_9_0_,
prereserva1_.SVC_CODE AS col_10_0_,
prereserva1_.MSG_TEXT AS col_11_0_, -- CLOB data
prereserva2_.CUST_CODE AS col_12_0_
FROM SBH_PRE_MSK reservatio0_,
SBH_PRE_RES prereserva1_,
SBH_PRE_RES_CUST prereserva2_,
SBH_PRE_RES prereserva3_
WHERE reservatio0_.MSK_ID=preres
AND prereserva3_.RMR_ID=preres
AND prereserva1_.RMR_ID=preres
AND prereserva2_.CUST_TYP='AGT
AND prereserva1_.ORIG='ZRH'
AND prereserva1_.OWNR_CARR_COD
AND (prereserva1_.MSG_TEXT IS NOT NULL) --MSG_TEXT is contain CLOB
ORDER BY reservatio0_.CREATED_DATE DESC;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you remove MSG_TEXT from the select portion and the where clause, how long does the query take?
What is the percentage of rows in the table where MSG_TEXT is not null? Maybe you can create a virtual column that has a 1 or 0 if MSG_TEXT is null or not. Then you can create an index on the virtual column.