--
-- Erstellung eines Beispielschemas mit generierten Testdaten
--
-- Zweck: (A) Test einer bestehenden Loesung und Vermessung von Statements
--
-- Voraussetzungen:
--         - Beispielschema DEMO mit entsprechender Berechtigung (dbms_stats-Package)
--         - hinreichend grosser default Tablespace fuer die Objekte
--
-- Vorgehen:
--         - Objekte (Funktionen, Prozeduren, Tabellen im Beispielschema anlegen
--           (Teil 1)
--         - Objekte befuellen (Teil 2)
--         - Teststatements laufenlassen (Teil 3)
--
-- Verfasser:
-- Frank Schneede,    ORACLE Deutschland B.V. u. Co. KG
--
-- Date     |Who | What
---------------------------------------------------------------
-- 22.02.10 | FS | Erstellung
--
-- =============================================================================
*/


/* =============================================================================
--
-- Teil 1: Erstellung aller Objekte im Beispielschema
--
-- =============================================================================
*/
SPOOL create_demo.log
SET linesize 200
SET pagesize 100
SET trimout ON
SET trimspool ON
SET serveroutput ON
SET echo ON
SET feedback ON
SET timing ON
SELECT 'Anlegen der Objekte fuer Beispielschema' FROM dual;

/* -----------------------------------------------------------------------------
-- Hilfsfunktionen zur Testdatengenerierung
-- -----------------------------------------------------------------------------
*/
CREATE OR REPLACE FUNCTION ran_m_n ( min_n    IN NUMBER
                                   , max_n    IN NUMBER
                                   , decimals IN NUMBER DEFAULT 0)
                  RETURN NUMBER
/* =============================================================================
-- FUNCTION RAN_N_M
--
-- Erzeugung von Zufallszahlen
--
-- Verfasser:
-- Frank Schneede,    ORACLE Deutschland B.V. u. Co. KG
--
-- Date     |Who | What
---------------------------------------------------------------
-- 22.02.10 | FS | Erstellung
--
  __  _   _   _     _
 (__)|-< /-\ (_ |_ (-_
-- =============================================================================
*/

IS
   random_number NUMBER;
   minimum_value NUMBER := 0;
   m             NUMBER := 0;
   digits        NUMBER := 1;

BEGIN
   IF max_n > min_n
   THEN
      minimum_value := min_n;
      -- the handling with default "Zero" prevent FROM a loop situation if the second parameter is lower
      -- than the first
   END IF;
   IF max_n < 10000000
   THEN
      digits :=7;
   END IF;
   IF max_n < 1000000
   THEN
      digits :=6;
   END IF;
   IF max_n < 100000
   THEN
      digits :=5;
   END IF;
   IF max_n < 10000
   THEN
      digits :=4;
   END IF;
   IF max_n < 1000
   THEN
      digits :=3;
   END IF;
   IF max_n < 100
   THEN
      digits :=2;
   END IF;
   LOOP
      random_number := TO_NUMBER(SUBSTR(TO_CHAR(DBMS_RANDOM.RANDOM), -digits, digits));
      IF  random_number < max_n
      AND random_number > minimum_value
      THEN
         IF DECIMALS > 0
         THEN
            m := TO_NUMBER(SUBSTR(TO_CHAR(DBMS_RANDOM.RANDOM), -DECIMALS, DECIMALS));
            random_number :=  TO_NUMBER(TO_CHAR(random_number)||'.'||TO_CHAR(m));
         END IF;

         RETURN random_number;
      END IF;
   END LOOP;
END;
/

CREATE OR REPLACE FUNCTION ran_words ( word_delimiter IN VARCHAR2 DEFAULT 'default'
                                     , words          IN VARCHAR2)
                  RETURN VARCHAR2
/* =============================================================================
-- FUNCTION RAN_WORDS
--
-- Erzeugung von zufaelligen VARCHAR2 Werten aus einer Liste
--
-- Verfasser:
-- Frank Schneede,    ORACLE Deutschland B.V. u. Co. KG
--
-- Date     |Who | What
---------------------------------------------------------------
-- 22.02.10 | FS | Erstellung
--
  __  _   _   _     _
 (__)|-< /-\ (_ |_ (-_
-- =============================================================================
*/
IS
   word_counter  NUMBER :=0;
   random_number NUMBER := 0;
   n             NUMBER := 0;
   value_group   VARCHAR2(20);
   result_string VARCHAR2(256);

   TYPE t_value_table IS
      TABLE OF VARCHAR2(20)
      INDEX BY BINARY_INTEGER;

   t_values t_value_table;

BEGIN
   t_values(1) := '';
   t_values(2) := '';
   t_values(3) := '';

   -- scan the values of parameter 'words'
   -- delimiter is the character in 'WORD_DELIMITER'
   LOOP
     n  := n+1;
     IF n = LENGTH(words)+1
     THEN
        EXIT;
     END IF;
     IF SUBSTR(words, n, 1) = word_delimiter
     THEN
         t_values(word_counter) := value_group;
         value_group := '';
         word_counter := word_counter+1;
     ELSE
         value_group := value_group||SUBSTR(words,n,1);
     END IF;
   END LOOP;

   -- find a correct random number to fetch a record of PL/SQL table
   LOOP
      random_number := TO_NUMBER(SUBSTR(TO_CHAR(DBMS_RANDOM.RANDOM),-2,2));
      IF random_number < word_counter
      THEN
         -- the correct random number is found
         EXIT;
      END IF;
   END LOOP;

   RETURN t_values(random_number);
END;
/

/* -----------------------------------------------------------------------------
-- Tabelle Fahrzeuge
-- -----------------------------------------------------------------------------
*/
DROP TABLE fahrzeuge;
CREATE TABLE fahrzeuge
   ( fgstnr              NUMBER       NOT NULL
   , marke               VARCHAR2(10) NOT NULL
   , modell              VARCHAR2(8)  NOT NULL
   , motor               VARCHAR2(8)  NOT NULL
   , getriebe            VARCHAR2(6)  NOT NULL
   , anz_tueren          NUMBER
   , farbe               VARCHAR2(10)
   , zubehoer            VARCHAR2(2800)
   , auslieferung_kunde  VARCHAR2(6)
   , land_kunde          VARCHAR2(3)
   , prodmonat           VARCHAR2(6)  NOT NULL
   , modelljahr          VARCHAR2(4)  NOT NULL
   )
  TABLESPACE  users
  PARTITION BY RANGE (modelljahr, land_kunde, marke)
  (
  PARTITION pfzg000101 VALUES LESS THAN ('2007', 'AUT', 'BMW'),
  PARTITION pfzg000102 VALUES LESS THAN ('2007', 'AUT', 'DAIMLER'),
  PARTITION pfzg000103 VALUES LESS THAN ('2007', 'AUT', 'VW'),
  PARTITION pfzg000104 VALUES LESS THAN ('2007', 'AUT', MAXVALUE),
  PARTITION pfzg000201 VALUES LESS THAN ('2007', 'CH',  'BMW'),
  PARTITION pfzg000202 VALUES LESS THAN ('2007', 'CH',  'DAIMLER'),
  PARTITION pfzg000203 VALUES LESS THAN ('2007', 'CH',  'VW'),
  PARTITION pfzg000204 VALUES LESS THAN ('2007', 'CH',  MAXVALUE),
  PARTITION pfzg000301 VALUES LESS THAN ('2007', 'DEU', 'BMW'),
  PARTITION pfzg000302 VALUES LESS THAN ('2007', 'DEU', 'DAIMLER'),
  PARTITION pfzg000303 VALUES LESS THAN ('2007', 'DEU', 'VW'),
  PARTITION pfzg000304 VALUES LESS THAN ('2007', 'DEU', MAXVALUE),
  PARTITION pfzg010101 VALUES LESS THAN ('2008', 'AUT', 'BMW'),
  PARTITION pfzg010102 VALUES LESS THAN ('2008', 'AUT', 'DAIMLER'),
  PARTITION pfzg010103 VALUES LESS THAN ('2008', 'AUT', 'VW'),
  PARTITION pfzg010104 VALUES LESS THAN ('2008', 'AUT', MAXVALUE),
  PARTITION pfzg010201 VALUES LESS THAN ('2008', 'CH',  'BMW'),
  PARTITION pfzg010202 VALUES LESS THAN ('2008', 'CH',  'DAIMLER'),
  PARTITION pfzg010203 VALUES LESS THAN ('2008', 'CH',  'VW'),
  PARTITION pfzg010204 VALUES LESS THAN ('2008', 'CH',  MAXVALUE),
  PARTITION pfzg010301 VALUES LESS THAN ('2008', 'DEU', 'BMW'),
  PARTITION pfzg010302 VALUES LESS THAN ('2008', 'DEU', 'DAIMLER'),
  PARTITION pfzg010303 VALUES LESS THAN ('2008', 'DEU', 'VW'),
  PARTITION pfzg010304 VALUES LESS THAN ('2008', 'DEU', MAXVALUE),
  PARTITION pfzg020101 VALUES LESS THAN ('2009', 'AUT', 'BMW'),
  PARTITION pfzg020102 VALUES LESS THAN ('2009', 'AUT', 'DAIMLER'),
  PARTITION pfzg020103 VALUES LESS THAN ('2009', 'AUT', 'VW'),
  PARTITION pfzg020104 VALUES LESS THAN ('2009', 'AUT', MAXVALUE),
  PARTITION pfzg020201 VALUES LESS THAN ('2009', 'CH',  'BMW'),
  PARTITION pfzg020202 VALUES LESS THAN ('2009', 'CH',  'DAIMLER'),
  PARTITION pfzg020203 VALUES LESS THAN ('2009', 'CH',  'VW'),
  PARTITION pfzg020204 VALUES LESS THAN ('2009', 'CH',  MAXVALUE),
  PARTITION pfzg020301 VALUES LESS THAN ('2009', 'DEU', 'BMW'),
  PARTITION pfzg020302 VALUES LESS THAN ('2009', 'DEU', 'DAIMLER'),
  PARTITION pfzg020303 VALUES LESS THAN ('2009', 'DEU', 'VW'),
  PARTITION pfzg020304 VALUES LESS THAN ('2009', 'DEU', MAXVALUE),
  PARTITION pfzg030101 VALUES LESS THAN ('2010', 'AUT', 'BMW'),
  PARTITION pfzg030102 VALUES LESS THAN ('2010', 'AUT', 'DAIMLER'),
  PARTITION pfzg030103 VALUES LESS THAN ('2010', 'AUT', 'VW'),
  PARTITION pfzg030104 VALUES LESS THAN ('2010', 'AUT', MAXVALUE),
  PARTITION pfzg030201 VALUES LESS THAN ('2010', 'CH',  'BMW'),
  PARTITION pfzg030202 VALUES LESS THAN ('2010', 'CH',  'DAIMLER'),
  PARTITION pfzg030203 VALUES LESS THAN ('2010', 'CH',  'VW'),
  PARTITION pfzg030204 VALUES LESS THAN ('2010', 'CH',  MAXVALUE),
  PARTITION pfzg030301 VALUES LESS THAN ('2010', 'DEU', 'BMW'),
  PARTITION pfzg030302 VALUES LESS THAN ('2010', 'DEU', 'DAIMLER'),
  PARTITION pfzg030303 VALUES LESS THAN ('2010', 'DEU', 'VW'),
  PARTITION pfzg030304 VALUES LESS THAN ('2010', 'DEU', MAXVALUE),
  PARTITION pfzgmax    VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE)
  );

DROP TABLE fahrzeuge11g;
CREATE TABLE fahrzeuge11g
   ( fgstnr              NUMBER       NOT NULL
   , marke               VARCHAR2(10) NOT NULL
   , modell              VARCHAR2(8)  NOT NULL
   , motor               VARCHAR2(8)  NOT NULL
   , getriebe            VARCHAR2(6)  NOT NULL
   , anz_tueren          NUMBER
   , farbe               VARCHAR2(10)
   , zubehoer            VARCHAR2(2800)
   , auslieferung_kunde  VARCHAR2(6)
   , land_kunde          VARCHAR2(3)
   , prodmonat           VARCHAR2(6)  NOT NULL
   , modelljahr          VARCHAR2(4)  NOT NULL
   )
  TABLESPACE  users
  PARTITION BY LIST (modelljahr)
  SUBPARTITION BY RANGE (land_kunde, marke)
  SUBPARTITION TEMPLATE (
  SUBPARTITION AUTBMW VALUES LESS THAN ('AUT', 'BMW'),
  SUBPARTITION AUTDAM VALUES LESS THAN ('AUT', 'DAIMLER'),
  SUBPARTITION AUTVW  VALUES LESS THAN ('AUT', 'VW'),
  SUBPARTITION AUT    VALUES LESS THAN ('AUT', MAXVALUE),
  SUBPARTITION CHBMW  VALUES LESS THAN ('CH',  'BMW'),    
  SUBPARTITION CHDAM  VALUES LESS THAN ('CH',  'DAIMLER'),
  SUBPARTITION CHVW   VALUES LESS THAN ('CH',  'VW'),     
  SUBPARTITION CH     VALUES LESS THAN ('CH',  MAXVALUE),     
  SUBPARTITION DEUBMW VALUES LESS THAN ('DEU', 'BMW'),    
  SUBPARTITION DEUDAM VALUES LESS THAN ('DEU', 'DAIMLER'),
  SUBPARTITION DEUVW  VALUES LESS THAN ('DEU', 'VW'),
  SUBPARTITION DEU    VALUES LESS THAN ('DEU', MAXVALUE),
  SUBPARTITION MAX    VALUES LESS THAN (MAXVALUE, MAXVALUE))
  (PARTITION p2007 VALUES ('2007'),
   PARTITION p2008 VALUES ('2008'),
   PARTITION p2009 VALUES ('2009'),
   PARTITION p2010 VALUES ('2010'),
   PARTITION other VALUES (DEFAULT));     


CREATE OR REPLACE PROCEDURE fahrzeuge_gen (anz_fahrzeuge NUMBER)
/* =============================================================================
-- PROCEDURE FEHRZEUGE_GEN
--
-- Erzeugung von zufaelligen Testdaten fuer die Tabelle Fahrzeuge
--
-- Verfasser:
-- Frank Schneede,    ORACLE Deutschland B.V. u. Co. KG
--
-- Date     |Who | What
---------------------------------------------------------------
-- 22.02.10 | FS | Erstellung
--
  __  _   _   _     _
 (__)|-< /-\ (_ |_ (-_
-- =============================================================================
*/
AS
   anz_fzg  NUMBER;
   recno    NUMBER;

BEGIN
   recno := 1;

   WHILE recno <= anz_fahrzeuge  LOOP
      INSERT INTO fahrzeuge ( fgstnr
                            , marke
                            , modell
                            , motor
                            , getriebe
                            , anz_tueren
                            , farbe
                            , zubehoer
                            , auslieferung_kunde
                            , land_kunde
                            , prodmonat
                            , modelljahr
                            )
      VALUES ( recno
             , ran_words('/','BMW/DAIMLER/VW/')
             , ran_words('/','Klein/Mittel/Gross/Sport/SUV/')
             , ran_words('/','1,8l/2,0l/2,2l/2,7l/3,2l/4,0l/5,0l/')
             , ran_words('/','AUTO/5Gang/6Gang/')
             , to_number(ran_words('/','3/5/'))
             , ran_words('/','rot/gelb/gruen/blau/silber/grau/weiss/schwarz/')
             , 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz'
             , ran_words('/','200701/200702/200703/200704/200705/200706/200707/200708/200709/200710/200711/200712/200801/200802/200803/200804/200805/200806/200807/200808/200809/200810/200811/200812/200901/200902/200903/200904/200905/200906/200907/200908/200909/200910/200911/200912/201001/201002/201003/201004/201005/201006/201007/201008/')
             , ran_words('/','AUT/CH/DEU/')
             , ran_words('/','200701/200702/200703/200704/200705/200706/200707/200708/200709/200710/200711/200712/200801/200802/200803/200804/200805/200806/200807/200808/200809/200810/200811/200812/200901/200902/200903/200904/200905/200906/200907/200908/200909/200910/200911/200912/201001/201002/201003/201004/201005/201006/201007/201008/')
             , ran_words('/','2007/2008/2009/2010/')
             );

      recno := recno + 1;
   END LOOP;
   COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE fahrzeuge11g_gen (anz_fahrzeuge NUMBER)
/* =============================================================================
-- PROCEDURE FEHRZEUGE_GEN
--
-- Erzeugung von zufaelligen Testdaten fuer die Tabelle Fahrzeuge
--
-- Verfasser:
-- Frank Schneede,    ORACLE Deutschland B.V. u. Co. KG
--
-- Date     |Who | What
---------------------------------------------------------------
-- 22.02.10 | FS | Erstellung
--
  __  _   _   _     _
 (__)|-< /-\ (_ |_ (-_
-- =============================================================================
*/
AS
   anz_fzg  NUMBER;
   recno    NUMBER;

BEGIN
   recno := 1;

   WHILE recno <= anz_fahrzeuge  LOOP
      INSERT INTO fahrzeuge11g ( fgstnr
                            , marke
                            , modell
                            , motor
                            , getriebe
                            , anz_tueren
                            , farbe
                            , zubehoer
                            , auslieferung_kunde
                            , land_kunde
                            , prodmonat
                            , modelljahr
                            )
      VALUES ( recno
             , ran_words('/','BMW/DAIMLER/VW/')
             , ran_words('/','Klein/Mittel/Gross/Sport/SUV/')
             , ran_words('/','1,8l/2,0l/2,2l/2,7l/3,2l/4,0l/5,0l/')
             , ran_words('/','AUTO/5Gang/6Gang/')
             , to_number(ran_words('/','3/5/'))
             , ran_words('/','rot/gelb/gruen/blau/silber/grau/weiss/schwarz/')
             , 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz'
             , ran_words('/','200701/200702/200703/200704/200705/200706/200707/200708/200709/200710/200711/200712/200801/200802/200803/200804/200805/200806/200807/200808/200809/200810/200811/200812/200901/200902/200903/200904/200905/200906/200907/200908/200909/200910/200911/200912/201001/201002/201003/201004/201005/201006/201007/201008/')
             , ran_words('/','AUT/CH/DEU/')
             , ran_words('/','200701/200702/200703/200704/200705/200706/200707/200708/200709/200710/200711/200712/200801/200802/200803/200804/200805/200806/200807/200808/200809/200810/200811/200812/200901/200902/200903/200904/200905/200906/200907/200908/200909/200910/200911/200912/201001/201002/201003/201004/201005/201006/201007/201008/')
             , ran_words('/','2007/2008/2009/2010/')
             );

      recno := recno + 1;
   END LOOP;
   COMMIT;
END;
/

SPOOL OFF;

/* =============================================================================
--
-- Teil 2: Befuellung der Schemaobjekte
--
-- =============================================================================
*/
pause
SPOOL fill_demo.log
SET linesize 200
SET pagesize 100
SET trimout ON
SET trimspool ON
SET serveroutput ON
SET echo ON
SET feedback ON
ALTER SESSION SET nls_date_format  = 'DDMMYYYY';
SET timing ON
SELECT 'Start der Verarbeitung' FROM dual;

/* -----------------------------------------------------------------------------
-- Fuellen Fahrzeuge (100.000 Eintraege)
-- -----------------------------------------------------------------------------
*/
SELECT '--> Tabelle Fahrzeuge erstellen' Aktion FROM dual;
EXEC fahrzeuge_gen(300000);
SELECT 'Fahrzeuge hat: ',  count(*), ' Zeile' FROM fahrzeuge;
EXEC fahrzeuge11g_gen(300000);
SELECT 'Fahrzeuge11g hat: ',  count(*), ' Zeile' FROM fahrzeuge11g;

/* -----------------------------------------------------------------------------
-- Statistiken erstellen und verifizieren
-- -----------------------------------------------------------------------------
*/
SELECT '--> Statistiken erstellen' Aktion FROM dual;
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('PART', DEGREE=>4);

SELECT table_name
,      blocks
FROM user_tables
WHERE table_name LIKE 'FAHRZEUGE%';

SELECT ROUND((SELECT value FROM v$system_parameter WHERE name = 'db_block_size') *
             (SELECT blocks FROM user_tables WHERE table_name = 'FAHRZEUGE')  /
             (1024*1024*1024)
            ) Gigabytes
FROM dual;

SPOOL OFF;

SPOOL test1.log
EXPLAIN PLAN FOR
SELECT fgstnr
FROM  fahrzeuge
WHERE modelljahr = '2009'
AND   land_kunde = 'DEU'
AND   marke      = 'VW';

SELECT * FROM TABLE(dbms_xplan.display());

EXPLAIN PLAN FOR
SELECT fgstnr
FROM  fahrzeuge
WHERE land_kunde = 'DEU'
AND   marke      = 'VW';

SELECT * FROM TABLE(dbms_xplan.display());

SPOOL OFF;

SPOOL test2.log
EXPLAIN PLAN FOR
SELECT fgstnr
FROM  fahrzeuge11g
WHERE modelljahr = '2009'
AND   land_kunde = 'DEU'
AND   marke      = 'VW';

SELECT * FROM TABLE(dbms_xplan.display());

EXPLAIN PLAN FOR
SELECT fgstnr
FROM  fahrzeuge11g
WHERE land_kunde = 'DEU'
AND   marke      = 'VW';

SELECT * FROM TABLE(dbms_xplan.display());

SPOOL OFF;

SPOOL test3.log

DROP TABLE fahrzeuge;
CREATE TABLE fahrzeugesp
   ( fgstnr              NUMBER       NOT NULL
   , marke               VARCHAR2(10) NOT NULL
   , modell              VARCHAR2(8)  NOT NULL
   , motor               VARCHAR2(8)  NOT NULL
   , getriebe            VARCHAR2(6)  NOT NULL
   , anz_tueren          NUMBER
   , farbe               VARCHAR2(10)
   , zubehoer            VARCHAR2(2800)
   , auslieferung_kunde  VARCHAR2(6)
   , land_kunde          VARCHAR2(3)
   , prodmonat           VARCHAR2(6)  NOT NULL
   , modelljahr          VARCHAR2(4)  NOT NULL
   )
PARTITION BY SYSTEM
(
   PARTITION p1 TABLESPACE users,
   PARTITION p2 TABLESPACE users
);


SELECT partition_name
FROM  user_segments
WHERE segment_name = 'FAHRZEUGESP';

CREATE INDEX in_fahrzeugesp ON fahrzeugesp (marke) LOCAL;

SELECT partition_name
FROM  user_segments
WHERE segment_name = 'IN_FAHRZEUGESP';

SELECT partitioning_type
FROM  user_part_tables
WHERE table_name = 'FAHRZEUGESP';

SELECT partition_name, high_value
FROM  user_tab_partitions
WHERE table_name = 'FAHRZEUGE';

INSERT INTO fahrzeugesp
            PARTITION (p1) ( fgstnr
                           , marke
                           , modell
                           , motor
                           , getriebe
                           , anz_tueren
                           , farbe
                           , zubehoer
                           , auslieferung_kunde
                           , land_kunde
                           , prodmonat
                           , modelljahr
                           )
VALUES ( 1
       , 'BMW'
       , 'SUV'
       , '4,0l'
       , '6Gang'
       , '5'
       , 'rot'
       , NULL
       , '200802'
       , 'DEU'
       , '200801'
       , '2008'
       );

INSERT INTO fahrzeugesp
            PARTITION (p1) ( fgstnr
                           , marke
                           , modell
                           , motor
                           , getriebe
                           , anz_tueren
                           , farbe
                           , zubehoer
                           , auslieferung_kunde
                           , land_kunde
                           , prodmonat
                           , modelljahr
                           )
VALUES ( 1
       , 'BMW'
       , 'SUV'
       , '4,0l'
       , '6Gang'
       , '5'
       , 'rot'
       , NULL
       , '200802'
       , 'DEU'
       , '200801'
       , '2008'
       );
 
 
-- DELETE fahrzeugesp WHERE fgstnr = 1;

DELETE fahrzeugesp PARTITION (p1) WHERE fgstnr = 1;

SPOOL OFF;