Notes for 1Z0-047 Part IV

By | January 23, 2011

Continuation of my notes to review before taking the 1Z0-047 certification.

Rollup / Cube / Grouping Sets

grouping(column_name) = 1 indicates a superaggregate row

Data Dictionary

  • Adding Comments: Comments can be added to tables, columns, indextype, etc.:

    comment on table ships is 'Listing of all ships';
    comment on column ships.capacity is 'Maximum number of passengers';

  • Comments can’t be dropped. To achieve same result, just set to blank:
    comment on table ships is '';
  • USER_CATALOG – Dictionary view that contains all tables, views, synonyms and sequences for current user
    • Only has two columns: table_name and table_type
  • USER_CONTRAINTS – Possible values for constraint_type:
    • P – Primary Key
    • R – Referential Integrity (Foreign Key)
    • U – Unique
    • C – Check and NOT NULL
  • DICTIONARY – Dictionary view that contains all views that make up data dictionary

    Manipulating Large Datasets

    CTAS – Can work if all columns have a valid name / alias in subquery or by specifying column list in create table statement:

    create table active_ships
    (ship_name, total_capacity)
    select ship_name, first_deck_capacity + second_deck_capacity
    from ships
    where status = 'Active';

    Multitable Insert

    • Unconditional – ALL keyword is required:
      INTO table1 VALUES(column_list)
      INTO table2 VALUES(column_list)
      SELECT ...

    • Conditional – Example:

      WHEN expression THEN
      INTO table1 VALUES(column_list)
      WHEN expression THEN
      INTO table2 VALUES(column_list)
      INTO table3 VALUES(column_list)
      INTO table4 VALUES(column_list)
      SELECT ...

      • ALL – Default option; Executes all into clauses for all expressions that match
      • FIRST – Executes into clauses only for first expression that matches
      • VALUES Clause – Optional if number of columns and their datatypes match table in INTO exactly
      • ELSE Clause – Optional; Must be last if included


    • Example:

      MERGE INTO table_name
      USING [table_name / view_name / subquery]
      ON condition
      SET col = expression
      WHERE condition
      DELETE WHERE condition
      WHERE condition
      WHERE condition;

    • DELETE Clause will only delete rows that are processed by UPDATE statement and DELETE condition looks at values as they exist after the udpate


    Flashback Query:

    SELECT *
    FROM table
    AS OF [TIMESTAMP / SCN] expression;

    Flashback Version Query

    SELECT *
    FROM table
    VERSIONS BETWEEN [TIMESTAMP / SCN] expression AND expression;

    • expression can be keywords MINVALUE or MAXVALUE for the earliest / latest values available
    • Output shows 1 row for each version of each row in table that existed between times specified
    • Can’t be used when querying a view
    • Pseudocolumns providing identifying information for each version:
      • VERSIONS_STARTTIME / VERSIONS_ENDTIME – Start / End time when Version was created (null if created before / after lower boundary)
      • VERSIONS_STARTSCN / VERSIONS_ENDSCN – Start / End SCN when Version was created (null if created before / after lower boundary)
      • VERSIONS_XID – ID identifying transaction that created version
      • VERSIONS_OPERATION – I/U/D to identify operation (Insert/Update/Delete) that created version

    Flashback Query and Flashback Version Query can be used together:

    select s.*, versions_startscn, versions_endscn, versions_operation
    from ships s
    versions between SCN MINVALUE and MAXVALUE
    as of timestamp systimestamp - 1
    order by ship_name;

    • VERSIONS clause must come first and is constrained by AS OF clause
    • MAXVALUE will correspond to time specified by as of clause

    Flashback Transaction Query

    • Query on data dictionary table FLASHBACK_TRANSACTION_QUERY
    • TABLE contains XID, which is global transaction identifier
      • Corresponds to value in pseudocolumn VERSIONS_XID
      • Datatype is RAW; Can use RAWTOHEX to read
    • Contains column UNDO_SQL which will contain SQL statements necessary to undo statement ran that created transaction
      • For example, a delete that deletes 10 rows will result in FLASHBACK_TRANSACTION_QUERY table having 10 rows all with same XID that each have an insert statement in UNDO_SQL column
  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    Turn on pictures to see the captcha *