Oracle Nested T ables Another structuring to ol pro vided - PDF document

Oracle Nested T ables Another structuring to ol pro vided in Oracle is the abilit y to ha v e a relation with an attribute whose v alue is not just an ob ject, but a (m ulti)set of ob jects, i.e., a relation. Keyw ord allo ws us to treat a nested THE � relation as a regular relation, e.g., in FROM clauses. Keyw ords let us turn CAST(MULTISET(...)) � the result of a query in to a nested relation. De�ning T able T yp es If w e ha v e an ob ject t yp e, w e can create a new t yp e that is a bag of that t yp e b y OF . AS TABLE 1

Example Supp ose w e ha v e a more complicated b eer t yp e: CREATE TYPE BeerType AS OBJECT ( name CHAR(20), kind CHAR(10), color CHAR(10) ); / W e ma y create a t yp e that is a (nested) table of ob jects of this t yp e b y: CREATE TYPE BeerTableType AS TABLE OF BeerType; / 2

No w, w e can de�ne a relation of man ufacturers that will nest their b eers inside. In a sense, w e normalize an unnormalized � relation, since other data ab out the man ufacturer app ears only once no matter ho w man y b eers they pro duce. CREATE TABLE Manfs ( name CHAR(30), addr CHAR(50), beers BeerTableType ) Ho w ev er, to tell the system ho w to store � the littl e tables, w e m ust follo w this beers statemen t, prior to the semicolon, b y a statemen t NESTED TABLE beers STORE AS BeerTable; The name of the table that stores the tuples � for the nested relations is arbitrary; beers here w e used BeerTable . 3

Querying With Nested T ables An attribute that is a nested table can b e prin ted lik e an y other attribute. The v alue has t w o t yp e constructors, one for � the table, one for the t yp e of its tuples. Example List the b eers made b y Anheuser-Busc h. SELECT beers FROM Manfs WHERE name = 'Anheuser-Busch' ; A single v alue will b e prin ted, lo oking � something lik e: BeerTableType( BeerType('Bud', 'lager', 'yellow'), BeerType('Lite', 'malt', 'pale'),... ) 4

Op erating on Nested T ables Use to get the nested table itself, then treat it THE lik e an y other relation. Example Find the ales made b y Anheuser-Busc h. SELECT bb.name FROM THE( SELECT beers FROM Manfs WHERE name = 'Anheuser-Busch' ) bb WHERE bb.kind = 'ale'; 5

Casting to Create Nested T ables Create a v alue for a nested table b y using a select- from-where query and \casting" it to the table t yp e. Example Supp ose w e ha v e a relation Beers(beer, � manf) , where is a ob ject and beer BeerType its man ufacturer. manf W e w an t to insert in to a tuple for Manfs � P ete's Brewing Co., with all the b eers brew ed b y P ete's (according to Beers ) in one nested table. INSERT INTO Manfs VALUES( 'Pete''s', 'Palo Alto', CAST( MULTISET( SELECT bb.beer FROM Beers bb WHERE bb.manf = 'Pete''s' ) AS BeerType ) ); 6

T ransactions = units of w ork that m ust b e: 1. = app ear to ha v e b een executed when Isolate d no other DB op erations w ere b eing p erformed. ✦ Often called b eha vior. serializable ✦ In mo dern DBMS's, serializa bil i t y is often one of sev eral options for ho w b eha vior is restricted. 2. = either all w ork is done, or none of it. A tomic 7

Commit/Ab ort Decision Eac h transaction ends with either: 1. = the w ork of the transaction is Commit installe d in the database; previously its c hanges ma y b e in visibl e to other transactions. 2. = no c hanges b y the transaction app ear A b ort in the database; it is as if the transaction nev er o ccurred. ✦ is the term used in SQL and ROLLBACK the Oracle system. In the ad-ho c query in terface (e.g., Oracle's � SQLplus), transactions are single queries or mo di�cation statemen ts. ✦ Oracle allo ws SET TRANSACTION to b egin a m ultistatemen t READ ONLY transaction that do esn't c hange an y data, but needs to see a consisten t \snapshot" of the data. In program in terfaces (e.g., Pro*C or � PL/SQL), transactions b egin whenev er the database is accessed, and end when either a or statemen t is executed. COMMIT ROLLBACK 8

Example Sells(bar , beer , price) Jo e's Bar sells Bud for $2.50 and Miller for � $3.00. Sally is querying the database for the highest � and lo w est price Jo e c harges: (1) SELECT MAX(price) FROM Sells WHERE bar = 'Joe''s Bar'; (2) SELECT MIN(price) FROM Sells WHERE bar = 'Joe''s Bar'; A t the same time, Jo e has decided to replace � Miller and Bud b y Heinek en at $3.50: (3) DELETE FROM Sells WHERE bar = 'Joe''s Bar' AND (beer = 'Miller' OR beer = 'Bud'); (4) INSERT INTO Sells VALUES('Joe''s bar', 'Heineken', 3.50); 9

If the order of statemen ts is 1, 3, 4, 2, then it � app ears to Sally that Jo e's minim um price is greater than his maxim um price. Fix the problem b y grouping Sally's t w o � statemen ts in to one transaction, e.g. with one PL/SQL statemen t. 10

Example: Problem With Rollbac k Supp ose Jo e executes statemen t 4 (insert Heinek en), but then, during the transaction thinks b etter of it and issues a statemen t. ROLLBACK If Sally is allo w ed to execute her statemen t 1 � (�nd max) just b efore the rollbac k, she gets the answ er $3.50, ev en though Jo e do esn't sell an y b eer for $3.50. Fix b y making statemen t 4 a transaction, or � part of a transaction, so its e�ects cannot b e seen b y Sally unless there is a action. COMMIT 11

SQL Isolation Lev els determine what a transaction is Isolation levels allo w ed to see. The declaration, v alid for one transaction, is: X SET TRANSACTION ISOLATION LEVEL ; where: = SERIALIZABLE : this transaction m ust X � execute as if at a p oin t in time, where all other transactions o ccurred either completely b efore or completely after. Example Supp ose Sally's statemen ts 1 and 2 are one transaction and Jo e's statemen ts 3 and 4 are another transaction. If Sally's transaction runs at isolation lev el SERIALIZABLE , she w ould see the relation either b efore or after statemen ts 3 Sells and 4 ran, but not in the middle. 12

= COMMITTED : this transaction can X READ � only read committed data. Example If transactions are as ab o v e, Sally could see the original for statemen t 1 and the completely Sells c hanged for statemen t 2. Sells 13

= READ : if a transaction reads X REPEATABLE � data t wice, then what it sa w the �rst time, it will see the second time (it ma y see more the second time). ✦ Moreo v er, all data read at an y time m ust b e committed; i.e., is REPEATABLE READ a strictly stronger condition than READ COMMITTED . Example If 1 is executed b efore 3, then 2 m ust see the Bud and Miller tuples when it computes the min, ev en if it executes after 3. But 2 ma y see the Heinek en tuple, ev en if 1 didn't. 14

= UNCOMMITTED : essen tially no X READ � constrain t, ev en on reading data written and then remo v ed b y a rollbac k. Example 1 and 2 could see Heinek en, ev en if Jo e rolled bac k his transaction. 15

Indep endence of Isolation Lev els Isolation lev els describ e what a transaction with T that isolation lev el sees. They constrain what other do not � transactions, p erhaps at di�eren t isolation lev els, can see of the w ork done b y . T Example If transaction 3-4 (Jo e) runs serializable , but transaction 1-2 (Sally) do es not, then Sally migh t see as the v alue for b oth min and max, since NULL it could app ear to Sally that her transaction ran b et w een steps 3 and 4. 16

Authorization in SQL File systems iden tify certain access privileges � on �les, e.g., read, write, execute. In partial analogy , SQL iden ti�es nine access � privileges on relations, of whic h the most imp ortan t are: 1. = the righ t to query the relation. SELECT 2. = the righ t to insert tuples in to INSERT the relation | ma y refer to one attribute, in whic h case the privilege is to sp ecify only one column of the inserted tuple. 3. = the righ t to delete tuples from DELETE the relation. 4. = the righ t to up date tuples of UPDATE the relation | ma y refer to one attribute. 17