1 2 Previous Next 22 Replies Latest reply: Jun 19, 2013 12:33 PM by BluShadow Go to original post RSS
      • 15. Re: Execute a DML query its length exceeds 4000 characters with execute immediate statement.
        BluShadow

        a) Why are you creating a dynamic statement?

        b) PL/SQL varachar2 datatype supports 32767 characters, so you should not exceed any 4000 character limit unless you create a string literal within the SQL statement itself that exceeds 4000 characters.  Show us your new SQL.

        c) Don't use an IN condition with lots of literal values in it, put the values into a table (or global temporary table) and then join to that table to apply the restriction.

        • 16. Re: Execute a DML query its length exceeds 4000 characters with execute immediate statement.
          851511

          i have to create dynamic query only because, i don't know the what conditons to match. Those condition values comes from user at runtime, because of that reason i have to create dynamic query

           

          Following one is my new sql(which is also generated dynamically). But query execution is very slow, i thought that it is because of XMLTable

          SELECT IVT.ID_IVT

                 ,IVT.ID_INS_IVT

              ,XVT.PRODUCTNAME

              ,XVT.INSTANCENAME

              ,XVT.INTERNALNAME

                  ,XVT."Jurisdictions"

                  ,XVT."Channels"

                  ,XVT."ModeofDelivery"

                 ,VW.NAME_CTG

                 ,VW.NAME_CTR

                 ,VW.NAME_MDL

              ,IVT.REEDIT_FLAG_IVT

                FROM  VARIATION_IVT IVT INNER JOIN CTG_CTR_MDL_VIEW VW ON IVT.ID_CTG_IVT=VW.ID_CTG

                  ,XMLTABLE('//DomainVariation' PASSING IVT.DOCUMENT_IVT COLUMNS PRODUCTNAME VARCHAR2(100) PATH './context/productName',INSTANCENAME VARCHAR2(100) PATH './context/elementName',INTERNALNAME VARCHAR2(150) PATH './context/elementInternalName',"Jurisdictions" VARCHAR2(100) PATH './domains/domain[domainName="Jurisdictions"]/domainValue',"Channels" VARCHAR2(100) PATH './domains/domain[domainName="Channels"]/domainValue',"ModeofDelivery" VARCHAR2(100) PATH './domains/domain[domainName="ModeofDelivery"]/domainValue' ) XVT

                   WHERE  IVT.STATUS_IVT='Active' AND IVT.DELETE_FLAG_IVT=0 AND IVT.ID_PRJ_IVT=1

                   AND XVT.PRODUCTNAME IN ('ALL','A009','A010','A046','AccidentShieldClassic','AccidentShieldOnline','AM01','AM02','AME_Cancellation','ARHG','ARPA','B003','B004','B007','B008','B009','B010','B012','B013','B015','B016','B017','BC04_PA','BC06_FDP','BC06_PA','BC09','BC10','BC12','BC13','BF03','BS01','BS02','C017','C035','C036','C037','C038','C040','C041','C041Gold','C041New','C045HomeContents','C048','C049','C054','C057','C060Building','C060Contents','C060FDP','C061Building','C061Contents','C062','C063','C067','C070','C072','C074','C077','C081','C082','C087','C088','CITIFOREVER','CITISECURE','CITICHILDPLAN','D001','DB01','DD01','DD02','DD03','DD04','DD09','DD10','E005','E011','E016','E020','E030','E034','E040','E041','E045HCP','E045HSP','E049','E049New','E052','E053','E054FDP','E055','E056','E057','E058','E061','E061BATCH','E062','E063','E064HCP','E064HSP','E066','E069','E073','E075','E076','E088','E090','E093A','E093B','E095','E099A','E099B','E106','E107','E110','E112','E114','E115','E116','F001','FamilyHealthInsurance','FamilyHospitalBenefits','FamilyHospitalisationCoverBenefit','G001','G002','HealthShieldOnline','Health_B005','Health_S057','HealthSheild','HealthWalkin','HomeContentOnline','HomeShieldOnline','HomeShieldWalkin','HospitalCashOnline','J001','J008','K001','KV02','LC03','ML01','MP02','MP03','MR01','O005','PO01','PO02','PO03','PO04','PO05','PO06','RR02','RR03','RR04','S006','S033','S049','S051','S054','S057','S060','S061','S065','S065TM','S068','S076','S077','S079','S080','S081','S084','S085','S086','S087','S088','S091','S092','S093','S094','S095','S097','S098','S099','S100','S101','S102','S103','S104','S106','S107','S108','S109','S110','S111','S113','SCBNAC','SF02','SS01','SS02','SUNFHM','SurgicalShield','TD01','TD02','TP01','U002Building','U002Contents','U004Building','U007','U009','U013','U014','U015','U016','V001','V002','V005','V006','V008','W008','W020','W021','W022','W023','W024','W026','W027','W028','W029','W105','W106','WI01','WI02','WI03','WI07','WI08','WI09','WI10','WI11','WI12','WI13','WI14','WI17','WI20','WI21','WI21_Health','WI23','WI24','WI26','WI30','WI31','WI33','WI34','X001','X002','X003','X004','X005','X008','Y001','Y007','Y009','Y010','Y011','Y011H','Y020','Y020N','Z008','ZI001')

              AND XVT."Jurisdictions" IN ('Delhi','Bangladesh','Mumbai','India','Pakistan','Nepal','Maldives','Kolkata','Bhutan','Chennai','ALL')

                  AND "Channels" IN ('Agents','SBI','Maruti','Direct','CitiFinancial','SCB','BankAssurance','CitiBank','Employees','GE','Brokers','Telemarketing','Agency','ALL')

                  AND "ModeofDelivery" IN ('Walkin','Internet','ALL')

           

          Thanks & Regards,

          K.Kedarnadh

          • 17. Re: Execute a DML query its length exceeds 4000 characters with execute immediate statement.
            BluShadow

            99.99% of the time people use dynamic SQL when it's not needed, and your requirement certainly doesn't fall into the 0.01% where dynamic SQL is the only way possible.

            Just because the user is supplying the search criteria doesn't mean that the SQL has to suddenly be done dynamically.

             

            In such a scenario (as I've already mentioned a couple of times)... I would expect the search criteria to be stored by the application into a Global Temporary Table (or other table if necessary) and then the query itself just joins to those tables to apply the restriction, using a static SQL e.g.

             

            SELECT IVT.ID_IVT

                  ,IVT.ID_INS_IVT

                  ,XVT.PRODUCTNAME

                  ,XVT.INSTANCENAME

                  ,XVT.INTERNALNAME

                  ,XVT."Jurisdictions"

                  ,XVT."Channels"

                  ,XVT."ModeofDelivery"

                  ,VW.NAME_CTG

                  ,VW.NAME_CTR

                  ,VW.NAME_MDL

                  ,IVT.REEDIT_FLAG_IVT

            FROM   VARIATION_IVT IVT

                   INNER JOIN CTG_CTR_MDL_VIEW VW ON IVT.ID_CTG_IVT=VW.ID_CTG

                   CROSS JOIN

                   XMLTABLE('//DomainVariation'

                            PASSING IVT.DOCUMENT_IVT

                            COLUMNS PRODUCTNAME      VARCHAR2(100) PATH './context/productName'

                                   ,INSTANCENAME     VARCHAR2(100) PATH './context/elementName'

                                   ,INTERNALNAME     VARCHAR2(150) PATH './context/elementInternalName'

                                   ,"Jurisdictions"  VARCHAR2(100) PATH './domains/domain[domainName="Jurisdictions"]/domainValue'

                                   ,"Channels"       VARCHAR2(100) PATH './domains/domain[domainName="Channels"]/domainValue'

                                   ,"ModeofDelivery" VARCHAR2(100) PATH './domains/domain[domainName="ModeofDelivery"]/domainValue'

                           ) XVT

            WHERE  IVT.STATUS_IVT='Active'

            AND    IVT.DELETE_FLAG_IVT=0

            AND    IVT.ID_PRJ_IVT=1

            AND    XVT.PRODUCTNAME IN (select products from gtt_productnames)

            AND    XVT."Jurisdictions" IN (select places from gtt_jurisdictions)

            AND    "Channels" IN (select ch from gtt_channels)

            AND    "ModeofDelivery" IN (select mode from gtt_modeofdelivery)

             

            As for it appearing "slow", that isn't necessarily because of the XMLTable.  If you've got to extract the data from XML, then you have no choice but to process it, whether you use XMLTable or the deprecated functionality.  Getting performance out of XML based queries can depend on the size of the XML documents you're processing and their complexity, as well as the database version you are using.  If you're dealing with large XML documents then it can be beneficial to register an XML Schema describing the XML before it is inserted to the database, and then the database will automatically create the necessary internal indexes and be able to access the data faster.

             

            I would suggest, when you've sorted out your ridiculous dynamic SQL and made it static, you go over the XML DB forum and start familiarizing yourself with the ways of optimizing XML queries.  A good example is given by Mark Drake over on this thread:

             

            Re: XML file processing into oracle

            • 18. Re: Execute a DML query its length exceeds 4000 characters with execute immediate statement.
              851511

              Hi,

               

              Thanks for the information.

               

              Related to dynamic query, whatever i am comparing domains names such as ."Jurisdictions","Channel" etc are not static one. Those will change as per the business model. In that case whatever you are talking about global temporary table to store such data will be difficult.

               

              The size of xml documents are below 1KB only. I will apply the Xml Schema and indexes  on those xml documents. But in my user schema (DB) i have 50,000 records aos of now. It will increase in future. When i execute that query directly as sql on those 50000 records (without appling xml schema and xml indexes) its almost took more than 25 minutes. so i think even after i apply the xml schema and indexes also it may not reduce to 5 minutes.

               

              Please suggest me if there are any other things i can do to speed up the execution.

               

              Thanks & Regards,

              K.Kedarnadh

              • 20. Re: Execute a DML query its length exceeds 4000 characters with execute immediate statement.
                BluShadow

                851511 wrote:

                Related to dynamic query, whatever i am comparing domains names such as ."Jurisdictions","Channel" etc are not static one. Those will change as per the business model. In that case whatever you are talking about global temporary table to store such data will be difficult.

                 

                Either

                a) you have a fixed design, so you know what options can be searched on - in which case you can use a static SQL query (even if there is no search criteria on a particular domain name)

                or

                b) you have different designs for different business models and you are trying to write something 'generic' to cater for that - in which case this is bad design and you should have seperate static SQL's to handle each business model/database design.

                 

                The size of xml documents are below 1KB only. I will apply the Xml Schema and indexes  on those xml documents. But in my user schema (DB) i have 50,000 records aos of now. It will increase in future. When i execute that query directly as sql on those 50000 records (without appling xml schema and xml indexes) its almost took more than 25 minutes. so i think even after i apply the xml schema and indexes also it may not reduce to 5 minutes.

                 

                Correct.  The benefits of having XML Schema's registered for the XML is for large XML documents, so you won't really gain much benefit (if any) on small 1K XML documents.

                • 21. Re: Execute a DML query its length exceeds 4000 characters with execute immediate statement.
                  851511


                  Are there any options to improve the speed of execution of a query after applying xml schema and xml indexes?

                  • 22. Re: Execute a DML query its length exceeds 4000 characters with execute immediate statement.
                    BluShadow

                    Yes, find out what's actually causing it to go slow first.

                     

                    You cannot fix something if you don't know which part is broken.

                    1 2 Previous Next