Week 10/11 Flashcards
Physical vs Logical objects
Do physical objects grow in size/occupy disk space? How about logical objects? What are some examples of both?
Physical objects occupy diskspace and grow in size. Think tables, indexes, materialized views
Logical objects do not occupy disk space and do not grow in size. Think views, sequences, synonyms, procedures, functions, triggers.
How do indexes optimize the retrieval process?
They speed up select statements
Every primary key and unique key column constraint automatically has an
index
How can users create an index?
CREATE INDEX
What is the drawback of Indexes?
They slow down DML in order to maintain the index (Insert something, Index must be updated)
What are sequence generated values generally used for?
Primary key values in tables
Are sequences linked to tables?
No, but it’s good practice to create a dedicated sequence for a tables column
What sort of pseudo columns do sequences use to return values?
<sequenceName>.currvalue and <sequenceName>.nextval
</sequenceName></sequenceName>
What are synonyms used for?
To shorten complex table/view names. They do not replace the name.
PL/SQL can do programming other than DB programming
False, it’s restricted
PL/SQL can have any number of statements, which reduces network traffic. This means that DB operations can be sent to the server in one large unit, rather than many separate statements
True. This minimizes network traffic.
T/F – With PL/SQL, the program must reside at the front end
False, it can reside on the front end or within the Oracle DB server as a stored subprogram
What sort of PL/SQL subprograms can be stored in the database?
Procedures, Functions, Triggers, Packages
PL/SQL Sub-programs can be
Anonymous Blocks or Named Sub Programs
What do stored sub programs do?
They promote reusability, better performance, programmability
What types of SQL statements are valid in PL/SQL, what types are not?
Valid: DML(insert), TCL (Commit, Rollback), SQL functions
Invalid: DDL(Create), DCL(Grant, Revoke)
For anonymous block PL/SQL syntax, which are optional and which are mandatory?
Declare, Begin, Exception
Declare and Exception and optional, Begin is mandatory
Scalar vs Composite variable types: what’s the simple difference
Scaler = 1 value
Composite = 2 values
Procedures are generally used for ___ or SELECT, functions are used for ____________
DML, calculations
Functions must return…
a single value
What are Data Dictionary Views? (DDVs)
Views that can show you information on procedures, functions, packages
2 Types of triggers in PL/SQL
Schema level, System
Schema level vs System triggers go
Schema - based on a particular table in a schema
System - Database-wide triggers
What are the 3 things we must decide when creating a trigger
The time, event, and type
What do the time, event and type mean in reference to triggers
Time = before or after event
Event = The event that will trigger the function (insert, update)
Type = row level or statement level
What is row level and statement level in reference to trigger type?
Row executes once for each record affected by the event (could be a lot)
Statement executes once regardless of records affected (only once)
Packages have 2 parts:
specification and body
I have a subprogram that is mentioned in the package body but not the specification, what does that mean?
It means the subprogram is PRIVATE
What are the 4 advantages of packages?
Modularity, Encapsulation, Overloading, Performance
