Oracle Live SQL - Script: DHBW Datenbanken Unit 1 - 15 - Tuning, Index, Statistics, Explain Plan

Statement 1

Create Plan Table:

CREATE TABLE PLAN_TABLE (  
    STATEMENT_ID       VARCHAR2(30),  
    PLAN_ID            NUMBER,  
    TIMESTAMP          DATE,  
    REMARKS            VARCHAR2(4000),  
    OPERATION          VARCHAR2(30),  
    OPTIONS            VARCHAR2(255),  
    OBJECT_NODE        VARCHAR2(128),  
    OBJECT_OWNER       VARCHAR2(30),  
    OBJECT_NAME        VARCHAR2(30),  
    OBJECT_ALIAS       VARCHAR2(65),  
    OBJECT_INSTANCE    NUMERIC,  
    OBJECT_TYPE        VARCHAR2(30),  
    OPTIMIZER          VARCHAR2(255),  
    SEARCH_COLUMNS     NUMBER,  
    ID                 NUMERIC,  
    PARENT_ID          NUMERIC,  
    DEPTH              NUMERIC,  
    POSITION           NUMERIC,  
    COST               NUMERIC,  
    CARDINALITY        NUMERIC,  
    BYTES              NUMERIC,  
    OTHER_TAG          VARCHAR2(255),  
    PARTITION_START    VARCHAR2(255),  
    PARTITION_STOP     VARCHAR2(255),  
    PARTITION_ID       NUMERIC,  
    OTHER              LONG,  
    DISTRIBUTION       VARCHAR2(30),  
    CPU_COST           NUMERIC,  
    IO_COST            NUMERIC,  
    TEMP_SPACE         NUMERIC,  
    ACCESS_PREDICATES  VARCHAR2(4000),  
    FILTER_PREDICATES  VARCHAR2(4000),  
    PROJECTION         VARCHAR2(4000),  
    TIME               NUMERIC,  
    QBLOCK_NAME        VARCHAR2(30),  
    OTHER_XML          CLOB  
)

Table created.

Statement 2

Create Plan View:

CREATE OR REPLACE VIEW PLAN_VIEW  
AS  
SELECT lpad(' ', (LEVEL-1)*2, ' ')  
       || decode((SELECT COUNT(*) FROM plan_table t WHERE t.parent_id = p.parent_id   
                  CONNECT BY PRIOR t.ID = t.parent_id START WITH ID = 0), 1,'*', '+')  
       ||'-'||p.operation ||' '||p.options  
       ||decode(p.OPTIMIZER, NULL,NULL, ' (' || p.OPTIMIZER ||')') AS "Description",       
       decode(p.OBJECT_OWNER, NULL, NULL, p.OBJECT_OWNER ||'.')  || p.OBJECT_NAME AS OBJECT,  
       p.OBJECT_TYPE, p.COST, p.POSITION,  p.CARDINALITY AS "ROWS", p.BYTES,  
       CASE WHEN p.OBJECT_TYPE IN ('INDEX', 'INDEX (BITMAP)')  
            THEN   
                (SELECT i.column_name FROM ALL_IND_COLUMNS i WHERE i.column_position = 1 AND i.INDEX_OWNER = p.OBJECT_OWNER AND i.INDEX_NAME = p.OBJECT_NAME )  
                ||(SELECT ', ' || i.column_name FROM ALL_IND_COLUMNS i WHERE i.column_position = 2 AND i.INDEX_OWNER = p.OBJECT_OWNER AND i.INDEX_NAME = p.OBJECT_NAME )  
                ||(SELECT ', ' || i.column_name FROM ALL_IND_COLUMNS i WHERE i.column_position = 3 AND i.INDEX_OWNER = p.OBJECT_OWNER AND i.INDEX_NAME = p.OBJECT_NAME )  
                ||(SELECT ', ' || i.column_name FROM ALL_IND_COLUMNS i WHERE i.column_position = 4 AND i.INDEX_OWNER = p.OBJECT_OWNER AND i.INDEX_NAME = p.OBJECT_NAME )  
                ||(SELECT ', ' || i.column_name FROM ALL_IND_COLUMNS i WHERE i.column_position = 5 AND i.INDEX_OWNER = p.OBJECT_OWNER AND i.INDEX_NAME = p.OBJECT_NAME )  
                ||(SELECT ', ...!'  FROM ALL_IND_COLUMNS i WHERE i.column_position = 6 AND i.INDEX_OWNER = p.OBJECT_OWNER AND i.INDEX_NAME = p.OBJECT_NAME )  
            ELSE NULL  
            END AS "INDEX COLUMNS"
       /* , partition_start, partition_stop, partition_id, other_tag  */
   FROM plan_table p  
CONNECT BY PRIOR ID = parent_id START WITH ID = 0

View created.

Statement 3

Create Table PERSON:

CREATE TABLE PERSON( 
    ID      NUMBER, 
    NAME    VARCHAR2(255), 
    SURNAME VARCHAR2(255), 
    CITY    VARCHAR2(255), 
    COUNTRY VARCHAR2(255) 
)

Table created.

Statement 4

WITH Clause and CROSS JOINS used to create many rows

Insert Data into Table PERSON:

INSERT INTO PERSON p 
(p.ID, p.NAME, p.SURNAME, p.CITY, p.COUNTRY) 
WITH 
surnames AS 
( 
    SELECT 'Alan' AS SURNAME, 'm' AS SEX FROM DUAL UNION ALL 
    SELECT 'Brad' AS SURNAME, 'm' AS SEX FROM DUAL UNION ALL 
    SELECT 'Dave' AS SURNAME, 'm' AS SEX FROM DUAL UNION ALL 
    SELECT 'Eric' AS SURNAME, 'm' AS SEX FROM DUAL UNION ALL 
    SELECT 'Frank' AS SURNAME, 'm' AS SEX FROM DUAL UNION ALL 
    SELECT 'George' AS SURNAME, 'm' AS SEX FROM DUAL UNION ALL 
    SELECT 'Mike' AS SURNAME, 'm' AS SEX FROM DUAL UNION ALL 
    SELECT 'Oliver' AS SURNAME, 'm' AS SEX FROM DUAL UNION ALL 
    SELECT 'Paul' AS SURNAME, 'm' AS SEX FROM DUAL UNION ALL 
    SELECT 'Steve' AS SURNAME, 'm' AS SEX FROM DUAL UNION ALL 
    SELECT 'Tim' AS SURNAME, 'm' AS SEX FROM DUAL UNION ALL 
    SELECT 'Victor' AS SURNAME, 'm' AS SEX FROM DUAL UNION ALL 
    SELECT 'Anna' AS SURNAME, 'f' AS SEX FROM DUAL UNION ALL 
    SELECT 'Berta' AS SURNAME, 'f' AS SEX FROM DUAL UNION ALL 
    SELECT 'Celia' AS SURNAME, 'f' AS SEX FROM DUAL UNION ALL 
    SELECT 'Erin' AS SURNAME, 'f' AS SEX FROM DUAL UNION ALL 
    SELECT 'Fiona' AS SURNAME, 'f' AS SEX FROM DUAL UNION ALL 
    SELECT 'Gina' AS SURNAME, 'f' AS SEX FROM DUAL UNION ALL 
    SELECT 'Jessica' AS SURNAME, 'f' AS SEX FROM DUAL UNION ALL 
    SELECT 'Maria' AS SURNAME, 'f' AS SEX FROM DUAL UNION ALL 
    SELECT 'Nina' AS SURNAME, 'f' AS SEX FROM DUAL UNION ALL 
    SELECT 'Olivia' AS SURNAME, 'f' AS SEX FROM DUAL UNION ALL 
    SELECT 'Paula' AS SURNAME, 'f' AS SEX FROM DUAL UNION ALL 
    SELECT 'Rita' AS SURNAME, 'f' AS SEX FROM DUAL UNION ALL 
    SELECT 'Summer' AS SURNAME, 'f' AS SEX FROM DUAL 
), 
names AS 
( 
    SELECT 'Anderson' AS NAME FROM DUAL UNION ALL 
    SELECT 'Brown' AS NAME FROM DUAL UNION ALL 
    SELECT 'Carpenter' AS NAME FROM DUAL UNION ALL 
    SELECT 'Cook' AS NAME FROM DUAL UNION ALL 
    SELECT 'Duke' AS NAME FROM DUAL UNION ALL 
    SELECT 'Ericson' AS NAME FROM DUAL UNION ALL 
    SELECT 'Fellow' AS NAME FROM DUAL UNION ALL 
    SELECT 'Grand' AS NAME FROM DUAL UNION ALL 
    SELECT 'Hawkins' AS NAME FROM DUAL UNION ALL 
    SELECT 'Hanson' AS NAME FROM DUAL UNION ALL 
    SELECT 'Hilton' AS NAME FROM DUAL UNION ALL 
    SELECT 'Johnson' AS NAME FROM DUAL UNION ALL 
    SELECT 'King' AS NAME FROM DUAL UNION ALL 
    SELECT 'Moore' AS NAME FROM DUAL UNION ALL 
    SELECT 'Miller' AS NAME FROM DUAL UNION ALL 
    SELECT 'McQueen' AS NAME FROM DUAL UNION ALL 
    SELECT 'Norris' AS NAME FROM DUAL UNION ALL 
    SELECT 'Rodriguez' AS NAME FROM DUAL UNION ALL 
    SELECT 'Smith' AS NAME FROM DUAL UNION ALL 
    SELECT 'Timberlake' AS NAME FROM DUAL UNION ALL 
    SELECT 'Wayne' AS NAME FROM DUAL UNION ALL 
    SELECT 'Young' AS NAME FROM DUAL  
), 
cities AS 
( 
    SELECT 'New York' AS CITY, 'USA' AS COUNTRY FROM DUAL UNION ALL 
    SELECT 'Dallas' AS CITY, 'USA' AS COUNTRY FROM DUAL UNION ALL 
    SELECT 'Houston' AS CITY, 'USA' AS COUNTRY FROM DUAL UNION ALL 
    SELECT 'Chicago' AS CITY, 'USA' AS COUNTRY FROM DUAL UNION ALL 
    SELECT 'Washington D.C.' AS CITY, 'USA' AS COUNTRY FROM DUAL UNION ALL 
    SELECT 'London' AS CITY, 'UK' AS COUNTRY FROM DUAL UNION ALL 
    SELECT 'Birmingham' AS CITY, 'UK' AS COUNTRY FROM DUAL UNION ALL 
    SELECT 'Glasgow' AS CITY, 'UK' AS COUNTRY FROM DUAL UNION ALL 
    SELECT 'Dublin' AS CITY, 'Ireland' AS COUNTRY FROM DUAL 
) 
    SELECT ROWNUM AS ID, n.NAME, s.SURNAME, c.CITY, c.COUNTRY 
      FROM surnames s 
CROSS JOIN names n 
CROSS JOIN cities c

4950 row(s) inserted.

Statement 5

COMMIT

Statement processed.

Statement 6

Statistics are very important for the Oracle Optimizer to create optimal SQL execution plans

Gather table statistics:

BEGIN 
    dbms_stats.gather_table_stats(null, 'PERSON'); 
END;

Statement processed.

Statement 7

SELECT * 
  FROM PERSON p
 WHERE p.NAME = 'King'
   AND p.SURNAME = 'Fiona'
   AND p.CITY = 'New York'


ID NAME SURNAME CITY COUNTRY
317 King Fiona New York USA

ID	NAME	SURNAME	CITY	COUNTRY
317	King	Fiona	New York	USA

Statement 8

DELETE FROM PLAN_TABLE

0 row(s) deleted.

Statement 9

Explain SQL Execution Plan:

EXPLAIN PLAN FOR 
SELECT *  
  FROM PERSON p 
 WHERE p.NAME = 'King' 
   AND p.SURNAME = 'Fiona' 
   AND p.CITY = 'New York'

Statement processed.

Statement 10

Show SQL Execution Plan:

SELECT * FROM PLAN_VIEW


Description OBJECT OBJECT_TYPE COST POSITION ROWS BYTES INDEX COLUMNS PARTITION_START PARTITION_STOP PARTITION_ID OTHER_TAG
+-SELECT STATEMENT  (ALL_ROWS)  -  - 9 9 1 30  -  -  -  -  - 
  *-TABLE ACCESS FULL (ANALYZED) SQL_NDKFNFMJJDOPQHIKRTOFCNAKH.PERSON TABLE 9 1 1 30  -  -  -  -  - 

2 rows selected.

Description	OBJECT	OBJECT_TYPE	COST	POSITION	ROWS	BYTES	INDEX COLUMNS	PARTITION_START	PARTITION_STOP	PARTITION_ID	OTHER_TAG
+-SELECT STATEMENT (ALL_ROWS)	-	-	9	9	1	30	-	-	-	-	-
*-TABLE ACCESS FULL (ANALYZED)	SQL_NDKFNFMJJDOPQHIKRTOFCNAKH.PERSON	TABLE	9	1	1	30	-	-	-	-	-

Statement 11

Create Indexes:

CREATE INDEX PERSON_NAME_IDX 
  ON PERSON (NAME)

Index created.

Statement 12

CREATE INDEX PERSON_CITY_IDX
  ON PERSON (CITY, NAME, SURNAME)

Index created.

Statement 13

CREATE INDEX PERSON_COUNTRY_IDX
  ON PERSON (COUNTRY, CITY)

Index created.

Statement 14

Gather table statistics:

BEGIN 
    dbms_stats.gather_table_stats(null, 'PERSON'); 
END;

Statement processed.

Statement 15

SELECT * 
  FROM PERSON p
 WHERE p.NAME = 'King'
   AND p.SURNAME = 'Fiona'
   AND p.CITY = 'New York'


ID NAME SURNAME CITY COUNTRY
317 King Fiona New York USA

ID	NAME	SURNAME	CITY	COUNTRY
317	King	Fiona	New York	USA

Statement 16

DELETE FROM PLAN_TABLE

2 row(s) deleted.

Statement 17

Explain SQL Execution Plan:

EXPLAIN PLAN FOR 
SELECT *  
  FROM PERSON p 
 WHERE p.NAME = 'King' 
   AND p.SURNAME = 'Fiona' 
   AND p.CITY = 'New York'

Statement processed.

Statement 18

Show SQL Execution Plan:

SELECT * FROM PLAN_VIEW


Description OBJECT OBJECT_TYPE COST POSITION ROWS BYTES INDEX COLUMNS PARTITION_START PARTITION_STOP PARTITION_ID OTHER_TAG
+-SELECT STATEMENT  (ALL_ROWS)  -  - 2 2 1 30  -  -  -  -  - 
  *-TABLE ACCESS BY INDEX ROWID BATCHED (ANALYZED) SQL_NDKFNFMJJDOPQHIKRTOFCNAKH.PERSON TABLE 2 1 1 30  -  -  -  -  - 
    *-INDEX RANGE SCAN (ANALYZED) SQL_NDKFNFMJJDOPQHIKRTOFCNAKH.PERSON_CITY_IDX INDEX 1 1 1  - CITY, NAME, SURNAME  -  -  -  - 

3 rows selected.

Description	OBJECT	OBJECT_TYPE	COST	POSITION	ROWS	BYTES	INDEX COLUMNS	PARTITION_START	PARTITION_STOP	PARTITION_ID	OTHER_TAG
+-SELECT STATEMENT (ALL_ROWS)	-	-	2	2	1	30	-	-	-	-	-
*-TABLE ACCESS BY INDEX ROWID BATCHED (ANALYZED)	SQL_NDKFNFMJJDOPQHIKRTOFCNAKH.PERSON	TABLE	2	1	1	30	-	-	-	-	-
*-INDEX RANGE SCAN (ANALYZED)	SQL_NDKFNFMJJDOPQHIKRTOFCNAKH.PERSON_CITY_IDX	INDEX	1	1	1	-	CITY, NAME, SURNAME	-	-	-	-

Statement 19

CREATE TABLE SURVEY( 
    ID              NUMBER PRIMARY KEY,     
    SEX             VARCHAR2(1) CHECK (SEX IN ('m', 'f')),  
    AGE_GROUP       NUMBER(2)   CHECK (AGE_GROUP IN (0, 10, 20, 30, 40, 50, 60)),  
    CITIZEN         VARCHAR2(5) CHECK (CITIZEN IN ('EU', 'US', 'other')), 
    DRIVER_LICENCE  VARCHAR2(3) CHECK (DRIVER_LICENCE IN ('yes', 'no')),  
    SMOKER          NUMBER(1)   CHECK (SMOKER IN (0, 1)) 
)

Table created.

Statement 20

Create B-Tree Indexes:

CREATE INDEX SURVEY_SEX_IDX ON SURVEY (SEX)

Index created.

Statement 21

CREATE INDEX SURVEY_CITIZEN_IDX ON SURVEY (CITIZEN)

Index created.

Statement 22

CREATE INDEX SURVEY_DRIVER_LICENCE_IDX ON SURVEY (DRIVER_LICENCE)

Index created.

Statement 23

CREATE INDEX SURVEY_SMOKER_IDX ON SURVEY (SMOKER)

Index created.

Statement 24

WITH Clause and CROSS JOINS used to create many rows

INSERT INTO SURVEY t 
(t.ID, t.SEX, t.AGE_GROUP, t.CITIZEN, t.DRIVER_LICENCE, t.SMOKER) 
WITH 
surnames AS 
( 
    SELECT 'Alan' AS SURNAME, 'm' AS SEX FROM DUAL UNION ALL 
    SELECT 'Dave' AS SURNAME, 'm' AS SEX FROM DUAL UNION ALL 
    SELECT 'Frank' AS SURNAME, 'm' AS SEX FROM DUAL UNION ALL 
    SELECT 'Mike' AS SURNAME, 'm' AS SEX FROM DUAL UNION ALL 
    SELECT 'Paul' AS SURNAME, 'm' AS SEX FROM DUAL UNION ALL 
    SELECT 'Steve' AS SURNAME, 'm' AS SEX FROM DUAL UNION ALL 
    SELECT 'Anna' AS SURNAME, 'f' AS SEX FROM DUAL UNION ALL 
    SELECT 'Fiona' AS SURNAME, 'f' AS SEX FROM DUAL UNION ALL 
    SELECT 'Gina' AS SURNAME, 'f' AS SEX FROM DUAL UNION ALL 
    SELECT 'Nina' AS SURNAME, 'f' AS SEX FROM DUAL UNION ALL 
    SELECT 'Paula' AS SURNAME, 'f' AS SEX FROM DUAL UNION ALL 
    SELECT 'Rita' AS SURNAME, 'f' AS SEX FROM DUAL 
), 
names AS 
( 
    SELECT 'Anderson' AS NAME FROM DUAL UNION ALL 
    SELECT 'Brown' AS NAME FROM DUAL UNION ALL 
    SELECT 'Cook' AS NAME FROM DUAL UNION ALL 
    SELECT 'Duke' AS NAME FROM DUAL UNION ALL 
    SELECT 'Ericson' AS NAME FROM DUAL UNION ALL 
    SELECT 'Hanson' AS NAME FROM DUAL UNION ALL 
    SELECT 'Hilton' AS NAME FROM DUAL UNION ALL 
    SELECT 'Johnson' AS NAME FROM DUAL UNION ALL 
    SELECT 'King' AS NAME FROM DUAL UNION ALL 
    SELECT 'Moore' AS NAME FROM DUAL UNION ALL 
    SELECT 'Miller' AS NAME FROM DUAL UNION ALL 
    SELECT 'Rodriguez' AS NAME FROM DUAL UNION ALL 
    SELECT 'Smith' AS NAME FROM DUAL 
), 
ages AS 
( 
    SELECT 0 AS AGE_GROUP FROM DUAL UNION ALL 
    SELECT 10 AS AGE_GROUP FROM DUAL UNION ALL 
    SELECT 20 AS AGE_GROUP FROM DUAL UNION ALL 
    SELECT 30 AS AGE_GROUP FROM DUAL UNION ALL 
    SELECT 40 AS AGE_GROUP FROM DUAL UNION ALL 
    SELECT 50 AS AGE_GROUP FROM DUAL UNION ALL 
    SELECT 60 AS AGE_GROUP FROM DUAL UNION ALL 
    SELECT 60 AS AGE_GROUP FROM DUAL  
), 
citizens AS 
( 
    SELECT 'US' AS CITIZEN FROM DUAL UNION ALL 
    SELECT 'EU' AS CITIZEN FROM DUAL UNION ALL 
    SELECT 'other' AS CITIZEN FROM DUAL  
), 
driverlicences AS 
( 
    SELECT 'yes' AS DRIVER_LICENCE FROM DUAL UNION ALL 
    SELECT 'no' AS DRIVER_LICENCE FROM DUAL  
), 
smokers AS 
( 
    SELECT 1 AS SMOKER FROM DUAL UNION ALL 
    SELECT 0 AS SMOKER FROM DUAL UNION ALL 
    SELECT 0 AS SMOKER FROM DUAL UNION ALL 
    SELECT 0 AS SMOKER FROM DUAL  
) 
    SELECT rownum AS ID, s.SEX, a.AGE_GROUP, c.CITIZEN, d.DRIVER_LICENCE, sm.SMOKER 
      FROM surnames s 
CROSS JOIN names n 
CROSS JOIN ages a 
CROSS JOIN citizens c  
CROSS JOIN driverlicences d 
CROSS JOIN smokers sm

29952 row(s) inserted.

Statement 25

COMMIT

Statement processed.

Statement 26

Gather table statistics:

BEGIN 
    dbms_stats.gather_table_stats(null, 'SURVEY'); 
END;

Statement processed.

Statement 27

SELECT count(*)
  FROM SURVEY s
 WHERE s.SEX = 'm'
   AND s.AGE_GROUP = '30'
   AND s.CITIZEN = 'US'
   AND s.DRIVER_LICENCE = 'yes'
   AND s.SMOKER = 1


COUNT(*)
78

COUNT(*)
78

Statement 28

DELETE FROM PLAN_TABLE

3 row(s) deleted.

Statement 29

Explain SQL Execution Plan:

EXPLAIN PLAN FOR 
SELECT count(*) 
  FROM SURVEY s 
 WHERE s.SEX = 'm' 
   AND s.AGE_GROUP = '30' 
   AND s.CITIZEN = 'US' 
   AND s.DRIVER_LICENCE = 'yes' 
   AND s.SMOKER = 1

Statement processed.

Statement 30

No Index is used ("TABLE ACCESS FULL")! The full table gets read!

Show SQL Execution Plan:

SELECT * FROM PLAN_VIEW


Description OBJECT OBJECT_TYPE COST POSITION ROWS BYTES INDEX COLUMNS PARTITION_START PARTITION_STOP PARTITION_ID OTHER_TAG
+-SELECT STATEMENT  (ALL_ROWS)  -  - 30 30 1 16  -  -  -  -  - 
  *-SORT AGGREGATE  -  -  - 1 1 16  -  -  -  -  - 
    *-TABLE ACCESS FULL (ANALYZED) SQL_NDKFNFMJJDOPQHIKRTOFCNAKH.SURVEY TABLE 30 1 178 2848  -  -  -  -  - 

3 rows selected.

Description	OBJECT	OBJECT_TYPE	COST	POSITION	ROWS	BYTES	INDEX COLUMNS	PARTITION_START	PARTITION_STOP	PARTITION_ID	OTHER_TAG
+-SELECT STATEMENT (ALL_ROWS)	-	-	30	30	1	16	-	-	-	-	-
*-SORT AGGREGATE	-	-	-	1	1	16	-	-	-	-	-
*-TABLE ACCESS FULL (ANALYZED)	SQL_NDKFNFMJJDOPQHIKRTOFCNAKH.SURVEY	TABLE	30	1	178	2848	-	-	-	-	-

Statement 31

There is no index that reduces the total amount of rows significantly. Therefore no index is used!

Why? Let's have a look at the table rows (total and for each filter):

SELECT 
    (SELECT count(*) FROM SURVEY s) AS "total rows",
    (SELECT count(*) FROM SURVEY s WHERE s.SEX = 'm') AS "SEX = m",
    (SELECT count(*) FROM SURVEY s WHERE s.CITIZEN = 'US') AS "CITIZEN = US",
    (SELECT count(*) FROM SURVEY s WHERE s.DRIVER_LICENCE = 'yes') AS "DRIVER_LICENCE = yes",
    (SELECT count(*) FROM SURVEY s WHERE s.SMOKER = 1) AS "SMOKER = 1"
 FROM DUAL


total rows SEX = m CITIZEN = US DRIVER_LICENCE = yes SMOKER = 1
29952 14976 9984 14976 7488

total rows	SEX = m	CITIZEN = US	DRIVER_LICENCE = yes	SMOKER = 1
29952	14976	9984	14976	7488

Statement 32

Drop B-Tree Indexes:

DROP INDEX SURVEY_SEX_IDX

Index dropped.

Statement 33

DROP INDEX SURVEY_CITIZEN_IDX

Index dropped.

Statement 34

DROP INDEX SURVEY_DRIVER_LICENCE_IDX

Index dropped.

Statement 35

DROP INDEX SURVEY_SMOKER_IDX

Index dropped.

Statement 36

Create Bitmap Indexes:

CREATE BITMAP INDEX SURVEY_SEX_BIDX ON SURVEY (SEX)

Index created.

Statement 37

CREATE BITMAP INDEX SURVEY_AGE_GROUP_BIDX ON SURVEY (AGE_GROUP)

Index created.

Statement 38

CREATE BITMAP INDEX SURVEY_CITIZEN_BIDX ON SURVEY (CITIZEN)

Index created.

Statement 39

CREATE BITMAP INDEX SURVEY_DRIVER_LICENCE_BIDX ON SURVEY (DRIVER_LICENCE)

Index created.

Statement 40

CREATE BITMAP INDEX SURVEY_SMOKER_BIDX ON SURVEY (SMOKER)

Index created.

Statement 41

Gather table statistics:

BEGIN 
    dbms_stats.gather_table_stats(null, 'SURVEY'); 
END;

Statement processed.

Statement 42

SELECT count(*)
  FROM SURVEY s
 WHERE s.SEX = 'm'
   AND s.AGE_GROUP = '30'
   AND s.CITIZEN = 'US'
   AND s.DRIVER_LICENCE = 'yes'
   AND s.SMOKER = 1


COUNT(*)
78

COUNT(*)
78

Statement 43

DELETE FROM PLAN_TABLE

3 row(s) deleted.

Statement 44

Explain SQL Execution Plan:

EXPLAIN PLAN FOR 
SELECT count(*) 
  FROM SURVEY s 
 WHERE s.SEX = 'm' 
   AND s.AGE_GROUP = '30' 
   AND s.CITIZEN = 'US' 
   AND s.DRIVER_LICENCE = 'yes' 
   AND s.SMOKER = 1

Statement processed.

Statement 45

Show SQL Execution Plan:

SELECT * FROM PLAN_VIEW


Description OBJECT OBJECT_TYPE COST POSITION ROWS BYTES INDEX COLUMNS PARTITION_START PARTITION_STOP PARTITION_ID OTHER_TAG
+-SELECT STATEMENT  (ALL_ROWS)  -  - 5 5 1 16  -  -  -  -  - 
  *-SORT AGGREGATE  -  -  - 1 1 16  -  -  -  -  - 
    *-BITMAP CONVERSION COUNT  -  - 5 1 78 1248  -  -  -  -  - 
      *-BITMAP AND  -  -  - 1  -  -  -  -  -  -  - 
        +-BITMAP INDEX SINGLE VALUE SQL_NDKFNFMJJDOPQHIKRTOFCNAKH.SURVEY_AGE_GROUP_BIDX INDEX (BITMAP)  - 1  -  - AGE_GROUP  -  -  -  - 
        +-BITMAP INDEX SINGLE VALUE SQL_NDKFNFMJJDOPQHIKRTOFCNAKH.SURVEY_SMOKER_BIDX INDEX (BITMAP)  - 2  -  - SMOKER  -  -  -  - 
        +-BITMAP INDEX SINGLE VALUE SQL_NDKFNFMJJDOPQHIKRTOFCNAKH.SURVEY_CITIZEN_BIDX INDEX (BITMAP)  - 3  -  - CITIZEN  -  -  -  - 
        +-BITMAP INDEX SINGLE VALUE SQL_NDKFNFMJJDOPQHIKRTOFCNAKH.SURVEY_DRIVER_LICENCE_BIDX INDEX (BITMAP)  - 4  -  - DRIVER_LICENCE  -  -  -  - 
        +-BITMAP INDEX SINGLE VALUE SQL_NDKFNFMJJDOPQHIKRTOFCNAKH.SURVEY_SEX_BIDX INDEX (BITMAP)  - 5  -  - SEX  -  -  -  - 

9 rows selected.

Description	OBJECT	OBJECT_TYPE	COST	POSITION	ROWS	BYTES	INDEX COLUMNS	PARTITION_START	PARTITION_STOP	PARTITION_ID	OTHER_TAG
+-SELECT STATEMENT (ALL_ROWS)	-	-	5	5	1	16	-	-	-	-	-
*-SORT AGGREGATE	-	-	-	1	1	16	-	-	-	-	-
*-BITMAP CONVERSION COUNT	-	-	5	1	78	1248	-	-	-	-	-
*-BITMAP AND	-	-	-	1	-	-	-	-	-	-	-
+-BITMAP INDEX SINGLE VALUE	SQL_NDKFNFMJJDOPQHIKRTOFCNAKH.SURVEY_AGE_GROUP_BIDX	INDEX (BITMAP)	-	1	-	-	AGE_GROUP	-	-	-	-
+-BITMAP INDEX SINGLE VALUE	SQL_NDKFNFMJJDOPQHIKRTOFCNAKH.SURVEY_SMOKER_BIDX	INDEX (BITMAP)	-	2	-	-	SMOKER	-	-	-	-
+-BITMAP INDEX SINGLE VALUE	SQL_NDKFNFMJJDOPQHIKRTOFCNAKH.SURVEY_CITIZEN_BIDX	INDEX (BITMAP)	-	3	-	-	CITIZEN	-	-	-	-
+-BITMAP INDEX SINGLE VALUE	SQL_NDKFNFMJJDOPQHIKRTOFCNAKH.SURVEY_DRIVER_LICENCE_BIDX	INDEX (BITMAP)	-	4	-	-	DRIVER_LICENCE	-	-	-	-
+-BITMAP INDEX SINGLE VALUE	SQL_NDKFNFMJJDOPQHIKRTOFCNAKH.SURVEY_SEX_BIDX	INDEX (BITMAP)	-	5	-	-	SEX	-	-	-	-

DHBW Datenbanken Unit 1 - 15 - Tuning, Index, Statistics, Explain Plan

Additional Information