Oracle Performance Testing for Index using Cost

Result

Query

Cost
Query without criteria without index column 68
Query with criteria with index column 2

Insert

  Time
Insert without Indexes 0.008 Seconds
Insert with Indexes 0.194 Seconds

Conclusion: The index could help to increase performance on Query but reduce performance on Insert.

 

SQL

SQL of reducing Cost by creating index

-- CREATE DUMMY_TABLE1 FOR TESTING
CREATE TABLE DUMMY_TABLE1 (
 COL1 VARCHAR(100),
 COL2 VARCHAR(100),
 COL3 VARCHAR(100), 
 COL4 VARCHAR(100),
 COL5 VARCHAR(100),
 COL6 VARCHAR(100),
 COL7 VARCHAR(100),
 COL8 VARCHAR(100),
 COL9 VARCHAR(100),
 COL10 VARCHAR(100)
);

-- INSERT TESTING DATA INTO DUMMY_TABLE1
BEGIN
 FOR I IN 1..10001 LOOP
 INSERT INTO DUMMY_TABLE1(COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10) VALUES (I, 'COL1'||I, 'COL2'||I, 'COL3'||I,'COL4'||I,'COL5'||I,'COL6'||I,'COL7'||I,'COL8'||I,'COL9'||I );
 END LOOP;
END;
/

-- COST: 68 
-- OPTION: FULL
SELECT * FROM DUMMY_TABLE1 where col1 = '1';

-- CREATE PRIMARY KEY ON DUMMY_TABLE1
ALTER TABLE DUMMY_TABLE1 ADD CONSTRAINT PK_DUMMY_TABLE1 PRIMARY KEY (COL1);

-- COST: 2
-- OPTION: BY INDEX ROWID
SELECT * FROM DUMMY_TABLE1 where col1 = '1';

The more index you added to table, the slower performance of insert would be .

-- CREATE DUMMY TABLE FOR TESTING
CREATE TABLE DUMMY_TABLE1 (
  COL1 VARCHAR(100),
  COL2 VARCHAR(100),
  COL3 VARCHAR(100), 
  COL4 VARCHAR(100),
  COL5 VARCHAR(100),
  COL6 VARCHAR(100),
  COL7 VARCHAR(100),
  COL8 VARCHAR(100),
  COL9 VARCHAR(100),
  COL10 VARCHAR(100)
);

-- GENERATE DUMMY DATA
BEGIN
  FOR I IN 1..1000000
  LOOP
    INSERT INTO DUMMY_TABLE1 VALUES (I, I, I, I, I, I, I, I, I, I);
  END LOOP;
  COMMIT;
END; 
/ 

INSERT INTO DUMMY_TABLE1 VALUES (
'NEW DATA', 
'NEW DATA', 
'NEW DATA', 
'NEW DATA', 
'NEW DATA', 
'NEW DATA', 
'NEW DATA', 
'NEW DATA', 
'NEW DATA', 
'NEW DATA');
-- 0.008 Seconds

CREATE INDEX DUMMY_INDEX1 ON DUMMY_TABLE1 (COL1);
CREATE INDEX DUMMY_INDEX2 ON DUMMY_TABLE1 (COL2);
CREATE INDEX DUMMY_INDEX3 ON DUMMY_TABLE1 (COL3);
CREATE INDEX DUMMY_INDEX4 ON DUMMY_TABLE1 (COL4);
CREATE INDEX DUMMY_INDEX5 ON DUMMY_TABLE1 (COL5);
CREATE INDEX DUMMY_INDEX6 ON DUMMY_TABLE1 (COL6);
CREATE INDEX DUMMY_INDEX7 ON DUMMY_TABLE1 (COL7);
CREATE INDEX DUMMY_INDEX8 ON DUMMY_TABLE1 (COL8);
CREATE INDEX DUMMY_INDEX9 ON DUMMY_TABLE1 (COL9);
CREATE INDEX DUMMY_INDEX10 ON DUMMY_TABLE1 (COL10);

INSERT INTO DUMMY_TABLE1 VALUES (
'NEW DATA', 
'NEW DATA', 
'NEW DATA', 
'NEW DATA', 
'NEW DATA', 
'NEW DATA', 
'NEW DATA', 
'NEW DATA', 
'NEW DATA', 
'NEW DATA'); 
-- TIME USED: 0.194

-- DROP TABLE
DROP TABLE DUMMY_TABLE1;

 

 

 

 

Oracle Performance Testing for Order by Index Using Cost

Result

  Cost
Not Include Order By 68
Order by Index column is Varchar2 1158
Order by Index column is Int 158
Order by not Index column (Varchar2) 1201
Order by not Index column (Int) 1201

The order by clause would increase the cost. If the index column is Int would reduce cost compared to other condition.

 

SQL

The index column is Varchar2

-- CREATE DUMMY_TABLE1 FOR TESTING
CREATE TABLE DUMMY_TABLE1 (
 COL1 VARCHAR(100),
 COL2 VARCHAR(100),
 COL3 VARCHAR(100), 
 COL4 VARCHAR(100),
 COL5 VARCHAR(100),
 COL6 VARCHAR(100),
 COL7 VARCHAR(100),
 COL8 VARCHAR(100),
 COL9 VARCHAR(100),
 COL10 VARCHAR(100)
);

-- INSERT TESTING DATA INTO DUMMY_TABLE1
BEGIN
 FOR I IN 1..10000 LOOP
 INSERT INTO DUMMY_TABLE1(COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10) VALUES (I, 'COL1'||I, 'COL2'||I, 'COL3'||I,'COL4'||I,'COL5'||I,'COL6'||I,'COL7'||I,'COL8'||I,'COL9'||I );
 END LOOP;
END;
/

-- CREATE PRIMARY KEY ON DUMMY_TABLE1
ALTER TABLE DUMMY_TABLE1 ADD CONSTRAINT PK_DUMMY_TABLE1 PRIMARY KEY (COL1);

-- COST: 68 
-- OPTION: FULL
SELECT * FROM DUMMY_TABLE1;

-- COST: 1158
SELECT * FROM DUMMY_TABLE1 ORDER BY COL1;

The index column is integer

-- CREATE DUMMY_TABLE1 FOR TESTING
CREATE TABLE DUMMY_TABLE2 (
 ID INT,
 COL1 VARCHAR(100),
 COL2 INT,
 COL3 VARCHAR(100), 
 COL4 VARCHAR(100),
 COL5 VARCHAR(100),
 COL6 VARCHAR(100),
 COL7 VARCHAR(100),
 COL8 VARCHAR(100),
 COL9 VARCHAR(100),
 COL10 VARCHAR(100)
);

-- INSERT TESTING DATA INTO DUMMY_TABLE2
BEGIN
 FOR I IN 1..10000 LOOP
 INSERT INTO DUMMY_TABLE2(ID, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10) VALUES (I, I, I, 'COL2'||I, 'COL3'||I,'COL4'||I,'COL5'||I,'COL6'||I,'COL7'||I,'COL8'||I,'COL9'||I );
 END LOOP;
END;

-- CREATE PRIMARY KEY ON DUMMY_TABLE2
ALTER TABLE DUMMY_TABLE2 ADD CONSTRAINT PK_DUMMY_TABLE2 PRIMARY KEY (ID);

-- COST: 69
SELECT * FROM DUMMY_TABLE2;

-- COST 151
-- OPTION: BY INDEX ROWID
SELECT * FROM DUMMY_TABLE2 ORDER BY ID;

-- COST: 1201
SELECT * FROM DUMMY_TABLE2 ORDER BY COL1;

-- COST: 1201
SELECT * FROM DUMMY_TABLE2 ORDER BY COL2;

 

 

 

 

 

 

 

 

 

SQLs for Tables

The below is the tables storing meta data of table and relationship information.

  User Tables All Table
Table List USER_TABLES ALL_TABLES
Columns of Tables USER_TAB_COLS ALL_TAB_COLS
Constraint List USER_CONSTRAINTS ALL_CONSTRAINTS
Columns of Constraint USER_CONS_COLUMNS ALL_CONS_COLUMNS
Index List USER_INDEXES ALL_INDEXES
Columns of Index USER_IND_COLUMNS ALL_IND_COLUMNS

 

Query Table List

SELECT TABLE_NAME FROM USER_TABLES;
SELECT * FROM USER_TABLES

Query Columns of a specialized table

DEFINE TABLE_NAME = '<TABLE_NAME>';
SELECT COLUMN_NAME FROM USER_TAB_COLS WHERE TABLE_NAME = '&TABLE_NAME';
DEFINE TABLE_NAME = '<TABLE_NAME>';
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, DATA_DEFAULT FROM USER_TAB_COLS WHERE TABLE_NAME = '&TABLE_NAME';
DEFINE TABLE_NAME = '<TABLE_NAME>';
SELECT * FROM USER_TAB_COLS WHERE TABLE_NAME = '&TABLE_NAME';

Query Constraints

SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS;

Primary Key

DEFINE TABLE_NAME = '<TABLE_NAME>';
SELECT B.COLUMN_NAME FROM USER_CONSTRAINTS A
LEFT JOIN USER_CONS_COLUMNS B
ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
WHERE A.CONSTRAINT_TYPE = 'P'
AND A.TABLE_NAME = '&TABLE_NAME'
;
DEFINE TABLE_NAME = '<TABLE_NAME>';
SELECT B.TABLE_NAME, B.COLUMN_NAME FROM USER_CONSTRAINTS A
LEFT JOIN USER_CONS_COLUMNS B
ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
WHERE A.CONSTRAINT_TYPE = 'P'
AND A.TABLE_NAME = '&TABLE_NAME'
;

 

Foreign Key

Information of the table linking to other tables.

Query Result Columns: 

  1. Foreign Key columns,
  2. Foreign Table
  3. Foreign Table Primary Key Columns
DEFINE TABLE_NAME = '<TABLE_NAME>';
SELECT 
  D.COLUMN_NAME,                  -- 1. FOREIGN KEY COLUMN OF THE TABLE
  C.TABLE_NAME AS FOREIGN_TABLE,  -- 2. FOREIGN TABLE
  C.COLUMN_NAME AS FOREIGN_COLUMN -- 3. FOREIGN TABEL PRIMARY KEY COLUMNS
FROM USER_CONSTRAINTS A
LEFT JOIN USER_CONSTRAINTS B
 ON A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
LEFT JOIN USER_CONS_COLUMNS C
 ON B.CONSTRAINT_NAME = C.CONSTRAINT_NAME
LEFT JOIN USER_CONS_COLUMNS D
 ON A.CONSTRAINT_NAME = D.CONSTRAINT_NAME
WHERE A.CONSTRAINT_TYPE = 'R'
AND A.TABLE_NAME = '&TABLE_NAME'
;

Information of other tables linking to the tables.

Query Result Columns:

  1. Primary Key Column
  2. Foreign Table
  3. Foreign Table Foreign Key Column
DEFINE TABLE_NAME = '<TABLE_NAME>';
SELECT 
  D.COLUMN_NAME,                  -- 1. PRIMARY KEY COLUMN OF THE TABLE
  C.TABLE_NAME AS FOREIGN_TABLE,  -- 2. FOREIGN TABLE
  D.COLUMN_NAME AS FOREIGN_COLUMN -- 3. FOREIGN TABLE FOREIGN KEY
FROM USER_CONSTRAINTS A 
LEFT JOIN USER_CONSTRAINTS B
 ON A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
LEFT JOIN USER_CONS_COLUMNS C
 ON B.CONSTRAINT_NAME = C.CONSTRAINT_NAME
LEFT JOIN USER_CONS_COLUMNS D
 ON A.CONSTRAINT_NAME = D.CONSTRAINT_NAME
WHERE A.CONSTRAINT_TYPE = 'P'
AND A.TABLE_NAME = '&TABLE_NAME'
;

Information of other tables linking to the table and the tables linking to other tables.

DEFINE TABLE_NAME = '<TABLE_NAME>';
SELECT 
 'FOREIGN' AS KEY_TYPE,          -- 1. KEY TYPE OF THE COLUMN (SECOND COLUMN)
 D.COLUMN_NAME,                  -- 2. FOREIGN KEY COLUMN OF THE TABLE
 C.TABLE_NAME AS FOREIGN_TABLE,  -- 3. FOREIGN TABLE
 C.COLUMN_NAME AS FOREIGN_COLUMN -- 4. FOREIGN TABEL PRIMARY KEY COLUMNS
FROM USER_CONSTRAINTS A
LEFT JOIN USER_CONSTRAINTS B
 ON A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
LEFT JOIN USER_CONS_COLUMNS C
 ON B.CONSTRAINT_NAME = C.CONSTRAINT_NAME
LEFT JOIN USER_CONS_COLUMNS D
 ON A.CONSTRAINT_NAME = D.CONSTRAINT_NAME
WHERE A.CONSTRAINT_TYPE = 'R'
AND A.TABLE_NAME = '&TABLE_NAME'
UNION
SELECT 
 'PRIMARY' AS KEY_TYPE,           -- 1. KEY TYPE OF THE COLUMN (SECOND COLUMN)
 D.COLUMN_NAME,                   -- 2. PRIMARY KEY COLUMN OF THE TABLE
 C.TABLE_NAME FOREIGN_TABLE,      -- 3. FOREIGN TABLE
 D.COLUMN_NAME AS FOREIGN_COLUMN  -- 4. FOREIGN TABLE FOREIGN KEY COLUMNS
FROM USER_CONSTRAINTS A 
LEFT JOIN USER_CONSTRAINTS B
 ON A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
LEFT JOIN USER_CONS_COLUMNS C
 ON B.CONSTRAINT_NAME = C.CONSTRAINT_NAME
LEFT JOIN USER_CONS_COLUMNS D
 ON A.CONSTRAINT_NAME = D.CONSTRAINT_NAME
WHERE A.CONSTRAINT_TYPE = 'P'
AND A.TABLE_NAME = '&TABLE_NAME'
;

Check Constraints

DEFINE TABLE_NAME = '<TABLE_NAME>';
SELECT B.COLUMN_NAME, A.SEARCH_CONDITION FROM USER_CONSTRAINTS A
LEFT JOIN USER_CONS_COLUMNS B
ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
WHERE A.CONSTRAINT_TYPE = 'C'
AND A.TABLE_NAME = '&TABLE_NAME'
;

Unique Constraints

DEFINE TABLE_NAME = '<TABLE_NAME>';
SELECT B.COLUMN_NAME FROM USER_CONSTRAINTS A
LEFT JOIN USER_CONS_COLUMNS B
ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
WHERE A.CONSTRAINT_TYPE = 'U'
AND A.TABLE_NAME = '&TABLE_NAME'
;
DEFINE TABLE_NAME = '<TABLE_NAME>';
SELECT B.TABLE_NAME, B.COLUMN_NAME FROM USER_CONSTRAINTS A
LEFT JOIN USER_CONS_COLUMNS B
ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
WHERE A.CONSTRAINT_TYPE = 'U'
AND A.TABLE_NAME = '&TABLE_NAME'
;

 

Index

Query index list

SELECT 
 B.TABLE_NAME, 
 B.COLUMN_NAME, 
 A.INDEX_TYPE,
 A.UNIQUENESS
FROM USER_INDEXES A
LEFT JOIN USER_IND_COLUMNS B
ON A.INDEX_NAME = B.INDEX_NAME
;

Query index of specialized table

DEFINE TABLE_NAME = '<TABLE_NAME>';
SELECT 
 B.TABLE_NAME, 
 B.COLUMN_NAME, 
 A.INDEX_TYPE,
 A.UNIQUENESS
FROM USER_INDEXES A
LEFT JOIN USER_IND_COLUMNS B
ON A.INDEX_NAME = B.INDEX_NAME
WHERE A.TABLE_NAME = '&TABLE_NAME'
;

PL/SQL Programming Concepts: Review

PL/SQL Run-Time Architecture

Image 699

Block Type

  • Procedure
  • Function
  • Anonymous

PL/SQL Block Structure

  • Declare
    • Variables, Cursors, User-defined exceptions
  • Begin
    • SQL Statement
    • PL/SQL Statement
  • Exception
    • Action to perform when exception occurs
  • End;

Type of Variables

  • PL/SQL Variables
    • Scalar
    • Reference
    • Large object (LOB)
    • Composite (Record, Collection)
  • Non-PL/SQL Variables
    • Bind variables

 

Reference:

http://people.inf.elte.hu/sila/DB1Pract/Oracle_PLSQL.pdf

 

 

 

 

Advanced PL/SQL

  • Oracle11g: Advanced PL/SQL
  • PL/SQL Programming Concepts: Review
  • Designing PL/SQL Code
  • Working with Collections
  • Using Advanced Interface Methods
  • Implementing Fine-Grained Access Control for VPD
  • Manipulating Large Objects
  • Administering SecureFile LOBs
  • Performance and Tuning
  • Improving Performance with Caching
  • Analyzing PL/SQL Code
  • Profiling and Tracing PL/SQL Code
  • Safeguarding Your Code Against SQL Injection Attacks