next Bibliography
up Appendix
previous Fundamentals I - Übungen und Lösungen
  Contents   Index

Subsections


Tuning - Übungen und Lösungen


Tools für die Leistungsüberwachung

In der folgenden Übung werden Sie Hilfsmittel und Tools unter Oracle kennen lernen, mit denen man wichtige Statistiken und Auswertungen einer Datenbank durchführen kann.

Grundlage sind die Scripte zur Erstellung der Nordwind-DB.

1. Melden Sie sich als sys and der Datenbank an.

2. Erstellen Sie die Tabelle employees aus dem Skript employees.txt. Wie viel Datensätze hat die Tabelle employees?

start ~/1ZO-033/Northwind-Skript/employees.txt

select count(*) from employees;

  COUNT(*)
----------
         9
3. Führen Sie folgende Anweisung aus, um die Ausgabe des Ausführungsplanes für die jeweiligen Anweisungen durchzusetzen.
set autotrace on explain;
4. Was passiert und was ist die Ursache?
SP2-0613: Format oder Existenz von PLAN_TABLE kann nicht überprüft werden
SP2-0611: Fehler beim Initialisieren von EXPLAIN - Bericht
5. Erstellen Sie nun die Plan-Tabelle, indem Sie das Skript utlxplan.sql (den Speicherort der Datei müssen Sie selber herausfinden) in Ihrem aktuellen Fenster ausführen. Führen Sie anschließend erneut die Anweisung aus.
start /oracle/ora92/rdbms/admin/utlxplan.sql

set autotrace on explain;
6. Was passiert? Wo lag die Datei utlxplan.sql?
/oracleora92/rdbms/admin/utlxplan.sql
7. Führen Sie eine Abfrage aus, welche alle Datensätze und alle Attribute (Spalten) der Tabelle employees zurückgibt. Was sagt Ihnen der Ausführungsplan?
select * from employees;

Ausführungsplan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'EMPLOYEES'
8. Nun wollen wir uns auch jeweils die Zeit anzeigen lassen, die eine Abfrage benötigte. Führen Sie hierfür folgende Anweisung aus:
set timing on;
9. Führen Sie eine Abfrage aus, welche alle Datensätze und alle Attribute (Spalten) der Tabelle employees zurückgibt. Wie lange dauerte die Abfrage?
select * from employees;

Abgelaufen: 00:00:00.01

Ausführungsplan
---------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1  0   TABLE ACCESS (FULL) OF 'EMPLOYEES'
10. Erzeugen Sie eine Tabelle namens empl_cop, welche genau die Spalten und die Datensätze der Employees enthält. Wie lautet die Anweisung hierfür?
create table empl_cop as select * from employees;
11. Verdoppeln Sie die Anzahl der Datensätze in dieser Tabelle empl_cop insgesamt 16 mal. Wie viel Datensätze haben Sie nunmehr?
-- 16 mal:
insert into empl_cop select * from empl_cop;
commit;

select count(*) from empl_cop;
  COUNT(*)
----------
    589824
12. WICHTIG: Alles weitere bezieht sich jeweils immer auf die empl_cop!
Schreiben Sie eine Abfrage, welche alle Employees und daneben die Anzahl der Datensätze der Employees ausgibt. Wie lautet die Abfrage und wie lange dauert diese? Überlegen Sie, warum diese Abfrage so aufwendig ist. Wir werden später eine ähnliche Abfrage so optimieren, dass die Ausführung unter 1/10 Sekunde dauert.

Beispielergebnismenge

LASTNAME               COUNT(*)
-------------------- ----------
Buchanan                  65536
Callahan                  65536
Davolio                   65536
Dodsworth                 65536
Fuller                    65536
King                      65536
Leverling                 65536
Peacock                   65536
Suyama                    65536
select lastname, count(*) from empl_cop group by lastname;

Abgelaufen: 00:00:20.09

Ausführungsplan
-----------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1  0   SORT (GROUP BY)
2  1     TABLE ACCESS (FULL) OF 'EMPL_COP'
13. Löschen Sie die Tabelle employees und auch die Tabelle Empl_cop wieder.
drop table employees;
drop table empl_cop;
commit;
Im folgenden werden Sie das Tool tkprof etwas genauer kennen lernen. Führen Sie hierfür folgende Schritte durch:

1. Erstellen Sie die Tabelle customers aus dem Skript customers.txt. Wie viel Datensätze hat die Tabelle customers?

start ~/1ZO-033/Northwind-Skript/customers.txt
select count(*) from customers;

  COUNT(*)
----------
        91

Abgelaufen: 00:00:00.00

Ausführungsplan
----------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1  0   SORT (AGGREGATE)
2  1     TABLE ACCESS (FULL) OF 'CUSTOMERS'
2. Nun muss das Tracing eingeschaltet werden, um eine Trace-Datei als Grundlage für tkprof zu haben. Führen Sie hierfür folgenden Befehl aus:
alter session set sql_trace = true;
3. Mit welchen Befehl hätten Sie diese Einstellung permanent machen können?
alter system set sql_trace=true scope=spfile;
4. Wo befindet sich der Ordner, in dem die Trace-Datei gespeichert wird?
/oracle/admin/pingudb/udump/pingudb_ora_2404.trc
5. Wie könnten Sie diesen Ort verändern, auf eine andere Platte zum Beispiel?
USER_DUMP_DEST
Andere Platte entsprechend Inhalt von USER_DUMP_DEST mounten. ;-)

6. Ermitteln Sie die Kunden, die aus Berlin kommen

select * from customers where city = 'Berlin';

1 Zeilen ausgewählt.

Abgelaufen: 00:00:00.00

Ausführungsplan
-----------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1  0   TABLE ACCESS (FULL) OF 'CUSTOMERS'
Ermitteln Sie die Kunden, die aus London kommen
select * from customers where city = 'London';

6 Zeilen ausgewählt.

Abgelaufen: 00:00:00.03

Ausführungsplan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'CUSTOMERS'
Ermitteln Sie die Kunden, die aus Paris kommen
2 Zeilen ausgewählt.

Abgelaufen: 00:00:00.01

Ausführungsplan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'CUSTOMERS'
8. Nun sollten Sie das Tracing ausschalten.
alter session set sql_trace = false;
9. Schauen Sie nun nach, ob die Trace-Datei erstellt wurde.

ja

10. Führen Sie tkprof an der Unix-Shell aus, um sich eine lesbare Datei erstellen zu lassen.

tkprof.exe /oracle/admin/pingudb/udump/pingudb_ora_2404.trc /tmp/1.txt
11. Öffnen Sie nun die eben erstellte Textdatei und schauen Sie sich die Ergebnisse an

Zusatzaufgaben 1

Sicher kommt es vor, dass eine Abfrage so lange dauert, dass es sinnvoller wäre, diese Session abzubrechen. Hierfür können Sie folgendes machen: Melden Sie sich als Scott an und führen Sie eine lange Abfrage aus (z. B. Cross Join über order_details)

Machen Sie eine weitere Session auf:

Führen Sie folgende Abfrage aus, um den Benutzer herauszufinden, dessen Session beendet werden soll select username,sid,serial# from v$session;

USERNAME                              SID    SERIAL#
------------------------------ ---------- ----------
                                        1          1
                                        2          1
...
SYS                                     9         34
SCOTT                                  12        242
Beenden Sie die Session folgendermassen (SID, SERIAL#):
alter system kill session '12,242';


Indizes

In der folgende Übung werden Sie Indizes erstellen, anpassen und natürlich auch wieder löschen. Sie werden beobachten, ob der Index benutzt wird oder nicht.

WICHTIG: Legen Sie sich ein Cold-Backup Ihrer Datenbank an, um im Fehlerfall die Datenbank schnell wiederherstellen und weiterarbeiten zu können.

Bitte führen Sie folgende Schritte als Vorbereitung für die Übung durch.
Falls Sie die Plan-Tabelle in einer der vorigen Übungen noch nicht erstellt haben, so tun Sie dies bitte jetzt (utlxplan.sql ggf. ausführen).
Aktivieren Sie die Anzeige der Zeit und des Ausführungsplanes. Geben Sie hierfür folgendes ein:

set autotrace on exp;
set timing on;
Führen Sie folgende kleine Anweisung aus um zu sehen, ob die Einstellungen ordnungsgemäß umgesetzt wurden:
select * from dual;
Als Ergebnis müsste eine Zeitangabe und ein Ausführungsplan erscheinen

Im folgenden wollen wir untersuchen, wie lange die Suche nach einem Datensatz einer Tabelle bei einem Full Table Scan dauert und anschließend diese Prozedur durch einen Index verbessern:

1. Erstellen Sie die Tabelle order_det_prod, welche aus der orderid, productname, productid, quantity und unitprice (aus products und order_details) besteht. Verdoppeln Sie die Anzahl der Datensätze jeweils 12 mal. Wie viel Datensätze haben Sie nun?

create table order_det_prod as
  select o.orderid,p.productname,p.productid,o.quantity,p.unitprice 
    from products p,order_details o
    where p.productid = o.productid
;
-- 12 mal:
insert into order_det_prod select * from order_det_prod;
commit;

select count(*) from order_det_prod;
  COUNT(*)
----------
   8826880
Abgelaufen: 00:05:32.09

Ausführungsplan
-----------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1  0   SORT (AGGREGATE)
2  1     TABLE ACCESS (FULL) OF 'ORDER_DET_PROD'
2. Ermitteln Sie die größte Bestellnummer. Wie heißt die Abfrage und wie lange dauert die Abfrage? Mit welcher Methode wurde dieser Datensatz ermittelt?
select max(orderid) from order_det_prod;

MAX(ORDERID)
------------
       11077

Abgelaufen: 00:01:06.05

Ausführungsplan
------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1  0   SORT (AGGREGATE)
2  1     TABLE ACCESS (FULL) OF 'ORDER_DET_PROD'
3. Erstellen Sie einen Index namens id1 basierend auf der Spalte orderid.
create index id1 on order_det_prod(orderid);

Index wurde angelegt.

Abgelaufen: 00:17:14.03
4. Ermitteln Sie die größte Bestellnummer. Wie lange dauert die Abfrage nunmehr? Mit welcher Methode wurde dieser Datensatz ermittelt?
select max(orderid) from order_det_prod;

MAX(ORDERID)
------------
       11077

Abgelaufen: 00:00:00.00

Ausführungsplan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FULL SCAN (MIN/MAX)) OF 'ID1' (NON-UNIQUE)
5. Wie oft wurde bisher 'Chai' bestellt (bitte keine Funktion wie lower etc. benutzen)? Wie schnell wurde die Abfrage ausgeführt?
select count(*) from order_det_prod where productname='Chai';

  COUNT(*)
----------
    155648

Abgelaufen: 00:01:07.05

Ausführungsplan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'ORDER_DET_PROD'
6. Erstellen Sie einen geeigneten Index namens id2 und führen Sie die Abfrage erneut aus. Wie schnell wurde die Abfrage nunmehr ausgeführt?
create index id2 on order_det_prod(productname);

Index wurde angelegt.
Abgelaufen: 00:25:17.03
...
7. Erstellen Sie eine Tabelle namens emp_cop basierend auf der employees-Tabelle. Die Tabelle sollte identisch zur employees sein (gleiche Spalten, gleiche Zeilen)
create table emp_cop as select * from employees;
8. Erstellen Sie einen Index namens id3 auf Basis von lastname
create index id3 on emp_cop(lastname);
9. Nun werden Sie den Index id3 analysieren, dass heißt, den Prozentsatz von ungenutztem (durch Löschen) zu dem gesamten Platz ermitteln.
ANALYZE INDEX id3 VALIDATE STRUCTURE;
select (del_lf_rows_len/lf_rows_len)*100 from index_stats;

(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100
---------------------------------
                                0
0% gelöschte Rows.

10. Löschen Sie den ältesten Mitarbeiter. Wie viel Datensätze wurden gelöscht?

delete from emp_cop where birthdate =
  (
    select min(birthdate) from emp_cop
  )
;
1 Zeile wurde gelöscht.
11. Nun werden Sie den Index analysieren, dass heißt, den Prozentsatz von ungenutztem (durch Löschen) zu dem gesamten Platz ermitteln. Legen Sie Ihre Schlussfolgerungen dar.
ANALYZE INDEX id3 VALIDATE STRUCTURE;
select (del_lf_rows_len/lf_rows_len)*100 from index_stats;

(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100
---------------------------------
                       11,0465116
11% gelöschte ROWs

12. Nun werden Sie ein rebuild des Index durchführen. Führen Sie diesen Befehl bitte auch durch.

ALTER INDEX id3 REBUILD;
13. Nun werden Sie den Index analysieren, dass heißt, den Prozentsatz von ungenutztem (durch Löschen) zu dem gesamten Platz ermitteln. Legen Sie Ihre Schlussfolgerungen dar:
ANALYZE INDEX id3 VALIDATE STRUCTURE;
select (del_lf_rows_len/lf_rows_len)*100 from index_stats;

(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100
---------------------------------
                                0
Im folgenden werden Sie einen Bitmap-Index erstellen und testen:

1. Erweitern Sie die Tabelle order_det_prod um eine Spalte namens done (varchar2(1)).

alter table order_det_prod add(done varchar2(1));
2. Für alle Orderids unter 11000 soll Done 'N' sein und für alle darüber soll Done 'Y' sein.
update order_det_prod set done = 'N' where orderid < 11000;
update order_det_prod set done = 'Y' where orderid > 11000;
commit;
Hier geht der Rechner in die Knie!

3. In wie vielen Datensätzen steht bei Done Y drin?

select count(*) from order_det_prod where done='Y';
  COUNT(*)
----------
    880640
4. Erstellen Sie einen Bitmap-Index auf der Spalte Done.
create bitmap index id4 on order_det_prod(done);
5. In wie vielen Datensätzen steht bei Done N drin (Bitte benutzen Sie kein * beim Zählen)? Schreiben Sie hierfür eine Abfrage und messen Sie die Zeit.
select count(done) from order_det_prod where done='N';
Im folgenden werden Sie einen funktionsbasierten Index kennen lernen.

1. Ermitteln Sie alle Datensätze, bei denen der Productname "Chai" ist. Hierbei soll es egal sein, ob das Wort groß oder klein geschrieben wurde.

select count(*) from order_det_prod 
  where lower(productname) = 'chai'
;
2. Erstellen Sie einen normalen Index, der die Suche beschleunigen soll. Was passiert und warum?
create index id5 on order_det_prod(productname);
3. Löschen Sie diesen Index und schreiben Sie einen funktionsbasierten Index. Was passiert und warum?
drop index id5;

create index ... lower(..).. ;
Zusatzaufgabe 1:

Schreiben Sie eine Abfrage, die Ihnen angibt, welche Indizes auf der Tabelle customers liegen und welche Spalten diese umfassen.

select mit Hint

Zusatzaufgabe 2:

Löschen Sie den Bitmap-Index auf Done und erstellen Sie anstelle dessen einen normalen Index. Führen Sie die entsprechenden Messungen erneut durch. Benutzen auch eventuell Hints.

Zusatzaufgabe 3:

Erstellen Sie eine große Tabelle (ca. 3 bis 4 Mio Datensätze) und erstellen Sie einen Index. Überprüfen Sie nun, ob ein Rebuild schneller geht als ein Drop und Recreate des Index. Überprüfen Sie des weiteren, ob während des Rebuilds weitergearbeitet werden kann. Überprüfen Sie außerdem, ob während des normalen Recreate (also Drop und Create) weitergearbeitet werden kann. Legen Sie Ihrer Ergebnisse kurz schriftlich dar.


Optimierer

Im folgenden werden Sie gespeicherte Ausführungspläne testen und einsetzen. Außerdem werden Sie den Optimierer und dessen Arbeitsweise kennen lernen. Führen Sie hierfür folgende Schritte durch:

1. Erstellen Sie die Tabelle order_details neu aus dem Script zur Nordwind-DB.

start ~/1ZO-033/Northwind-Skript/order_details.txt
2. Schalten Sie Autotrace ein.
set autotrace on explain;
3. Fügen Sie zur Tabelle order_details eine Spalte namens paketgroesse varchar(10) hinzu.
alter table order_details add(paketgroesse varchar(10));
4. Geben Sie für 1/3 der Datensätze den Wert 'klein' in die Spalte Paketgroesse.
update order_details set paketgroesse=mod(rownum,3);
update order_details set paketgroesse='klein' where paketgroesse='1';
5. Geben Sie für 1/3 der Datensätze den Wert 'mittel' in die Spalte Paketgroesse.
update order_details set paketgroesse='mittel' where paketgroesse='2';
6. Geben Sie für 1/3 der Datensätze den Wert 'gross' in die Spalte Paketgroesse.
update order_details set paketgroesse='gross' where paketgroesse='0';
commit;
select paketgroesse,count(*) from order_details group by paketgroesse;

PAKETGROES   COUNT(*)
---------- ----------
gross             718
klein             719
mittel            718
7. Ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'klein' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt? Begründen Sie Ihre Antwort.
select * from order_details where paketgroesse='klein';

719 Zeilen ausgewählt.

Ausführungsplan
----------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE
1    0   TABLE ACCESS (FULL) OF 'ORDER_DETAILS'
8. Erstellen Sie einen Index basierend auf der Spalte paketgroesse.
create index id1 on order_details(paketgroesse);
9. Ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'mittel' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt? Begründen Sie Ihre Antwort.
select * from order_details where paketgroesse='mittel';

718 Zeilen ausgewählt.

Ausführungsplan
--------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_DETAILS'
2    1     INDEX (RANGE SCAN) OF 'ID1' (NON-UNIQUE)
Es wird RBO genommen, weil keine Analyse der Tabelle existiert.

10. Zwingen Sie den Optimierer, den cost based optimizer (CBO) bei der nun folgenden Abfrage zu nehmen. Ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'gross' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt? Begründen Sie Ihre Antwort.

select /*+ALL_ROWS*/ * 
  from order_details 
  where paketgroesse='gross'
;
Ausführungsplan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=1 Card=6 Bytes=432)
1  0   TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_DETAILS' (Cost=1 Card=6 Bytes=432)
2  1     INDEX (RANGE SCAN) OF 'ID1' (NON-UNIQUE) (Cost=1 Card=2)
Es erfolgt eine Index-Scan weil CBO, Index vorhanden und es gibt keine Statistik.

11. Fügen Sie einen Datensatz hinzu, bei dem die Paketgröße 'riesig' ist.

insert into order_details values(11111,22,3,4,44,'riesig');
12. Ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'riesig' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt? Begründen Sie Ihre Antwort.
select * from order_details 
  where paketgroesse='riesig'
;
Ausführungsplan
--------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_DETAILS'
2    1     INDEX (RANGE SCAN) OF 'ID1' (NON-UNIQUE)
RBO, da noch keine Statistik vorhanden ist.

13. Zwingen Sie den Optimierer, den CBO bei der nun folgenden Abfrage zu nehmen. Ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'riesig' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt? Begründen Sie Ihre Antwort.

select /*+ALL_ROWS*/ * 
  from order_details 
  where paketgroesse='riesig'
;
Ausführungsplan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=1 Card=6 Bytes=432)
1  0   TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_DETAILS' (Cost=1 Card=6 Bytes=432)
2  1     INDEX (RANGE SCAN) OF 'ID1' (NON-UNIQUE) (Cost=1 Card=2)
Index-Scan, da keine Analyse,

14. Zwingen Sie den Optimierer, den rules based optimizer (RBO) bei der nun folgenden Abfrage zu nehmen. Ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'riesig' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt? Begründen Sie Ihre Antwort.

select /*+RULE*/ * 
  from order_details 
  where paketgroesse='riesig'
;
Ausführungsplan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=HINT: RULE
1  0   TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_DETAILS'
2  1     INDEX (RANGE SCAN) OF 'ID1' (NON-UNIQUE)
RULE: Wenn Index da, dann nehme Index.

15. Erstellen Sie ein Histogramm der Tabelle order_details.

analyze table order_details 
  compute statistics 
  for columns quantity size 4
;
Histogramm wird nur mit 'size ...' erstellt.

16. Ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'riesig' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt? Welcher Optimierungsmodus wird eingesetzt? Begründen Sie Ihre Antwort.

select * from order_details 
  where paketgroesse='riesig'
;
Ausführungsplan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1  0   TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_DETAILS'
2  1     INDEX (RANGE SCAN) OF 'ID1' (NON-UNIQUE)
Index-Scan auf Grund des erstellten Histogramms.

17. Ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'mittel' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt? Welcher Optimierungsmodus wird eingesetzt? Begründen Sie Ihre Antwort.

select * from order_details 
  where paketgroesse='mittel'
;
Ausführungsplan
-------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1  0   TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_DETAILS'
2  1     INDEX (RANGE SCAN) OF 'ID1' (NON-UNIQUE)
Index-Scan wurde verwendet. Full-Table-Scan könnte auch ausgewählt werden, wenn das Verhältnis Anzahl der Datensätze zur Anzahl unterschiedlicher Werte etwas anders wäre.

18. Erstellen Sie die Tabellen products und categories aus dem Skript.

start ~/1ZO-033/Northwind-Skript/products.txt
start ~/1ZO-033/Northwind-Skript/categories.txt
19. Erstellen Sie eine Abfrage, die Ihnen den Produktnamen und den Kategorienamen als Ergebnismenge zurückliefert. Um welchen Join handelte es sich hierbei und warum?
select p.productname,c.categoryname 
  from products p, categories c 
  where p.categoryid=c.categoryid
;
Ausführungsplan
------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1  0   MERGE JOIN
2  1     SORT (JOIN)
3  2       TABLE ACCESS (FULL) OF 'CATEGORIES'
4  1     SORT (JOIN)
5  4       TABLE ACCESS (FULL) OF 'PRODUCTS'
20. Erstellen Sie einen Index auf der Spalte categoryid der Tabelle products. Führen Sie nun die Abfrage nochmals aus. Um welchen Join handelte es sich hierbei und warum?
create index id2 on products(categoryid);
select p.productname,c.categoryname 
  from products p, categories c 
  where p.categoryid=c.categoryid
;
Ausführungsplan
---------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1  0   TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCTS'
2  1     NESTED LOOPS
3  2       TABLE ACCESS (FULL) OF 'CATEGORIES'
4  2       INDEX (RANGE SCAN) OF 'ID2' (NON-UNIQUE)


Stored Outlines

Im folgenden werden Sie Stored Outlines, also gespeicherte Ausführungspläne testen und einsetzen. Führen Sie hierfür folgende Schritte durch:

1. Schalten Sie Autotrace ein

set autotrace on explain;
2. Erstellen Sie die Tabelle order_details.
start ~/1ZO-033/Northwind-Skript/order_details.txt
3. Fügen Sie zur Tabelle order_details eine Spalte namens paketgroesse varchar(10) hinzu
alter table order_details add(paketgroesse varchar(10));
4. Geben Sie für 1/3 der Datensätze den Wert 'klein' in die Spalte Paketgroesse.
update order_details set paketgroesse=mod(rownum,3);
update order_details set paketgroesse='klein' where paketgroesse='1';
5. Geben Sie für 1/3 der Datensätze den Wert 'mittel' in die Spalte Paketgroesse.
update order_details set paketgroesse='mittel' where paketgroesse='2';
6. Geben Sie für 1/3 der Datensätze den Wert 'gross' in die Spalte Paketgroesse.
update order_details set paketgroesse='gross' where paketgroesse='0';
commit;
7. Erstellen Sie einen Index basierend auf der Spalte paketgroesse
create index id1 on order_details(paketgroesse);

8. Ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'klein' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt?

select * from order_details where paketgroesse='klein';

Ausführungsplan
------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1  0   TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_DETAILS'
2  1     INDEX (RANGE SCAN) OF 'ID1' (NON-UNIQUE)
9. Erstellen Sie eine Stored Outline für die eben erstellte Abfrage.
create or replace outline o1 for category cat1 on 
  select * from order_details where paketgroesse='klein';
10. Aktivieren Sie diese Stored Outline Wie lautet die Anweisung hierfür?
alter session set use_stored_outlines=cat1;
11. Generieren Sie eine Statistik für die Tabelle order_details
analyze table order_details compute statistics;
12. Ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'klein' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt?
select * from order_details where paketgroesse='klein';

Ausführungsplan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=718 Bytes=14360)
1  0   TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_DETAILS' (Cost=9 Card=718 Bytes=14360)
2  1     INDEX (RANGE SCAN) OF 'ID1' (NON-UNIQUE) (Cost=2 Card=718)
13. Ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'mittel' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt?
select * from order_details where paketgroesse='mittel';

Ausführungsplan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=718 Bytes=14360)
1  0   TABLE ACCESS (FULL) OF 'ORDER_DETAILS' (Cost=3 Card=718 Bytes=14360)
14. Deaktivieren Sie diese Stored Outline.
alter session set use_stored_outlines=false;
Session wurde geändert.
15. Ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'klein' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt?
select * from order_details where paketgroesse='klein';

Ausführungsplan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=718 Bytes=14360)
1  0   TABLE ACCESS (FULL) OF 'ORDER_DETAILS' (Cost=3 Card=718 By tes=14360)
16. Aktivieren Sie diese Stored Outline erneut und ermitteln Sie die Datensätze aus order_details, deren Paketgröße 'klein' ist. Wird ein Index-Scan oder ein Full-Table-Scan durchgeführt?
alter session set use_stored_outlines=cat1;
select * from order_details where paketgroesse='klein';

Ausführungsplan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=718 Bytes=14360)
1  0   TABLE ACCESS (BY INDEX ROWID) OF 'ORDER_DETAILS' (Cost=9 Card=718 Bytes=14360)
2  1     INDEX (RANGE SCAN) OF 'ID1' (NON-UNIQUE) (Cost=2 Card=718)

Optimierungen

Spielen Sie die Datenbank aus einer Sicherung zurück.

Voraussetzung:

Optimieren Sie folgende Anweisungen. Halten Sie Ihrer Vorgehensweise und die Ergebnisse einschließlich der Zeitangaben schriftlich fest.

1. Optimierung: Ermittlung der minimalen orderid

create index inx_oid 
  on scott.orders(orderid) tablespace indx
;
analyze table scott.orders compute statistics 
  for columns orderid size 16
;

select distinct min(orderid) from scott.orders;

MIN(ORDERID)
------------
       10248
Abgelaufen: 00:00:00.00

Ausführungsplan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1  0   SORT (AGGREGATE)
2  1     INDEX (FULL SCAN (MIN/MAX)) OF 'INX_OID' (NON-UNIQUE)

-- Ausführungsplan speichern
create outline oid_min for category cat1 on 
  select distinct min(orderid) from scott.orders
;
2. Optimierung: Wie viel Auslaufprodukte gibt es?
-- bitmap index erstellen
create bitmap index inx_disc 
  on scott.products(discontinued) tablespace indx
;
-- analysieren 
analyze table scott.products compute statistics 
  for columns discontinued size 2
;
--  Fall 1
select count(*) from scott.products where discontinued=1;

  COUNT(*)
----------
    131072

Abgelaufen: 00:00:02.07

Ausführungsplan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1  0   SORT (AGGREGATE)
2  1     TABLE ACCESS (FULL) OF 'PRODUCTS'

-- Fall 2
select /*+first_rows*/ count(*) 
  from scott.products where discontinued = 1
;
  COUNT(*)
----------
    131072

Abgelaufen: 00:00:00.00

Ausführungsplan
---------------------------------------------------------
0    SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=3 Card=1 Bytes=2)
1  0   SORT (AGGREGATE)
2  1     BITMAP CONVERSION (COUNT)
3  2       BITMAP INDEX (SINGLE VALUE) OF 'INX_DISC'
3. Optimierung: Welche Produkte fangen mit CH (Klein- oder Großschreibung egal) an?
create bitmap index id3 on products(productname);

analyze table products compute statistics for columns productname size 80;

select distinct productname from products 
  where lower(productname) like 'ch%';

PRODUCTNAME
----------------------------------------
Chai
Chang
Chartreuse verte
Chef Anton's Cajun Seasoning
Chef Anton's Gumbo Mix
Chocolade

6 Zeilen ausgewählt.

Abgelaufen: 00:00:09.05

Ausführungsplan
---------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1  0   SORT (UNIQUE)
2  1     TABLE ACCESS (FULL) OF 'PRODUCTS'

select /*+ index(id3) */distinct productname 
  from products where lower(productname) like 'ch%'
;
PRODUCTNAME
----------------------------------------
Chai
Chang
Chartreuse verte
Chef Anton's Cajun Seasoning
Chef Anton's Gumbo Mix
Chocolade

6 Zeilen ausgewählt.

Abgelaufen: 00:00:00.01

Ausführungsplan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=139 Card=77 Bytes=1309)
1  0   SORT (UNIQUE) (Cost=139 Card=77 Bytes=1309)
2  1     BITMAP INDEX (FAST FULL SCAN) OF 'ID3'
4. Optimierung: Wie teuer ist das Produkt namen Chai. Sie wissen nicht, ob dieses Produkt groß oder klein geschrieben ist. Optimieren Sie dieses Statement so weit es geht. Hier geht es um Geschwindigkeit um jeden Preis.
create index id34 on products(productname);
analyze index id34 validate structure;

select distinct productname,unitprice from products
  where lower(productname) = 'chai'
;

PRODUCTNAME             UNITPRICE
---------------------------------
Chai                           18

Abgelaufen: 00:00:03.09

Ausführungsplan
---------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1  0   SORT (UNIQUE)
2  1     TABLE ACCESS (FULL) OF 'PRODUCTS'

select /*+ index(id34)*/ distinct productname,unitprice from products
where lower(productname) = 'chai';

PRODUCTNAME             UNITPRICE
---------------------------------
Chai                           18

Abgelaufen: 00:00:03.08

Ausführungsplan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=1082 Card=8625 Bytes=301875)
1  0   SORT (UNIQUE) (Cost=1082 Card=8625 Bytes=301875)
2  1     TABLE ACCESS (FULL) OF 'PRODUCTS' (Cost=1017 Card=8625 Bytes=301875)

analyze index id34 delete statistics;
drop index id34;

create index fid34 on products(lower(productname));

select /*+ index(fid34)*/ distinct productname,unitprice from products
where lower(productname) = 'chai';

PRODUCTNAME             UNITPRICE
---------------------------------
Chai                           18

Abgelaufen: 00:00:02.02

Ausführungsplan
----------------------------------------------------------
0  SELECT STATEMENT Optimizer=CHOOSE (Cost=67 Card=8625 Bytes=301875)
1  0   SORT (UNIQUE) (Cost=67 Card=8625 Bytes=301875)
2  1     TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCTS' (Cost=2 Card=8625 Bytes=301875)
3  2       INDEX (RANGE SCAN) OF 'FID34' (NON-UNIQUE) (Cost=1 Card=3450)

analyze index fid34 delete statistics;
drop index fid34;

create bitmap index bid34 on products(productname,unitprice);

select /*+ index(bid34)*/ distinct productname,unitprice
from products where lower(productname) = 'chai';

PRODUCTNAME             UNITPRICE
---------------------------------
Chai                           18

Abgelaufen: 00:00:00.01

Ausführungsplan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=88 Card=8625 Bytes=301875)
1  0   SORT (UNIQUE) (Cost=88 Card=8625 Bytes=301875)
2  1     BITMAP INDEX (FAST FULL SCAN) OF 'BID34'

analyze index bid34 delete statistics;
drop index bid34;
5. Optimierung: Welche Produkte (productname und unitprice) werden von Lieferanten aus UK geliefert? Optimieren Sie hier um jeden Preis.
create index s_1 on suppliers(country, supplierid);
create bitmap index s_2 on products(productname, unitprice, supplierid);
select distinct productname, unitprice, country 
  from products, suppliers 
  where suppliers.supplierid(+)=products.supplierid and suppliers.country='UK'
;

PRODUCTNAME                               UNITPRICE COUNTRY
-----------------------------------------------------------
Aniseed Syrup                                    10 UK
Chai                                             18 UK
Chang                                            19 UK
Scottish Longbreads                            12,5 UK
Sir Rodney's Marmalade                           81 UK
Sir Rodney's Scones                              10 UK
Teatime Chocolate Biscuits                      9,2 UK

7 Zeilen ausgewählt.

Abgelaufen: 00:00:01.07

Ausführungsplan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=2393 Card=3376 Bytes=148544)
1  0 SORT (UNIQUE) (Cost=2393 Card=3376 Bytes=148544)
2  1   HASH JOIN (Cost=60 Card=142688 Bytes=6278272)
3  2     INDEX (RANGE SCAN) OF 'S_1' (NON-UNIQUE) (Cost=2 Card=2 Bytes=44)
4  2     BITMAP CONVERSION (TO ROWIDS)
5  4       BITMAP INDEX (FAST FULL SCAN) OF 'S_2'
6. Optimierung: Bei welchen Lieferanten ist der maximale Produktpreis größer als 200?
create bitmap index s_2 on products(supplierid, unitprice);
select supplierid 
  from (select supplierid, max(unitprice) m1 
  from products group by supplierid) where m1 > 200
;
SUPPLIERID
----------
        18

Abgelaufen: 00:00:02.08

Ausführungsplan
----------------------------------------------------------
0   SELECT STATEMENT Optimizer=CHOOSE (Cost=29 Card=2 Bytes=10)
1  0  FILTER
2  1    SORT (GROUP BY NOSORT) (Cost=29 Card=2 Bytes=10)
3  2      BITMAP CONVERSION (TO ROWIDS)
4  3        BITMAP INDEX (FULL SCAN) OF 'S_2'
7. Optimieren Sie folgende Abfrage: Wieviel Produkte haben an der zweiten Stelle ein h?
SQL> select count(distinct productname) from products where productname Like '_h%';

COUNT(DISTINCTPRODUCTNAME)
--------------------------
                         8
Abgelaufen: 00:00:00.02

Ausführungsplan
----------------------------------------------------------
0   SELECT STATEMENT Optimizer=CHOOSE (Cost=23 Card=1 Bytes=17)
1  0 SORT (GROUP BY)
2  1   BITMAP CONVERSION (TO ROWIDS)
3  2     BITMAP INDEX (FAST FULL SCAN) OF 'B2_IND'


Materialized Views

1. Geben Sie Scott die folgenden Berechtigungen:
grant CREATE SESSION to scott;
grant CREATE TABLE to scott;
grant CREATE MATERIALIZED VIEW to scott;
grant QUERY REWRITE to scott;
2. Melden Sie sich als scott mit dem Kennwort tiger an der Datenbank an.
connect scott/tiger@testdb29;
3. Erstellen Sie eine Tabelle namens bigtab, die auf den Spalten und Datensätzen der Tabelle all_objects basiert.
create table bigtab as select * from all_objects;
4. Verdoppeln Sie die Tabelle 5 mal. Wie viel Datensätze hat die Tabelle?
-- 5 mal
insert into bigtab select * from bigtab;
commit;

select count(*) from bigtab;

  COUNT(*)
----------
    751840
5. Analysieren Sie die Tabelle.
analyze table bigtab compute statistics;
6. Schreiben Sie eine Abfrage, die Ihnen die Anzahl der Datensätze je owner anzeigt. Messen Sie die Zeit für die Ausführung dieser Abfrage und halten Sie Ihr Ergebnis schriftlich fest.
OWNER        COUNT(*)
---------------------
CTXSYS           6264
ELAN             1272
HR                816
MDSYS            5640
ODM              9768
ODM_MTR           288

set timing on;
7. Führen Sie folgende Befehle aus.
alter session set query_rewrite_enabled=true;
alter session set query_rewrite_integrity=enforced;
8. Erstellen Sie folgende Materialized View:
create materialized view mv_bigtab
   build immediate
   refresh on commit
   enable query rewrite
 as
   select owner, count(*) from bigtab group by owner
;
Materialized View wurde erstellt.

Abgelaufen: 00:00:12.08
9. Führen Sie folgende Abfrage aus. Messen Sie bitte die Zeit:
select owner,count(*) from bigtab group by owner;

Abgelaufen: 00:00:00.00
10. Was sind Ihre Schlussfolgerungen?

Es wurde die Materialized View verwendet. Dies ist eine sehr optimale Methode.

11. Führen Sie folgende Anweisung aus:

alter session set query_rewrite_enabled=false;
12. Was macht die Anweisung?

Die Materialized View kann so nicht mehr verwendet werden.

13. Führen Sie folgende Abfrage aus. Messen Sie bitte die Zeit:

select owner, count(*) from bigtab group by owner;

Abgelaufen: 00:00:07.05
14. Was sind Ihre Schlussfolgerungen?

Die Materialized View wurde nicht mehr verwendet.

15. Führen Sie folgende Anweisung aus:

alter session set query_rewrite_enabled = true;
16. Was macht die Anweisung?

Ermöglicht die Verwendung der Materialized View.

17. Fügen Sie einen Datensatz in die Tabelle bigtab hinzu.

insert into bigtab
         (OWNER, OBJECT_NAME, OBJECT_ID, CREATED, LAST_DDL_TIME)
  values ('Hans','bla',1,sysdate,sysdate)
;
18. Schließen Sie das Einfügen mit commit auch ab.
commit;
19. Führen Sie folgende Abfrage aus.
select owner, count(*) from bigtab group by owner;

Abgelaufen: 00:00:00.00
20. Was sind Ihre Schlussfolgerungen? Ist der neue Datensatz mit enthalten und wurde der materialisierte View benutzt?

Mit commit wird die Materialized View aktualisiert.

21. Testen Sie selbständig, ob das auch für das Löschen gilt.

delete bigtab where OWNER='Hans';
commit;

select owner, count(*) from bigtab group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
CTXSYS                               2048
MDSYS                                5376
...
Abgelaufen: 00:00:00.00
Auch beim Löschen wird die Materialized View aktualisiert.

22. Schreiben Sie die Abfrage so um, dass nur die Datensätze angezeigt werden, bei denen zum Owner mehr als 1000 Datensätze gehören. Führen Sie die Abfrage aus und beobachten Sie, ob der materialisierte View benutzt wird.

start /oracle/ora92/rdbms/admin/utlxplan.sql
set autotrace on explain;

select owner, count(*) from bigtab group by owner having count(*) > 1000;

OWNER                            COUNT(*)
------------------------------ ----------
CTXSYS                               2048
MDSYS                                5376
ODM                                  7904
OLAPSYS                              4256
ORDSYS                              28896
PUBLIC                             369280
SYS                                322528
WKSYS                                3776
WMSYS                                1824
XDB                                  5152

10 Zeilen ausgewählt.

Abgelaufen: 00:00:00.00

Ausführungsplan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=4 Bytes=120)
1  0   TABLE ACCESS (FULL) OF 'MV_BIGTAB' (Cost=2 Card=4 Bytes=120)
Es wird die Materialized View verwendet, obwohl das Matching nicht exakt ist. Bei HAVING bringen Materialized Views Vorteile.

23. Löschen Sie die Materialized View wieder.

drop materialized view name;
Zusatzaufgaben

1. Erstellen Sie eine Tabelle namens lehrer und tragen Sie dort 5 Lehrer ein. Bestätigen Sie das mit commit.

create table lehrer
  (
    Name varchar2(29)
  )
;
insert into lehrer values ('Meier');
insert into lehrer values ('Müller');
insert into lehrer values ('Schmidt');
insert into lehrer values ('Schulz');
insert into lehrer values ('Meyer');
commit;
2. Erstellen Sie einen materialized view namens mv1, der die Anzahl der Datensätze der Tabelle lehrer ausgibt.
create materialized view mv1
   build immediate
   refresh on commit
   enable query rewrite
 as
   select count(*) from lehrer
;
Hinweis (ohne count):
SQL> create materialized view mv1
  2     build immediate
  3     refresh on commit
  4     enable query rewrite
  5   as
  6     select * from lehrer
  7  ;
   select * from lehrer
                 *
FEHLER in Zeile 6:
ORA-12014: Tabelle 'LEHRER' enthält kein Primärschlüssel-Constraint
3. Schreiben Sie eine Abfrage basierend auf der Tabelle lehrer, welche die Anzahl der Datensätze ermittelt und beobachten Sie, ob die Materialized View benutzt wird.
select count(*) from lehrer;

  COUNT(*)
----------
         5

Abgelaufen: 00:00:00.00

Ausführungsplan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1  0   SORT (AGGREGATE)
2  1     TABLE ACCESS (FULL) OF 'LEHRER'
Die Materialized Views wurde nicht verwendet, da der CBO nicht aktiv ist (kein ANALYZE).

4. Fügen Sie einen Datensatz zur Tabelle lehrer hinzu.

insert into lehrer values ('Kohl');
commit;
5. Schreiben Sie eine Abfrage basierend auf der Tabelle lehrer, welche die Anzahl der Datensätze ermittelt und beobachten Sie, ob der Materialized View benutzt wird.

select count(*) from lehrer;
  COUNT(*)
----------
         6

Abgelaufen: 00:00:00.00

Ausführungsplan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1  0   SORT (AGGREGATE)
2  1     TABLE ACCESS (FULL) OF 'LEHRER'


Ressourcen-Manager

Teil 1

Erstellen Sie folgenden Ressource-Manager-Plan per Anweisung. Aktivieren Sie ihn anschließend. Erstellen Sie auch folgende Benutzer und ordnen Sie diese den entsprechenden Gruppen zu.
OLTP_Group: OLTP_1
BATCH_Group: Batch_1
ADHOC_Group: Adhoc_1

Ressourcen Gruppen Level 1 Level 2 Level 3 Parallel Degree Limit
OLTP_GROUP 20% 0% 0% 0
BATCH_GROUP 0% 80% 0% 10
ADHOC_GROUP 0% 20% 0% 50
OTHER_GROUP 0% 0% 100% 0

User erstellen

create user OLTP_1 identified by OLTP_1;
create user Batch_1 identified by Batch_1;
create user Adhoc_1 identified by Adhoc_1;
Pending-Area neu erstellen.
exec dbms_resource_manager.clear_pending_area();
exec dbms_resource_manager.create_pending_area();
NIGHT_PLAN erstellen
exec dbms_resource_manager.create_plan(plan => 'NIGHT_PLAN',comment=>'Mein erster Plan');
Gruppen erstellen
exec dbms_resource_manager.create_consumer_group
  (consumer_group => 'OLTP_Group',comment=>'Meine erste Gruppe')
;
exec dbms_resource_manager.create_consumer_group
  (consumer_group => 'BATCH_Group',comment=>'Meine zweite Gruppe')
;
exec dbms_resource_manager.create_consumer_group
  (consumer_group => 'ADHOC_Group',comment=>'Meine dritte Gruppe')
;
Resources
exec dbms_resource_manager.create_plan_directive
  (plan => 'NIGHT_PLAN', group_or_subplan => 'OLTP_Group', 
   cpu_p1 => 20, parallel_degree_limit_p1 =>  0, comment=>'')
;
exec dbms_resource_manager.create_plan_directive
  (plan => 'NIGHT_PLAN', group_or_subplan => 'BATCH_Group', 
   cpu_p2 => 80, parallel_degree_limit_p1 => 10, comment=>'')
;
exec dbms_resource_manager.create_plan_directive
  (plan => 'NIGHT_PLAN', group_or_subplan => 'ADHOC_Group', 
   cpu_p2 => 20, parallel_degree_limit_p1 => 5, comment=>'')
;
exec dbms_resource_manager.create_plan_directive
  (plan => 'NIGHT_PLAN', group_or_subplan => 'OTHER_GROUPS', 
   cpu_p3 => 100, parallel_degree_limit_p1 =>  0, comment=>'')
;
Submit
exec dbms_resource_manager.submit_pending_area();
exec dbms_resource_manager.create_pending_area();
Berechtigung den Users zuweisen
exec dbms_resource_manager_privs.grant_switch_consumer_group
  (grantee_name => 'OLTP_1',consumer_group => 'OLTP_Group', grant_option => FALSE)
;
exec dbms_resource_manager_privs.grant_switch_consumer_group
  (grantee_name => 'Batch_1',consumer_group => 'BATCH_Group', grant_option => FALSE)
;
exec dbms_resource_manager_privs.grant_switch_consumer_group
  (grantee_name => 'Adhoc_1',consumer_group => 'ADHOC_Group', grant_option => FALSE)
;
User zuweisen
exec dbms_resource_manager.set_initial_consumer_group
  (user => 'OLTP_1',consumer_group => 'OLTP_Group')
;
exec dbms_resource_manager.set_initial_consumer_group
  (user => 'Batch_1',consumer_group => 'BATCH_Group')
;
exec dbms_resource_manager.set_initial_consumer_group
  (user => 'Adhoc_1',consumer_group => 'ADHOC_Group')
;
Plan aktivieren
alter system set resource_manager_plan = 'NIGHT_PLAN';
Teil 2

Verändern Sie den Plan so, dass für alle anderen (also other_groups) alle Abfragen, die länger als 5 Sekunden dauern, geblockt werden. Überprüfen Sie Ihr Ergebnis anhand eines geeigneten Szenarios

exec dbms_resource_manager.clear_pending_area();
exec dbms_resource_manager.create_pending_area();
Update
exec dbms_resource_manager.update_plan_directive
  (plan => 'NIGHT_PLAN', group_or_subplan => 'OTHER_GROUPS', new_max_est_exec_time => 5)
;
Submit
exec dbms_resource_manager.SUBMIT_pending_area();
Teil 3

Verändern Sie den Plan so, dass für adhoc_user alle Abfragen, die länger als 20 Sekunden dauern, geblockt werden. Außerdem sollten diese User nicht mehr als 6 paralelle Sessions geöffnet haben können. Überprüfen Sie Ihr Ergebnis anhand eines geeigneten Szenarios.

exec dbms_resource_manager.clear_pending_area();
exec dbms_resource_manager.create_pending_area();
Update
exec dbms_resource_manager.update_plan_directive
  (plan => 'NIGHT_PLAN', group_or_subplan => 'ADHOC_Group', 
   new_max_est_exec_time => 20, new_parallel_degree_limit_p1 => 6)
;
Submit
exec dbms_resource_manager.SUBMIT_pending_area();


Buffer-Cache

In der folgenden Übung werden Sie den Buffer-Cache einrichten und tunen. Voraussetzung hierfür ist Ihre Datenbank und ein funktionierendes SPFILE. Sollte dies noch nicht erfüllt sein, so unternehmen Sie alle notwendigen Schritte hierfür. Führen Sie, um Ihre Datenbank wieder im Fehlerfall wieder herstellen zu können, eine Offline-Sicherung Ihrer Datenbank durch. Die Ergebnisse können durchaus variieren.

1. Erstellen Sie eine Tabelle abteilung mit einer Spalte S1 char(1000) und zehn Datensätzen (von 'AAA' bis 'JJJ')

create table abteilung (s1 char(1000));
insert into abteilung values('AAA');
insert into abteilung values('BBB');
insert into abteilung values('CCC');
insert into abteilung values('DDD');
insert into abteilung values('EEE');
insert into abteilung values('FFF');
insert into abteilung values('GGG');
insert into abteilung values('HHH');
insert into abteilung values('III');
insert into abteilung values('JJJ');
2. Verdoppeln Sie die Tabelle 10 mal.
-- 10 x
insert into abteilung select * from abteilung;
commit;
3. Wie viel Datensätze haben Sie nun?
select count(*) from abteilung;
  COUNT(*)
----------
     10240
4. Stellen Sie sicher, dass die gecachten Blöcke niemals ausgelagert werden. Ermitteln Sie hierfür die Größe der Tabelle abteilung. Erstellen Sie gemäß dieser Größe den entsprechenden Buffer-Pool. Stellen Sie sicher, dass die Tabelle in den Keep-Pool geschrieben wird.
select sum(bytes) 
  from dba_segments 
  where owner = 'SYS' 
    and segment_name = 'ABTEILUNG'
;
SUM(BYTES)
----------
  12582912
= 12 MByte
alter system set DB_KEEP_CACHE_SIZE=16M;
-- Anzeige:
select 1, name, to_number(value/1024/1024) value 
  from v$parameter 
  where upper(name) like 'DB%CACHE_SIZE' 
    or 
       upper(name) 
       in ('SHARED_POOL_SIZE','LARGE_POOL_SIZE','JAVA_POOL_SIZE','LOG_BUFFER') 
     union 
       select 1, '+ 1MB', 1 from dual order by 2
;
-- Tabelle in den Keep-Pool
ALTER TABLE ABTEILUNG STORAGE (buffer_pool KEEP);
5. Führen Sie mehrfach eine Abfrage der Tabelle Abteilung durch und beobachten Sie jeweils die Hit Ratio für den Keep-Pool.
-- Abfrage (mehrmals)
select count(*) from abteilung where s1='AAA';

-- Abfrage Hit Ratio:
select 1-value/
      (
        select sum(value) 
          from v$sysstat 
          where lower(name) in ('consistent gets','db block gets')
      ) 
  from v$sysstat 
  where lower(name) in ('physical reads')
;
,959986246
,960109863
,960304683
Hit Ratio steigt.

6. Beobachten Sie ebenfalls die Werte in der Tabelle v$buffer_pool_statistics (PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS).

select name,
       100-round((physical_reads/(db_block_gets+consistent_gets))*100,2) 
        as Hit_Ratio 
  from v$buffer_pool_statistics
;
FEHLER in Zeile 1:
ORA-01476: Divisor ist Null
Da KEEP gleich 0 ist. Ggf. ANALYZE ??
select name, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS 
  from v$buffer_pool_statistics
;
NAME           PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS
-------------- -------------- ------------- ---------------
KEEP                        0             0               0
DEFAULT                 17251        357396          202093
7. Löschen Sie die Tabelle Abteilung.
drop table abteilung;
8. Führen Sie die gleichen Tests für den Recycle-Pool durch. Halten Sie auch hier Ihre Ergebnisse schriftlich fest:

Bis 4. sind die Schritte identisch.

alter system set DB_KEEP_CACHE_SIZE=0M;
alter system set db_recycle_cache_size=16M;
ALTER TABLE ABTEILUNG STORAGE (buffer_pool recycle);
-- Abfrage (mehrmals)
select count(*) from abteilung where s1='AAA';

-- Abfrage Hit Ratio:
select 1-value/
       (
        select sum(value) 
          from v$sysstat 
          where lower(name) in ('consistent gets','db block gets')
       ) 
  from v$sysstat 
  where lower(name) in ('physical reads')
;
,958975396
,959156127
,959268817
,959413019
select name,
       100-round((physical_reads/(db_block_gets+consistent_gets))*100,2) 
         as Hit_Ratio 
  from v$buffer_pool_statistics
;
FEHLER in Zeile 1:
ORA-01476: Divisor ist Null
select name, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS 
  from v$buffer_pool_statistics
;
NAME                 PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS
-------------------- -------------- ------------- ---------------
RECYCLE                           0             0               0
DEFAULT                       19180        394619          223113
9. Setzen Sie den buffer_cache auf 16 MB.
alter system set db_cache_size=16M;
10. Erzeugen Sie eine Tabelle angestellte auf Basis der Tabelle scott.emp
create table angestellte as select * from scott.emp;

11. Vergrößern Sie die Tabelle so lange, bis diese 32MB groß ist.

insert into ANGESTELLTE select * from ANGESTELLTE;
commit;
insert into ANGESTELLTE select * from ANGESTELLTE;
commit;
-- ...
select sum(bytes) 
  from dba_segments 
  where owner = 'SYS' 
    and segment_name = 'ANGESTELLTE'
;
SUM(BYTES)
----------
  45088768
12. Fragen Sie die Tabelle ab.
select count(*) from ANGESTELLTE;
  COUNT(*)
----------
    917504
13. Schauen Sie sich das Hit Ratio an. Was passiert und warum?
select 1-value/
       (
         select sum(value) 
           from v$sysstat 
           where lower(name) in ('consistent gets','db block gets')
        ) 
  from v$sysstat 
  where lower(name) in ('physical reads')
;
,938784339
,93226288
,92621924
,919666046
Hit Ratio nimmt ab.

14. Setzen Sie den Buffer_Cache auf 38 MB.

alter system set db_cache_size=38M;
15. Fragen Sie die Tabelle Angestellte ab.
select count(*) from ANGESTELLTE;
16. Schauen Sie sich das Hit Ratio an. Was passiert und warum?
select 1-value/
       (
         select sum(value) 
           from v$sysstat 
           where lower(name) in ('consistent gets','db block gets')
        ) 
  from v$sysstat 
  where lower(name) in ('physical reads')
;
,697806485
17. Fragen Sie die Tabelle Angestellte ab. Da Sie die Abfrage gleich wiederholen möchten, sollten die Blöcke nicht gleich wieder aus dem cache rausfliegen. Was müssen Sie tun?
,691264487
,682978723
18. Stellen Sie sicher, dass Full-Table-Scans der Tabelle Angestellte immer am MRU-Ende angefügt werden. Was müssen Sie hierfür tun?
select /*+ CACHE(ANGESTELLTE) */ count(*) 
  from ANGESTELLTE
;
,739961835
19. Probieren Sie den Cache-Advise aus.
,727582741

Kleine Optimierungsübung

Welche Produkte (productname) je Lieferant sind die preiswertesten?
grant CREATE SESSION to scott;
grant CREATE TABLE to scott;
grant CREATE MATERIALIZED VIEW to scott;
grant QUERY REWRITE to scott;
Da Materialized View unter sys nicht möglich sind, loggen wir uns als scott ein.
connect scott/tiger@testdb29;
start ~/1ZO-033/Northwind-Skript/suppliers.txt
start ~/1ZO-033/Northwind-Skript/products.txt
set timing on;
alter session set query_rewrite_enabled=true;
alter session set query_rewrite_integrity=enforced;
analyze table products compute statistics;
analyze table suppliers compute statistics;
select q.companyname, p.productname, q.Preis
  from products p, 
   (
    select s.companyname as companyname,s.supplierid, min(p.unitprice) as Preis
      from products p, suppliers s 
      where p.supplierid = s.supplierid
      group by s.companyname, s.supplierid
   ) q
   where p.supplierid = q.supplierid
    and q.Preis = p.unitprice
;
30 Zeilen ausgewählt.

Abgelaufen: 00:00:00.03
create materialized view mv_min_preise
   build immediate
   enable query rewrite
 as
   select q.companyname, p.productname, q.Preis
     from products p,
      (
       select s.companyname as companyname,s.supplierid, min(p.unitprice) as Preis
         from products p, suppliers s 
         where p.supplierid = s.supplierid
         group by s.companyname, s.supplierid
      ) q
     where p.supplierid = q.supplierid
       and q.Preis = p.unitprice
;
-- refresh on commit nicht möglich ??
select * from mv_min_preise;
30 Zeilen ausgewählt.

Abgelaufen: 00:00:00.01
select q.companyname, p.productname, q.Preis 
  from products p,
   (
    select s.companyname as companyname,s.supplierid, min(p.unitprice) as Preis
      from products p, suppliers s 
      where p.supplierid = s.supplierid
      group by s.companyname, s.supplierid
   ) q
   where p.supplierid = q.supplierid
    and q.Preis = p.unitprice
;

Ausführungsplan
----------------------------------------------------------
0   SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=82 Bytes=4674)
1 0   TABLE ACCESS (FULL) OF 'MV_MIN_PREISE' (Cost=2 Card=82 Bytes=4674)
In der Unix-Shell:
cd /oracle/admin/testdb29/udump/
tkprof testdb29_ora_1792.trc pinguin.trc


Undo / Rollback Segments

Im folgenden werden Sie das Undo-Management von Oracle etwas genauer kennenlernen.

1. Stellen Sie das Undo-Management auf Manuell um.

select * from v$tablespace;
...
1 UNDOTBS1  YES
...
Undo-Tablespace ist vorhanden.
alter system set undo_management=manual scope=spfile;
shutdown immediate;
startup;
2. Erstellen Sie ein neues Undo-Tablespace namens undots02. Alle weiteren Parameter können Sie frei wählen. Schalten Sie diesen Online.
CREATE UNDO TABLESPACE undots02 
  DATAFILE '/oracle/oradata/testdb29/undots02.dbf'
  SIZE 100M AUTOEXTEND ON
;
Tablespace wurde angelegt.

alter tablespace undots02 online;
3. Erstellen Sie in diesem Tablespace 5 Rollbacksegmente (rbs01 bis rbs05) und schalten Sie diese Online.
CREATE ROLLBACK SEGMENT rbs01 TABLESPACE undots02;
CREATE ROLLBACK SEGMENT rbs02 TABLESPACE undots02;
CREATE ROLLBACK SEGMENT rbs03 TABLESPACE undots02;
CREATE ROLLBACK SEGMENT rbs04 TABLESPACE undots02;
CREATE ROLLBACK SEGMENT rbs05 TABLESPACE undots02;
alter rollback segment rbs01 online;
alter rollback segment rbs02 online;
alter rollback segment rbs03 online;
alter rollback segment rbs04 online;
alter rollback segment rbs05 online;
4. Ändern Sie den Preis aller Produkte der Tabelle products um 10% nach oben. Die Undo-Informationen sollten im Rollback-Segment rbs01 erstellt werden.
set transaction use rollback segment rbs01;
start ~/1ZO-033/Northwind-Skript/products.txt
5. Versuchen Sie, die Szenarien 1 bis 5 nachzuspielen.
GRANT select ON products TO scott;
Szenario 1
-- Session 1: 
connect "sys/sys@testdb29 as sysdba";
-- Session 2:
connect scott/tiger@testdb29;
-- Session 1: 
update products set unitprice = unitprice*1.1;
77 Zeilen wurden aktualisiert.
-- Session 2:
select productname, unitprice 
  from sys.products 
  where productid = '75'
;
PRODUCTNAME                               UNITPRICE
---------------------------------------- ----------
Rhonbräu Klosterbier                           9,38

-- Session 1: 
commit;
-- Session 2:
select productname, unitprice 
  from sys.products 
  where productid = '75'
;
PRODUCTNAME                               UNITPRICE
---------------------------------------- ----------
Rhonbräu Klosterbier                          10,32
Szenario 2 und 3
-- Session 1:
delete from products where productid=77;
select productname, unitprice from sys.products where productid = '77';
Es wurden keine Zeilen ausgewählt.
-- Session 2:
select productname, unitprice from sys.products where productid = '77';
PRODUCTNAME                               UNITPRICE
---------------------------------------- ----------
Original Frankfurter grüne So¯e                17,3
-- Session 1: 
commit;
-- Session 2: 
select productname, unitprice from sys.products where productid = '77';
Es wurden keine Zeilen ausgewählt.
Szenario 4
-- Session 2: 
set transaction read only;
select productname, unitprice from sys.products where productid = '75';
PRODUCTNAME                               UNITPRICE
---------------------------------------- ----------
Rhönbräu Klosterbier                          10,32
-- Session 1: 
update products set unitprice = unitprice*1.1;
commit;
-- Session 2:
set transaction read only;
select productname, unitprice from sys.products where productid = '75';
PRODUCTNAME                               UNITPRICE
---------------------------------------- ----------
Rhönbräu Klosterbier                          10,32
-- Session 1:
update products set unitprice = unitprice*1.1;
commit;
select productname, unitprice from sys.products where productid = '75';
PRODUCTNAME                               UNITPRICE
---------------------------------------- ----------
Rhönbräu Klosterbier                          12,49
-- Session 2: 
select productname, unitprice from sys.products where productid = '75';
PRODUCTNAME                               UNITPRICE
---------------------------------------- ----------
Rhönbräu Klosterbier                          10,32
--
-- Ein commit beendet set transaction read only:
commit;
-- Session 1: 
update products set unitprice = unitprice*1.1;
commit;
select productname, unitprice from sys.products where productid = '75';
PRODUCTNAME                               UNITPRICE
---------------------------------------- ----------
Rhönbräu Klosterbier                          13,74
-- Session 2:
select productname, unitprice from sys.products where productid = '75';
PRODUCTNAME                               UNITPRICE
---------------------------------------- ----------
Rhönbräu Klosterbier                          13,74
Szenario 5:
-- Session 2:
set transaction read only;
-- Session 1:
-- 12 mal
insert into sys.products select * from sys.products;
commit;
delete from sys.products where productid=75;
4096 Zeilen wurden gelöscht.
insert into sys.products select * from sys.products;
...
-- Session 2:
select productname, unitprice from sys.products where productid = '75';
select productname, unitprice from sys.products where productid = '75'
*
FEHLER in Zeile 1:
ORA-01555: Snapshot zu alt: Rollback-Segmentnummer 24 namens "RBS04" ist zu klein.


StatsPack

1. Löschen Sie alle großen Tabellen.
drop table ...;
2. Erstellen Sie die Datenbank northwind vom Skript.
start ~/1ZO-033/Northwind-Skript/alles.txt
3. Führen Sie alle notwendigen Schritte aus, um StatsPack zu installieren.
start /oracle/ora92/rdbms/admin/spcreate.sql
...
Geben Sie einen Wert für perfstat_password ein: pinguin
...
Geben Sie einen Wert für default_tablespace ein: USERS
...
Geben Sie einen Wert für temporary_tablespace ein: TEMP
...
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
4. Erstellen Sie folgende Abfragen, speichern Sie diese, um diese Abfragen später ausführen zu können.

Wie viele Kunden haben Getränke bestellt?

select distinct companyname
  from
        customers cus,
        orders ord,
        order_details ode,
        products pro,
        categories cat
  where
        cus.customerid   = ord.customerid
    and ode.orderid      = ord.orderid
    and pro.productid    = ode.productid
    and cat.categoryid   = pro.categoryid
    and cat.Categoryname = 'Beverages'
;
Wie heißt der älteste Mitarbeiter?
select lastname,firstname,birthdate
  from employees
  where birthdate in
  (select min(birthdate) from employees)
;
Welche Produkte liefern deutsche Lieferanten?
select distinct pro.productname
  from
    suppliers sup, products pro
  where
    sup.supplierid = pro.supplierid
    and country = 'Germany'
;
5. Erstellen Sie den ersten Snapshot.
sqlplus perfstat/pinguin@testdb29
execute statspack.snap
6. Führen Sie die Abfragen aus.

s.o.

7. Erstellen Sie den zweiten Snapshot

sqlplus perfstat/pinguin@testdb29
execute statspack.snap
8. Erstellen Sie den Bericht
select snap_id, snap_time from stats$snapshot;

   SNAP_ID SNAP_TIM
---------- --------
         1 05.03.04
         2 05.03.04

start /oracle/ora92/rdbms/admin/spreport.sql
...
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Geben Sie einen Wert für begin_snap ein: 1
Begin Snapshot Id specified: 1
Geben Sie einen Wert für end_snap ein: 2
...
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2.  To use this name,
press <return> to continue, otherwise enter an alternative.
Geben Sie einen Wert für report_name ein: /tmp/sp_report_1_2
9. Schauen Sie sich den Bericht im Betriebssystem an.
less /tmp/sp_report_1_2


Index-Cluster

Im folgenden werden Sie einen Index-Cluster kennen lernen und praktisch erstellen.

1. Führen Sie folgende Anweisung aus, um den Cluster zu erstellen.

CREATE CLUSTER personnel (department_number NUMBER(2)) SIZE 512;
2. Was bedeutet Size 512?

SIZE sollte in der Größe eines "Datensatz" definiert werden.

3. Erstellen Sie eine Tabelle emp und ordnen diese dem Cluster zu.

CREATE TABLE emp 
  (
     empno     NUMBER        PRIMARY KEY,
     ename     VARCHAR2(10)  NOT NULL CHECK (ename = UPPER(ename)),
     job       VARCHAR2(9),
     mgr       NUMBER        , 
     hiredate  DATE          , 
     sal       NUMBER(10,2)  CHECK (sal > 500),
     comm      NUMBER(9,0)   DEFAULT NULL,
     deptno    NUMBER(2)      NOT NULL
   ) 
   CLUSTER personnel (deptno)
;
4. Erstellen Sie eine Tabelle dept und ordnen diese dem Cluster zu.
CREATE TABLE dept 
  (
    deptno  NUMBER(2),
    dname   VARCHAR2(9), 
    loc     VARCHAR2(9)
  ) 
  CLUSTER personnel (deptno)
;
5. Versuchen Sie einen Datensatz in eine der beiden Tabellen hinzuzufügen.
insert into dept values (1,'Head','Berlin');
            *
FEHLER in Zeile 1:
ORA-02032: Cluster-Tabellen sind erst nach Aufbau des Cluster-Indexes zu
verwenden
6. Erstellen Sie nun den Index-Cluster.
CREATE INDEX idx_personnel ON CLUSTER personnel;
7. Versuchen Sie einen Datensatz in eine der beiden Tabellen hinzuzufügen.
insert into dept values (1,'Head','Berlin');
1 Zeile wurde erstellt.
commit;
8. Veranlassen Sie, dass die Tabellen products_cl und suppliers_cl in einem Index-Cluster zusammengefasst werden. Begründen Sie Ihre Wahl für die Size.

Berechnung der Größe von SIZE:

describe products;
 Name                                      Null?    Typ
 ----------------------------------------- -------- -------------

 PRODUCTID                                 NOT NULL NUMBER(38)
 PRODUCTNAME                               NOT NULL VARCHAR2(40)
 SUPPLIERID                                         NUMBER(38)
 CATEGORYID                                         NUMBER(38)
 QUANTITYPERUNIT                                    VARCHAR2(20)
 UNITPRICE                                          NUMBER(20,2)
 UNITSINSTOCK                                       NUMBER(38)
 UNITSONORDER                                       NUMBER(38)
 REORDERLEVEL                                       NUMBER(38)
 DISCONTINUED                              NOT NULL NUMBER(38)

Spaltenbreite gesamt: 346
describe suppliers;
 Name                                      Null?    Typ
 ----------------------------------------- -------- --------------

 SUPPLIERID                                NOT NULL NUMBER(38)
 COMPANYNAME                               NOT NULL VARCHAR2(40)
 CONTACTNAME                                        VARCHAR2(30)
 CONTACTTITLE                                       VARCHAR2(30)
 ADDRESS                                            VARCHAR2(60)
 CITY                                               VARCHAR2(15)
 REGION                                             VARCHAR2(15)
 POSTALCODE                                         VARCHAR2(10)
 COUNTRY                                            VARCHAR2(15)
 PHONE                                              VARCHAR2(24)
 FAX                                                VARCHAR2(24)
 HOMEPAGE                                           VARCHAR2(255)
Spaltenbreite gesamt: 556

Ermitteln des Durchschnittswertes der 1-n-Beziehung:

select avg(count(*)) from products group by supplierid order by 1;

AVG(COUNT(*))
-------------
   2,65517241
Spaltenbreite gesamt der ersten Tabelle minus Spaltenbreite der gemeinsamen Spalte (SUPPLIERID) multipliziert mit dem ermittelten Durchschnitt. Die Spaltenbreite der Tabelle zwei wird dazu addiert und zur Sicherheit wird alles mit 20% multipliziert.

((346 - 38) * 2,65517241 + 556) * 1,2 = 1648,551722736

Das Ergebnis wird aufgerundet.

Cluster erstellen:

CREATE CLUSTER prod_supp_cl (SUPPLIERID NUMBER(38)) SIZE 1700;
Cluster Index erstellen:
CREATE INDEX idx_prod_supp_cl ON CLUSTER prod_supp_cl;
Erste Tabellen erstellen
Beachte: CategoryID ist als int zu definieren und nicht als number.
CREATE TABLE products_cl 
  (
    ProductID      int   NOT NULL ,
    ProductName    varchar2  (40) NOT NULL ,
    SupplierID     number(38)  NULL ,
    CategoryID     int  NULL ,
    QuantityPerUnit   varchar2  (20) NULL ,
    UnitPrice      number(20,2)  NULL ,
    UnitsInStock   int  NULL ,
    UnitsOnOrder   int  NULL ,
    ReorderLevel   int  NULL ,
    Discontinued   int  NOT NULL
  )
  CLUSTER prod_supp_cl(SUPPLIERID)
;
insert into products_cl select * from products;
Zweite Tabelle
Beachte: CategoryID ist als int zu definieren und nicht als number. Die Quelltabelle muss daher angepasst werden:
alter table suppliers modify (SUPPLIERID number(38));

CREATE TABLE suppliers_cl
  CLUSTER prod_supp_cl(SUPPLIERID)
  as select * from suppliers
;
Im folgenden werden Sie eine IOT erstellen. Führen Sie hierfür folgende Schritte durch: Führen Sie folgende Anweisung aus:
CREATE TABLE my_iot 
  (partno number,name varchar2(20),CONSTRAINT pk_my_iot PRIMARY KEY (partno))
  ORGANIZATION INDEX TABLESPACE users including name
  OVERFLOW TABLESPACE users
;

next Bibliography
up Appendix
previous Fundamentals I - Übungen und Lösungen
  Contents   Index


Stefan Hietel dama.go GmbH, Robert Warnke http://rowa.giso.de