8 Replies Latest reply: May 9, 2012 1:09 PM by wzhang-Oracle RSS

    DMU - difference between table conversion method-?

    user13303891
      What is the difference between these modes of conversion - ?
      documentaion - Update only convertible rows vs Scan and update only rows changing in conversion
      dmu - Update only convertible rows vs scan and update only convertible rows
        • 1. Re: DMU - difference between table conversion method-?
          Srini Chavali-Oracle
          Pl provide a doc link to these two types.

          http://docs.oracle.com/cd/E26101_01/doc/doc.11/e26097/toc.htm

          HTH
          Srini
          • 2. Re: DMU - difference between table conversion method-?
            user13303891
            There is no clear explanation in the documetation!
            in which case i should use 1 or 2 method?
            • 3. Re: DMU - difference between table conversion method-?
              Srini Chavali-Oracle
              Pl post a link to the two methods you mention

              Srini
              • 4. Re: DMU - difference between table conversion method-?
                user13303891
                http://docs.oracle.com/cd/E26101_01/doc/doc.11/e26097/ch3tasks.htm#autoId13

                Table Properties: Converting
                ...
                • 5. Re: DMU - difference between table conversion method-?
                  Srini Chavali-Oracle
                  >
                  ...
                  Update only convertible rows

                  An UPDATE statement will update only those rows of the table whose rowids have been collected during the last scan of the table.

                  Scan and update only rows changing in conversion

                  An UPDATE statement will update only those rows of the table that contain a convertible column value as determined by an internal scanning function included in the WHERE clause of the statement.
                  ...
                  >

                  I am not sure why the explanation is not clear - what do you not understand ?

                  DMU will select one of the 5 available options that it thinks is the best - I would suggest you go with what it chooses.

                  HTH
                  Srini
                  • 6. Re: DMU - difference between table conversion method-?
                    user13303891
                    Here is my example.
                    My copy of production database is about 1 tb size.I scanned my db for the first time, ~ 6 hours for scanning. - succesfull scan
                    No cleansing data was found and i decided to do test convert.I checked out all table's properties and found out that large enough tables has property : ◦Copy data using CREATE TABLE AS SELECT - it's clear enough.
                    Some tables has property : ◦Update all rows - it's clear too, all rows of the tables would be update without where clause
                    Some tables has property : ◦Update only convertible rows.
                    There were no tables that have property :◦Scan and update only rows changing in conversion - i wondered why so?
                    Ok, i start coverting my db, but instead of coversion the dmu start scanning my db again - why? I noticed, that in this scan there were only tables that has a properties "Update only convertible rows" . The second scan was about 5 hours and only after this scan the dmu start converting db. 11 hours for scanning db before converting - it is inadmissible for prod db.

                    So my question is, why dmu run second scan for db, even it has succesful one?
                    If i defined all table's properties to "Scan and update only rows changing in conversion" the only one scan is needed and the convertion starts immediatly.
                    • 7. Re: DMU - difference between table conversion method-?
                      user13303891
                      Any suggestions? Srini

                      Edited by: user13303891 on 03.05.2012 3:26
                      • 8. Re: DMU - difference between table conversion method-?
                        wzhang-Oracle
                        Please read bullet point 3 of http://docs.oracle.com/cd/E26101_01/doc/doc.11/e26097/ch4scenarios.htm#BACBGACC

                        and the paragraph starting with "After you accept all warnings" at http://docs.oracle.com/cd/E26101_01/doc/doc.11/e26097/ch4scenarios.htm#BACCEDBH

                        Note that the DMU scan can be performed in the production environment before the downtime window as it analyzes the conversion feasibility of the database without updating your data. The collection of convertible rowids is required for tables assigned with the "Update only convertible rows" method. It is possible to pre-collect rowids before the conversion phase and use the "Maximum Age" parameter to skip the additional scan in the conversion phase if you're sure there are no incremental changes for those tables since the last rowid collection.

                        If the "Scan and update only rows changing in conversion" method is chosen, the DMU will insert an internal scanning function in the WHERE clause of the UPDATE statement to determine the rowids to be converted during the actual data conversion. This method allows you to convert the table based on its latest contents but will also introduce more overhead due to the processing of the scanning function at conversion time.