Ingres 10 DBMS
Download
Report
Transcript Ingres 10 DBMS
Ingres 10 Key Features
GIUA 2010, Würzburg September 30
Sarkaut Mohn
Ingres 10 Key Features
New Ingres 10 Features Derived by Ingres ISV
Partners
–
–
–
–
–
–
–
Multi Version Concurrency Control (MVCC)
Scalar Subqueries
Batch Query Execution
Data Encryption
Rename Objects
Long Identifiers
Bulk Load Performance
Confidential — © 2010 Ingres Corporation
Slide 2
MVCC
Why We Need It?
Time
Update Transaction
Read Transaction with
Traditional Locking
Read Transaction
with MVCC Locking
• Updates row A in table X
• Attempts to read row A
in table X
• Updates row B in table Y
BLOCKED because
read cannot acquire
lock held by update!
• Commits transaction
• Read from table X
finally succeeds after
update has committed
With MVCC, readers are no longer blocked by writers
Likewise, writers are no longer blocked by readers
Confidential — © 2010 Ingres Corporation
Slide 3
• Successfully reads row
A from table X
• Successfully reads row
B from table Y
Data read is preupdate, so no
locking required
No application
hangs
Better performance!
MVCC
Why We Need It?
Existing applications originally written for DBMSs
like Oracle that support MVCC depend on the high
concurrency it provides
Avoids performance problems and application hangs
that Ingres encounters with traditional (ANSI
Isolation Level based) locking
Applications can avoid having to read inconsistent
data
– Ugly workaround of running with dirty read isolation
will no longer be necessary
Readers see consitent transaction / statement
snapshot of data based on isolation level
Confidential — © 2010 Ingres Corporation
Slide 4
Scalar Subqueries
Ingres currently only allows scalar subqueries to be
used in limited contexts
SELECT * FROM t1 WHERE t1.col =
(SELECT min(col) from t2 where t2.key = t1.key);
DELETE FROM t WHERE col = (SELECT max(col) FROM t);
Ingres 10 will allow scalar subqueries to be used in
more instances
Rewriting existing applications to avoid subqueries
is not easily scriptable
Confidential — © 2010 Ingres Corporation
Slide 5
Scalar Subqueries
Examples
Usage
Example
Projection List of
SELECT
SELECT emp_name, dept_name,
(SELECT MAX(salary) FROM emp) AS highest_salary
FROM emp;
VALUES clause
in INSERT
INSERT INTO credit(name, max_credit)
VALUES(‘XYZ’,
(SELECT MAX(credit) FROM credit_table
WHERE name = ‘XYZ’));
SET clause in
UPDATE
UPDATE tab1 t1 SET col =
(SELECT sum(col) FROM tab2 t2
WHERE t2.key = t1.key);
CASE expression SELECT vars,
CASE (SELECT expr FROM t2 WHERE t2.key = t1.key)
WHEN 1 THEN ‘A’ ELSE ‘B’ END
FROM t1;
Procedure
parameter
EXECUTE PROCEDURE proc
(count_param = (SELECT count(*) FROM t));
Confidential — © 2010 Ingres Corporation
Slide 6
Batch Query Execution
Non-Batch Execution
Client Application
Batch Execution
Ingres DBMS
JDBC Application
Creates Table
Create Table
Inserts into Table
ok
ok
ok
ok
Executes
Procedure
Execute
Procedure
Creates Table
Inserts into Table
Executes
Procedure
Create Table
Insert into Table
Execute
Procedure
ok
Insert into Table
Ingres 10 DBMS
ok
Any SQL statement can appear in a batch, except:
–
–
–
Fewer round trip
messages
Better performance!
SELECTs
Row producing procedures
Transaction Statements
Individual statements in the batch can succeed while others can fail,
provided the failures aren’t severe
Confidential — © 2010 Ingres Corporation
Slide 7
Batch Query Execution
Current Supported Interfaces
Java
– Use addBatch and executeBatch methods
– Unchanged from previous releases
• But now recognized by the DBMS rather than
translated in JDBC
• Massive improvement in performance
OpenAPI
– New IIapi_batch() function
No other interfaces currently supported
– ODBC may come soon
Confidential — © 2010 Ingres Corporation
Slide 8
Batch Query Execution
Additional Feature Details
Ingres JDBC driver already supports the API interfaces
necessary for batch execution
– addBatch
– executeBatch
Existing JDBC implementation, however, executes the
same as non-batch; i.e., no reduction in client-server
communication
Batch execution also allows a single statement to be
executed with multiple sets of parameters
This form of execution has been optimized for better load
performance
Performance of ETL tools that use batch execution in this
way will therefore benefit
Confidential — © 2010 Ingres Corporation
Slide 9
Batch Query Execution
Special Optimization for Insert
Ingres 10 DBMS
JDBC Application
• Prepare INSERT
statement
•Execute “prepare”
ok
• Add “Execute INSERT
with parameter set 1” to
batch
• Add parameter set 2 to
batch
•…
• Add parameter set n to
batch
• Execute batch
•Load “copy” buffer with
each parameter set
•Execute Copy Statement to
insert data
•Return results
ok
…
ok
Confidential — © 2010 Ingres Corporation
Slide 10
Batch Query Execution
Related Parameters
JDBC
– System Property: ingres.jdbc.batch.enabled
• Determines whether executeBatch() will translate into batch
execution in the DBMS or revert to the pre-10.0 behavior
DBMS
– Config Value: ii.<node>.*.batch_copy_optim
• Values “ON” (use the copy optimization), “OFF” (do not use the
copy optimization, insert statements are not converted to a copy)
– SQL “SET [NO]BATCH_COPY_OPTIM”
• As above for current session only
All parameters are for debug purposes only
Confidential — © 2010 Ingres Corporation
Slide 11
Data Encryption
Required for
– PCI-DSS 1.2 compliance - credit card information storage
(https://www.pcisecuritystandards.org/)
– HIPAA compliance - health information storage
Applies only to data “at rest”
Specified at the column level
By default, a “salt” and “verification hash” will be
embedded in the encrypted data
– Salt – Random bits that further obfuscate the encrypted
data
– Verification hash – Collision-resistant hash that allows the
integrity of the decryption to be verified
Confidential — © 2010 Ingres Corporation
Slide 12
Data Encryption
Securing the Encryption Keys
Tables with encrypted columns must specify:
– Whether to use AES-128, AES-192, or AES-256 encryption
– An encryption passphrase
Passphrase is never stored on disk, only the encrypted table key
Random Key
AES-256 Encryption
Encrypted
Encrypted
Table Key
Key
Table
To access encrypted columns within a table, encryption passphrase
associated with the table must be enabled at server startup
Encrypted
Encrypted
TableKey
Key
Table
Key Derived
From
Passphrase
Key Derived
From
Passphrase
Unencrypted Table Key
AES-256 Decryption
Unencrypted table key can then be used to encrypt/decrypt column
data
Confidential — © 2010 Ingres Corporation
Slide 13
Rename Objects
Introduction
Rename table/column:
Ability to rename a table or a column using new SQL
syntax:
No need to drop and recreate the table.
No need to drop and add the column.
No unload/reload of data.
Rename Objects
New SQL Syntax
Table rename
[EXEC SQL] RENAME [TABLE] [schema.]<table_name>
TO <new_table_name>
[EXEC SQL] ALTER TABLE [schema.]<table_name>
RENAME TO <new_table_name>
Column rename
[EXEC SQL] ALTER TABLE [schema.]<table_name>
RENAME [COLUMN] <column_name>
TO <new_column_name>
Rename Objects
Access Restrictions
You must own the tables (or whose column) you are
renaming.
You cannot rename a system catalog, extended
system catalog or any tables owned by super user
‘$ingres’.
You cannot rename columns of such catalogs
Rename Objects
Naming Restrictions
The name of the new table/column should conform
to naming rules for tables.
– See SQL Reference Guide, chapter 2.
There should be no table / column already existing in
the database with same name and owner
– The new table / column name will get the same reltid
or attribute id.
Long Identifiers
Ingres object names are currently limited to 32 characters
Current limit is problematic for applications like Hibernate that
auto-generate long names
Ingres 10 increases most object names to 256 characters
– Objects affected include: tables, columns, procedures, parameters,
rules, sequences
– Objects excluded include: databases, users
Enhancements made in DBMS to avoid additional memory
overhead if applications do not need long identifiers
Pre-existing databases will be converted to support long
identifiers during database upgrade
Confidential — © 2010 Ingres Corporation
Slide 18
Bulk Load Performance
CSV (comma separated values) and SSV (semi-colon
separated values) delimiters allow COPY to READ /
WRITE files
COPY from text file up to 2x faster
COPY FROM loading partitioned table bulk loads
into partitions
Confidential — © 2010 Ingres Corporation
Slide 19
Other Ingres 10 Features
Application Enablement Features
– New BOOLEAN Data Type (TRUE, FALSE, NULL)
– JDBC 4.0 Compliance
Additional remote data access performance improvements
New Functions – GREATEST, LEAST, NVL, NVL2
Hash Join and Hash Aggregation Improvements
Recovery Server Error Handling
Ingres for 64-bit Windows
64-bit ODBC Driver
Confidential — © 2010 Ingres Corporation
Slide 20
Full Ingres 10 Features
http://community.ingres.com/wiki/Ingres_Roadmap/10
Confidential — © 2010 Ingres Corporation
Slide 21
Potential Features Post Ingres 10
Scrollable Cursors for Large
Objects
UTF-8 Transliteration
Reuse Indexes for Constraints
Allow NULLs in Unique
Columns
Expanded Usernames
Stored Procedure
Enhancements
ALTER TABLE Enhancements
Implicit Commits for DDL
…
Improved Data Partitioning
Query Caching Improvements
Improved Memory
Management
Improved Top N Performance
Improved Performance
Monitoring
Generate data histograms
within the DBMS
Improved UTF-8 Performance
Installer Enhancements
Improved GeoSpatial Support
Confidential — © 2010 Ingres Corporation
Slide 22