Добавил:
Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:
Programming PL SQL.doc
Скачиваний:
3
Добавлен:
01.07.2025
Размер:
5 Мб
Скачать
☆

21.3.4 Object View with Inverse Relationship

To demonstrate virtual denormalization, I can create a keyword type for a view that links keywords back to the images they describe:

CREATE TYPE image_refs_t AS TABLE OF REF image_t;

CREATE TYPE keyword_t AS OBJECT (

keyword VARCHAR2(45),

image_refs image_refs_t);

And here is a keywords view definition:

CREATE OR REPLACE VIEW keywords_v

OF keyword_t

WITH OBJECT IDENTIFIER (keyword)

AS

SELECT keyword, CAST(MULTISET(SELECT MAKE_REF(images_v, image_id)

FROM keywords

WHERE keyword = main.keyword)

AS image_refs_t)

FROM (SELECT DISTINCT keyword FROM keywords) main;

Now, I don't promise that queries on this view will run fast—the query is compensating for the fact that the database lacks a reference table of keywords by doing a SELECT DISTINCT operation. Even if I weren't using any object features, that would be an expensive query.

You may correctly point out that using MAKE_REF is not mandatory here; I could have retrieved a REF by making the inner query on images_v rather than on the keywords table. In general, MAKE_REF should be faster than a lookup through an object view; on occasion, you may not have the luxury of being able to perform that lookup.

Anyway, at this point I can run such pithy queries as this one:

SQL> SELECT DEREF(VALUE(i)).print( )

2 FROM keywords_v v, TABLE(v.image_refs) i

3 WHERE keyword = 'SIXTIES';

DEREF(VALUE(I)).PRINT( )

------------------------------------------------------------------------------------

Id=100001; File=/files/web/60s/smiley_face.gif; keywords=HAPPY FACE, SIXTIES

Id=100002; File=/files/web/60s/peace_symbol.gif; keywords=JERRY RUBIN, PEACE SYMBOL,

SIXTIES

That is, I can show a list of all the images tagged with the keyword SIXTIES, along with their other keywords and attributes. I admit that I'm not sure how groovy that really is!

21.3.5 Instead of Triggers

Because Chapter 18 covered the syntax and use of INSTEAD OF triggers, I'm not going to discuss their mechanics here. Instead, I will explore whether they are a good fit for the problem of updating object views. If your goal is to migrate toward an object approach, you may ask whether INSTEAD OF triggers are just a relational throwback that facilitates a free-for-all in which any application can perform DML.

Well, they are and they aren't.

Let's examine the arguments for both sides, and come up with some considerations so you can decide what's best for your application.

21.3.5.1 The case against

On the one hand, you could use PL/SQL programs such as packages and object methods to provide a more comprehensive technique than triggers for encapsulating DML. It is nearly trivial to take the logic from our INSTEAD OF trigger and put it into an alternate PL/SQL construct that has more universal application. In other words, if you've already standardized on some combination of packages and methods as the means of performing DML, you could keep your environment consistent without using view triggers. You might conclude that view triggers are just added complexity in an increasingly confusing equation.

Moreover, even Oracle cautions against the "excessive use" of triggers, as they can cause "complex interdependencies." Imagine if your INSTEAD OF triggers performed DML on tables that had other triggers, which performed DML on still other tables with triggers . . . it's easy to see how this could get impossible to debug.

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]