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=prereserva3_.MRM_ID
  AND prereserva3_.RMR_ID=prereserva1_.RMR_ID
  AND prereserva1_.RMR_ID=prereserva2_.MMR_ID
  AND prereserva2_.CUST_TYP='AGT'
  AND prereserva1_.ORIG='ZRH'
  AND prereserva1_.OWNR_CARR_CODE='LX'
  AND (prereserva1_.MSG_TEXT IS NOT NULL) --MSG_TEXT is contain CLOB
MIHIR KAR#Hadoop #Oracle_DB #UNIX beginnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Please post the execution plan.

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.
Mark GeerlingsDatabase AdministratorCommented:
Your query checks records in four tables not just one.  Your "from" clause and your "where" clauses do not agree with other on how Oracle's query optimizer should try to process this query efficiently.  And, we don't know:
1. how many rows each of these four tables contain.
2. which columns are indexed in these tables.
3. how selective (or not) the values are in the columns that your "where" clauses provide values for,

The order of the tables that you list in the "from" clause is important.  Oracle assumes that the table you list first is the one it should start with, then it should proceed to the second table in the "from" clause, then to the third table, etc.

The "where" clauses should provide values that also help Oracle process these same tables in the same order, with the "known" value in each "where" clause line to the right of the "=" sign, like this:
AND prereserva2_.CUST_TYP='AGT'

So, that line by itself is fine.  But, the order of these lines (top-to-bottom) and the order of the values (right-to-left) for the lines that are used for the joins is significant.  Oracle processes these lines from the bottom up.  So it starts with the last line in your "where" clause.   The last line in your query: "AND (prereserva1_.MSG_TEXT IS NOT NULL)"  doesn't help Oracle at all in terms of efficiency, since Oracle cannot use an index for an "IS NOT NULL" condition.

The next three lines up in your "where" clause that provide "hard-coded" or literal values may (or may not) help performance significantly.  But we don't know if these columns are indexed, or not.  And, we don't know if these values are common (that is, many records have these values) and these records are scattered among lots of other records that have other values, or if these values are rare, or may happen to be on records that are adjacent to each other in the tables, or not.

Depending on whether these columns are indexed or not, and depending on both the numbers of records that have these values and on where these records are physically, in relation to other records in these table that have other values, it may (or may not) help to change the order of these lines (top-to-bottom).  And, it may (or may not) help to list the SBH_PRE_RES_CUST prereserva2_ table first in your "from" clause instead of third, depending on what I just said about the values in these three "where" clause lines that provide hard-codes values.

Then, the first three lines in your "where" clause should be evaluated and possibly re-ordered top-to-bottom and/or right-to-left depending on what I said about the other three lines in your "where" clause.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
Here's an example of how the "from" and "where" clauses should be adjusted *IF* the value 'AGT' is rare in the  prereserva2_.CUST_TYP column, *AND* if this column is indexed.  This assumes that the values ('ZRH' and 'LX') in the "prereserva1_.ORIG" and "prereserva1_.OWNR_CARR_CODE" are not rare, or are not indexed.

FROM SBH_PRE_RES_CUST prereserva2_,
      SBH_PRE_RES prereserva1_,
      SBH_PRE_RES prereserva3_,
      SBH_PRE_MSK reservatio0_
 WHERE reservatio0_.MSK_ID=prereserva3_.MRM_ID
   AND prereserva3_.RMR_ID=prereserva1_.RMR_ID
   AND (prereserva1_.MSG_TEXT IS NOT NULL) --MSG_TEXT is contain CLOB
   AND prereserva1_.ORIG='ZRH'
   AND prereserva1_.OWNR_CARR_CODE='LX'
   AND prereserva1_.RMR_ID=prereserva2_.MMR_ID
   AND prereserva2_.CUST_TYP='AGT'
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
>>The order of the tables that you list in the "from" clause is important.

With the Rule Based Optimizer that was more or less true but I'm not sure it is still true with the Cost Based Optimizer and Dynamic Sampling.  I cannot find anything in the docs that suggests table order makes a difference in execution.

>> since Oracle cannot use an index for an "IS NOT NULL" condition.

That is the reason for my suggestion of a virtual column on the table.  You can use it to index a not null value.
Mark GeerlingsDatabase AdministratorCommented:
The order of the tables may still affect how much work Oracle's query optimizer has to do before it determines which access path to actually use to process the query.  If we help the optimizer out by listing the tables and the join and filter conditions in the optimal order, that may save some time whenever this statement gets parsed.  After that, as long as the statement remains cached in RAM, the actual execution time is likely not affected.
johnsoneSenior Oracle DBACommented:
This is going to start to go way off topic .....

Neither the rules based or cost based optimizer relied on the ordering of tables.  They both used the ordering of tables to hint as to driving tables.

The rules based optimizer looks at them in order.  The cost based optimizer looks at them in reverse order.  Which is a function of how they build the parse tree, or at least that is what someone who worked at Oracle told me.

It really doesn't matter with the cost based optimizer, it tends to look at so many different permutations of the plan to determine the correct one, that it is highly unlikely that order ever comes into play.  This used to be controlled by a parameter called OPTIMIZER_MAX_PERMUTATIONS, which is still around, it just exists as a hidden parameter.  The original default value for when this came out was 80,000.  In 9i, it was reduced to 2,000.  I checked a current version of 12c and the hidden parameter is set to 2,000.  So, the optimizer is really checking up to 2,000 different execution plans to come up with the "best" one.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.