8 Replies Latest reply: Jun 6, 2014 9:06 AM by Martin Preiss RSS

    why comment is considered as ddl statement

    Satyapkunal

      Hello All,

       

      During an interview I was asked "why comment is considered as a DDL statement though it does not change structure of an object".

      My reply was though it does not directly changes structure of an object [table, column, view etc], however it provides information about it.

      Also comment adds data in data dictionary table.

      But interviewer was not satisfied with my answer.

       

      I tried searching answer on web, but could not find much details.

      Could anybody please suggest something on this.

        • 1. Re: why comment is considered as ddl statement
          EdStevens

          917433 wrote:

           

          Hello All,

           

          During an interview I was asked "why comment is considered as a DDL statement though it does not change structure of an object".

          My reply was though it does not directly changes structure of an object [table, column, view etc], however it provides information about it.

          Also comment adds data in data dictionary table.

          But interviewer was not satisfied with my answer.

           

          I tried searching answer on web, but could not find much details.

          Could anybody please suggest something on this.

          So how are we to know what was in the mind of the interviewer? I'd say your answer was pretty good.

           

          Interviews should be treated as conversations, not multiple choice tests.

           

          My last job search actually included a multiple choice test.  It had questions that were vague, based on fallacies, and based on unsupported assumptions. In other words, I knew more than the guy that wrote the test. Instead of simply picking the least objectionable answer (if there was even that) I made marginal notes explaining and expanding.  I got the job

          • 2. Re: why comment is considered as ddl statement
            SomeoneElse

            > why comment is considered as ddl statement

             

            Because it does a commit.

             

            SQL> select * from t;

             

                               N

            --------------------

                               0

             

            SQL> update t set n = 1;

             

            1 row updated.

             

            SQL> select * from t;

             

                               N

            --------------------

                               1

             

            SQL> comment on table t is 'testing';

             

            Comment created.

             

            SQL> rollback;

             

            Rollback complete.

             

            SQL> select * from t;

             

                               N

            --------------------

                               1

            • 3. Re: why comment is considered as ddl statement
              Brian Bontrager

              The blunt answer is "Because Oracle's documentation classifies it as DDL."

              Types of SQL Statements

               

              I might clarify the question with "I presume you mean the COMMENT ON statement that adds a formal comment in the database? (rather than general comments in a SQL script)" COMMENT

               

              If I wasn't familiar with the specifics and couldn't check the documentation (like I did to answer this) I might answer, "It is not DML (Insert,Update,Delete) or transaction/system control (COMMIT, ROLLBACK, ALTER SESSION), therefore it is DDL."

              About DML Statements and Transactions

               

              The distinction matters because COMMENT ON, as DDL, implicitly COMMITs.

               

              Personally, I might have been satisfied with your answer.  If you didn't mention the implicit COMMIT I would have followed with a question about DDL in general to see if you were aware of that.

              • 4. Re: why comment is considered as ddl statement
                Frank Kulash

                Hi,

                 

                Comments are properties of the table or its columns, the same way that constraints are.  Even though you don't use an ALTER TABLE command to create comments, it does change something about the table, and therefore it makes sense to consider it a DDL command.  If the command was called CREATE COMMENT, then it would be more obvious.

                 

                The fact that it changes data in the data dictionary doesn't matter.  All DDL commands change what is stored in the data dictionary, but that's DML, not DDL.

                 

                A good thing to say on an interview would be "Even if COMMENT wasn't DDL, it might as well be, since you always create comments immediately after creating tables or adding columns.  Anybody who doesn't create comments right away is just asking fro trouble."

                • 5. Re: why comment is considered as ddl statement
                  BluShadow

                  We could also say "because it's not DML" as it's not updating data on the table, therefore it must be DML.

                   

                  As Ed says, it's hard to know what answer the interviewer was looking for, or how relevant that really is to testing your knowledge and skills as a software developer or DBA etc. (whatever the job was for)

                  • 6. Re: why comment is considered as ddl statement
                    Solomon Yakobson

                    SomeoneElse wrote:

                     

                    Because it does a commit.

                     

                     

                    Every Oracle DDL implicitly commits but not everything that implicitly commits is DDL. And it is not commit that defines what DDL statement is about. One of DDL features is DDL enables altering object attributes and this is exacly what COMMENT does. It adds table/column comments and we can debate how important comments are, but it doesn't change the fact comments are object attributes.

                     

                    SY.

                    • 7. Re: why comment is considered as ddl statement
                      SomeoneElse

                      Another possible answer is "because Oracle said so".  It's not *considered* to be DDL, it *is* DDL.

                       

                      Straight from the manual: Types of SQL Statements

                       

                      The DDL statements are:

                       

                      ALTER

                      ANALYZE

                      ASSOCIATE STATISTICS

                      AUDIT

                      COMMENT

                      CREATE

                      DISASSOCIATE STATISTICS

                      DROP

                      FLASHBACK

                      GRANT

                      NOAUDIT

                      PURGE

                      RENAME

                      REVOKE

                      TRUNCATE

                      • 8. Re: why comment is considered as ddl statement
                        Martin Preiss

                        just a small addition: in some other rdbms a ddl operation does not include an implicit commit. In postgres for example you can drop a table (or add a comment) in a transaction and revert the operation by issuing a rollback. Oracle defines a comment as DDL - maybe because it includes a change of internal dictionary tables (sys.com$), that has to be committed? - but I am not even sure if other vendors would agree with this taxonomy.

                         

                        But as others already said: we can only speculate about the interviewer's thoughts.