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 '';
- Only has two columns: table_name and table_type
- P – Primary Key
- R – Referential Integrity (Foreign Key)
- U – Unique
- C – Check and NOT NULL
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)
as
select ship_name, first_deck_capacity + second_deck_capacity
from ships
where status = 'Active';
Multitable Insert
- Unconditional – ALL keyword is required:
INSERT ALL
INTO table1 VALUES(column_list)
INTO table2 VALUES(column_list)
SELECT ...
;
- Conditional – Example:
INSERT [ALL / FIRST]
WHEN expression THEN
INTO table1 VALUES(column_list)
WHEN expression THEN
INTO table2 VALUES(column_list)
INTO table3 VALUES(column_list)
ELSE
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
Merge
- Example:
MERGE INTO table_name
USING [table_name / view_name / subquery]
ON condition
WHEN MATCHED THEN UPDATE
SET col = expression
WHERE condition
DELETE WHERE condition
WHEN NOT MATCHED THEN INSERT(column_list)
VALUES(expression_list)
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
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