1 / 14

Understanding Object-Relational Databases: Types, Object Views, and Advanced Concepts

This guide explores key aspects of object-relational databases, focusing on advanced concepts such as types, object views, nested tables, varying arrays, and large objects. It delves into the creation and manipulation of complex data types, including abstract data types and methods that enhance behaviors. Detailed examples illustrate the creation of custom types (e.g., ADDRESS_TY and PERSON_TY), indexing, and using object views for efficient data retrieval. The treatment of LOBs, including BLOBs and CLOBs, provides insights into their storage and initialization. Explore best practices for using these advanced database features effectively.

quincy
Download Presentation

Understanding Object-Relational Databases: Types, Object Views, and Advanced Concepts

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Part IV: Object-Relational Databases • Ch. 30: Types, Object Views, Methods • Ch. 31: Nested Tables and Varying Arrays • Ch. 32: Large Objects • Ch. 33: Advanced OO Concepts 1

  2. Ch. 30: Types • Abstract datatypes groups related columns/data .create type ADDRESS_TY as object( Street VARCHAR2(50), City VARCHAR2(25), State CHAR(2), Zip NUMBER); • Abstract datatypes can be nested . • security – must have EXECUTE privilege to use a type. • set describe depth – see attributes within an object. 2

  3. Ch. 30: Types: indexing, selecting, manipulating Create type PERSON_TY ( Name VARCHAR2(25), Address ADDRESS_TY);Create table CUSTOMER ( Cust_Id NUMBER, Person PERSON_TY ); • Select:select c.Name, c.Person.Address.City from CUSTOMER c where c.Person.Address.City like ‘I%’; • Indexing:create index IDX_CUST on CUSTOMER(Person.Address.City); • Insert:insert into CUSTOMER values (1, PERSON_TY(‘xxxx’, ADDRESS_TY(‘aaaa’, ‘bbb’, ccc’))); 3

  4. Ch. 30: Object Views. create table CUSTOMER(Cust_id NUMBER PRIMARY KEY, Name VARCHAR2(25), Street VARCHAR2(50), City VARCHAR2(25), State CHAR(2), Zip NUMBER); create type ADDRESS_TY as object( Street VARCHAR2(50), City VARCHAR2(25), State CHAR(2), Zip NUMBER);create type PERSON_TY ( Name VARCHAR2(25), Address ADDRESS_TY);create or replace view CUSTOMER_OV(Cust_id, person) asselect Cust_id, PERSON_TY (Name, ADDRESS_TY(street, city, state, zip))from CUSTOMER; • Inserting into original table: • insert into CUSTOMER values ( 123, ‘name’, ‘Street’, ‘City’, ‘ST’, 99999 ); • Inserting into object view:insert into CUSTOMER_OV values ( 123, PERSON_TY(‘name’, ADDRESS_TY( ‘Street’, ‘City’, ‘ST’, 99999 ))); 4

  5. Ch. 30: INSTEAD OF Triggers • Views usually cannot use standard table-based PL/SQL Triggers, but they can use INSTEAD OF triggers. Works with either normal views or object views. create trigger AUTHOR_PUBLISHER_UPDATEinstead of UPDATE on AUTHOR_PUBLISHERfor each rowbegin …end; 5

  6. Ch. 30: Methods • Methods allow types to have “behavior”. • Methods are declared inside CREATE TYPE, they are defined with CREATE TYPE BODY. create type PERSON_TY ( Name VARCHAR2(25), Address ADDRESS_TY, Birthday DATE, member function AGE (BirthDate IN DATE) return NUMBER);create or replace type body PERSON_TY as member function AGE (Birthdate DATE) return NUMBER is begin return ROUND(sysdate – Birthdate); end; end; 6

  7. Ch. 31: Collector: Varying Arrays • Definition – a varying array is a set of objects, each of same type, seen as a column within a table. The size is limited when created. • Creating a Varying Array • Create type TOOL_TY as object (…); • Create type TOOLS_VA as varray(5) of VARCHAR2(25); • Create table TOOL_BORROWER (…, Tools TOOLS_VA); • Describing – requires queries on data dictionary to show varray structure. • Selecting – requires PL/SQL, see p. 571-572 • Inserting – insert into TOOL_BORROWER values (‘name’, TOOLS_VA(‘tool1’, ‘tool2’, ‘tool3’)); • Storage – stored in-line, ie. within table data segment. 7

  8. Ch. 31: Collector: Nested Tables • Definition – a nested table is a table within a table. Their size is not limited as with varying arrays. • Creating a Nested Table • Create type TOOL_TY as object (…); • Create type TOOLS_NT as table of TOOL_TY; • Create table TOOL_BORROWER (…, nested table Tools TOOLS_NT); • Describing – set describe depth will allow the describe command to show nested tables. • Selecting – requires the TABLE() function (previous to 9i use THE() function).This “flatten’s” or de-normalizes the nested table’s relationship.Select BreederName, N.Name, N.birthdate from BREEDER, TABLE(breeder.Animals) N • Inserting – insert into TABLE(select Animals from BREEDER where Breedername = ‘x’) values (ANIMAL_TY (‘x’,’y’,’z’)); • Storage – Nested tables can be stored in or out-of-line. Recommend they be stored out-of-line with a storage clause to improves query performance. 8

  9. Ch. 32: Large Object Datatypes • BLOB – binary field up to 4Gb. • CLOB – character field up to 4Gb. • NCLOB – CLOB that supports MBCS (multi-byte character set). • BFILE – read-only binary data stored in a file outside of the database. 9

  10. Ch. 32: LOB Storage, Initialization • LOB Storage – BLOB and (N)CLOB columns are stored out-of-line. They require the CREATE TABLE statement include a LOB STORAGE clause to tell where the LOB segment is stored. BFILE columns are by their nature stored out-of-line. • LOBs use at least 1 entire data block per row in the LOB segment. Normal DATA segments store multiple rows per block (up to 100% -PCTFREE). • Initializing LOBs – use EMPTY_BLOB or EMPTY_CLOB instead of NULL to initialize a LOB. • NULL will create a LOB with no data. • EMPTY_BLOB/CLOB will create an empty “LOB locater” (pointer) without allocating the empty block. 10

  11. Ch. 32: DBMS_LOB • LOB data is manipulated with the DBMS_LOB package. • For all LOBs: • APPEND • COMPARE • COPY • ERASE • GETCHUNKSIZE • GETLENGTH • INSTR • ISOPEN • OPEN • READ • SUBSTR • TRIM • WRITE • WRITEAPPEND • BFILE specific functions: • FILEOPEN • FILECLOSE • FILECLOSEALL • FILEEXISTS • FILEGETNAME • FILEISOPEN • LOADFROM FILE • Temporary LOB functions: • CREATETEMPORARY • FREETEMPORARY • ISTEMPORARY 11

  12. Ch. 32: BFILE • Read-only binary data stored in a file outside of the database. • CREATE DIRECTORY PROPOSAL_DIR AS ‘C:\My Documents’; • Insert into thetable ( col1, bfilecol ) values ( ‘value1’, BFILENAME(‘proposal_dir’, ‘myfile.doc’); • Deleting LOBs – internal LOBs when deleted have both their data and LOB locater deleted. Deleting a BFILE only deletes the locater, the file is read only and thus not changed. 12

  13. Ch. 33: row objects vs. column objects • Row objects • Column objects 13

  14. Ch. 33: REF, DEREF, VALUE • REF • DEREF • VALUE 14

More Related