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.
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.
Create Table PERSON:
CREATE TABLE PERSON(
ID NUMBER,
NAME VARCHAR2(255),
SURNAME VARCHAR2(255),
CITY VARCHAR2(255),
COUNTRY VARCHAR2(255)
)
Table created.
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.
COMMIT
Statement processed.
Gather table statistics:
BEGIN
dbms_stats.gather_table_stats(null, 'PERSON');
END;
Statement processed.
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 |
---|
DELETE FROM PLAN_TABLE
0 row(s) deleted.
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.
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 | - | - | - | - | - |
---|
Create Indexes:
CREATE INDEX PERSON_NAME_IDX
ON PERSON (NAME)
Index created.
CREATE INDEX PERSON_CITY_IDX
ON PERSON (CITY, NAME, SURNAME)
Index created.
CREATE INDEX PERSON_COUNTRY_IDX
ON PERSON (COUNTRY, CITY)
Index created.
Gather table statistics:
BEGIN
dbms_stats.gather_table_stats(null, 'PERSON');
END;
Statement processed.
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 |
---|
DELETE FROM PLAN_TABLE
2 row(s) deleted.
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.
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 | - | - | - | - |
---|
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.
Create B-Tree Indexes:
CREATE INDEX SURVEY_SEX_IDX ON SURVEY (SEX)
Index created.
CREATE INDEX SURVEY_CITIZEN_IDX ON SURVEY (CITIZEN)
Index created.
CREATE INDEX SURVEY_DRIVER_LICENCE_IDX ON SURVEY (DRIVER_LICENCE)
Index created.
CREATE INDEX SURVEY_SMOKER_IDX ON SURVEY (SMOKER)
Index created.
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.
COMMIT
Statement processed.
Gather table statistics:
BEGIN
dbms_stats.gather_table_stats(null, 'SURVEY');
END;
Statement processed.
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 |
---|
DELETE FROM PLAN_TABLE
3 row(s) deleted.
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.
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 | - | - | - | - | - |
---|
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 |
---|
Drop B-Tree Indexes:
DROP INDEX SURVEY_SEX_IDX
Index dropped.
DROP INDEX SURVEY_CITIZEN_IDX
Index dropped.
DROP INDEX SURVEY_DRIVER_LICENCE_IDX
Index dropped.
DROP INDEX SURVEY_SMOKER_IDX
Index dropped.
Create Bitmap Indexes:
CREATE BITMAP INDEX SURVEY_SEX_BIDX ON SURVEY (SEX)
Index created.
CREATE BITMAP INDEX SURVEY_AGE_GROUP_BIDX ON SURVEY (AGE_GROUP)
Index created.
CREATE BITMAP INDEX SURVEY_CITIZEN_BIDX ON SURVEY (CITIZEN)
Index created.
CREATE BITMAP INDEX SURVEY_DRIVER_LICENCE_BIDX ON SURVEY (DRIVER_LICENCE)
Index created.
CREATE BITMAP INDEX SURVEY_SMOKER_BIDX ON SURVEY (SMOKER)
Index created.
Gather table statistics:
BEGIN
dbms_stats.gather_table_stats(null, 'SURVEY');
END;
Statement processed.
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 |
---|
DELETE FROM PLAN_TABLE
3 row(s) deleted.
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.
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 | - | - | - | - |
---|