1 / 85

ASIQ-M 12.5

ASIQ-M 12.5. Breaking News!! - 1. ASIQ 12.5 is released! New licensing “rules” Developer release (5 IP connections) Enterprise release (Unlimited [200-1000 users]) Workgroup release (<5 CPUs) Platforms Supported AIX (32 and 64 bit) HP/UX 64 bit Solaris 64 bit Win 2k 32 bit.

varsha
Download Presentation

ASIQ-M 12.5

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ASIQ-M 12.5

  2. Breaking News!! - 1 • ASIQ 12.5 is released! • New licensing “rules” • Developer release (5 IP connections) • Enterprise release (Unlimited [200-1000 users]) • Workgroup release (<5 CPUs) • Platforms Supported • AIX (32 and 64 bit) • HP/UX 64 bit • Solaris 64 bit • Win 2k 32 bit

  3. Breaking News!! - 2 • We will be releasing ASIQ on Linux (and Win 2003) • Platform will initially be Itanium (64 bit Intel) • Maybe we will release on 32 bit (but not in first release) • Date….. Hopefully Q4 2003

  4. Breaking News!! – 3 • ASIQ TPC/H benchmark results • 1. Best performance of all 4-CPU servers (any DBMS) 1760 • 2. Absolute lowest system cost of all systems (intel and RISC) $105,382 P/P $60 • 3. Absolute best price/performance of all systems • 4. Absolute lowest storage usage: competitors used 3x-9x more storage, at 6x-84x higher cost

  5. So what does this mean? • 1. IQ is DBMS that meets strict TPC-H rules. • 2. IQ Storage efficiency: 12 disks (RAID-1 which doubled storage size) and 2 I/O channels for IQ at a cost of $8,686. Competition used 3x-9x more storage than IQ at 6x-84x higher cost. • 3. ”IQ saves $1M per TB of input data”: IQ storage cost is $8,686 1/6 to 1/80 of the competition cost ($55K-$760K) storage saving of $45K-$750K per 100GBè $450K-$7.6M per TB of input data. • 4. IQ Performance: IQ-Sun has the HIGHEST performance and THE LOWEST system cost in 4-CPU, 100GB. IQ and Sun(900MHz CPU) beat competition using 1.6GHz CPUs. • 5. Simplicity and elegance of IQ: 1 server, 1 disk array(12 disks) and 2 IO channels.

  6. Goals for 12.5 - 1 Enhance Overall Functionality • Load Table • Permit continued processing for input data errors • Introduce Full Referential Integrity • IQ Multiplex • Remove DDL Restrictions • Simplify Architecture • Add Miscellaneous New Features and Functions

  7. Goals for 12.5 - 2 Improve VLDB Performance • Larger Device Sizes • IQ Multiplex improvements • Union All View Performance • New indexes for Date/Time data types • Performance improvements on large (many cpu) servers • Join Optimization for Complex Queries

  8. IQ v12.5 – Topics • Referential Integrity • New IQ Indexes • Load Table Command • IQ Multiplex 12.5 • Sybase Central Changes • Miscellaneous • Join Optimization • New Functions • New System Procedures and Tools

  9. I. Referential Integrity (RI) • 12.4.3 - RI Phase I • Multi-column Unique HG Indexes • Provided Full Entity Integrity • Multi-column Primary Keys • 12.5 Introduces Referential Integrity Phase II • Multi-column Non-Unique HG indexes

  10. Referential Integrity Some Definitions: • Referential Integrity (RI) A rule defined on a column(s) on one table that allows INSERT or UPDATE of a row only if its value for the column(s) matches a value in a column(s) of a related table RI also includes rules that dictate actions to be taken when UPDATE or DELETE on the referenced table cause the referenced data to disappear.

  11. RI Definitions • Candidate Key is a Primary Key OR is a column(s) with a Unique Constraint • Foreign Key (FK) and Referenced Key A column(s) whose values must be a subset of values of another column(s). The referenced column(s) must be a candidate key and is called the Referenced Key

  12. RI Definitions • RESTRICT Action Any UPDATE or DELETE* action on a candidate key that causes any value upon which a foreign key depends to disappear will be denied. An UPDATE of a foreign key which results in a value which does not match a candidate keywill be denied. RESTRICT action is the ANSI default for RI (* DELETE implies TRUNCATION)

  13. What Was Done to Implement RI - 1 • Create and Alter Table Commands • Added column and table level RI constraints • Using ANSI Standard Syntax • Changed Key Length Limit to 1K (1024) bytes • Total byte limit (+1 byte overhead/column) • Was 256 bytes in 12.4.3 • Added a Non-Unique multi-column HG index • For support of multi-column foreign keys (1K limit)

  14. What Was Done to Implement RI - 2 • Modified Load Table command • For handling loads with RI enabled on tables • Details in Load Table section of this presentation • Added new Database Option for RI • To disable/enable RI checking in the database

  15. Table Create Syntax – Primary Keys Unique HG index is created Create Table Dept( DeptNo int Primary Key , DeptName char(10) ) Create Table Emp( EmpNo int Primary Key , EmpName char(19) , Mgr int , DeptNo int )

  16. Table Create Syntax – Primary Keyor Unique Constraint Unique HG is created Create Table Dept( DeptNo int Primary Key , DeptName char(10) ) Create Table Emp( EmpNo int Unique , EmpName char(19) , Mgr int , DeptNo int ) These would beCANDIDATE KEYS

  17. Table Create Syntax – Foreign Keys Non-Unique HG created FKEY on Mgr column is an example of Self-Referential Integrity Create Table Dept( DeptNo int Primary Key , DeptName char(10) ) Create Table Emp( EmpNo int Primary Key , EmpName char(19) , Mgr int References Emp(EmpNo) , DeptNo int References Dept(DeptNo) )

  18. Table Create Syntax – Constraints Create Table Dept( DeptNo int Primary Key iq unique(5) , DeptName char(10) not null iq unique(5) ) Create Table Emp( EmpNo int Primary Key iq unique(50) , EmpName char(19) not null iq unique(50) , Mgr int References Emp(EmpNo) , DeptNo int References Dept(DeptNo) )

  19. Automatic Indexing • Primary Key or Unique Constraint • Creates Unique HG index on key column(s) • Can only be dropped by Alter Table • Foreign Key Constraint • Creates Non-Unique HG index on key columns(s) • Can only be dropped by Alter Table

  20. RI Requirements • Candidate Key must - • be a Primary Key or have a • Unique Constraint • Candidate Key and Foreign Key must - • Contain Same Number of Columns • Have Identical Data Types • Same sign, precision and scale

  21. RI Enforcement – RESTRICT Action • Foreign Keys enforce the following (else ROLLBACK): • INSERT/UPDATE on Foreign Key columns • Value entered must exist in the Reference Table Column(s) • UPDATE/DELETE on Referenced Column • Cannot be deleted/truncated if values exist in its FKeys • Cannot be updated to leave orphans in its FKeys

  22. RI Enforcement – Some Details • ALTER TABLE Add Foreign Key will fail • when performed on a table with data loaded and there is an RI violation • LOAD TABLE • Will fail/roll back by default if there is a RI violation • New Load Table option to reject rows in violation • No foreign key can be associated with a candidate key that is also a foreign key

  23. Only RESTRICT Action is Available • Version 12.5 offers no other alternatives • Other RI Options not available include - • CASCADE (delete referenced rows) and • SET NULL (Set Fkey values to NULL) • These options may be considered for follow-on releases

  24. Database Option for RI • New Public Option to enable / disable RI Checks Disable_RI_Check – default is OFF • May be set to ON to bypass RI checks to permit - • Load Table • Insert • Update • Delete

  25. RI Load Performance Implications • If no RI has been specified, there is no performance hit • If RI is being enforced performance will depend on … • Type of activity (Load, Delete, Update) • Number of Foreign Key / Candidate Relationships • Dimension of the Foreign Key / Candidate • Platform • Checking occurs during Phase II of table loads • Expect 5% (max) overhead for RI checking

  26. Other RI Implications • Using RI will improve query performance !!! • Provides a wealth of information to Query Optimizer • Improves Many to Many Joins • Multi-column indexes help in some joins (and GROUP BY clauses) • Now that ASIQ has RI you may need to use it • Specific situation will be addressed later

  27. II. New IQ Indexes • Multi-Column Non-Unique HG • Date Indexes • Word Index Enhancement

  28. Multi-Column Indexes • We have discussed non-unique multi-columns • This is a HG index • May be created manually • What’s new is that the optimizer can use these indexes! • Not so in 12.4.3 • This index will be used for estimates in join plans

  29. Date Indexes • New Indexes have been added to support queries on • Dates • Time • Datetime • These indexes support a number of query types • Ranges searching • Datepart searches • You may not need to denormalize for date functionality

  30. Date Index • For use with columns defined as DATE data type • Any other data type returns an error • CANNOT be declared Unique • Date Index will outperform HNG for Range Queries • Will work best with LF or HG on same column • LF & HG indexes provide distinct counts for the optimizer, and • LF/HG are best for equality predicates

  31. Date Index for Queries • Designed to support these queries - • Datepart Functions • Month, Day of Month, Week, Day of Week, Quarter, Year, Week in Year • With these Operators • GT, GE, LT, LE, EQ, NE, Between • Date Index will NOT support • Sum, Average, SumDistinct • Min, Max

  32. Date Index vs. Other Index Tests 100 MM Rows – Random Dates • Date1 = ‘1990/12/15’ (29K rows) • HG = 1 sec • DateIndex = 26 sec • HNG = 29 sec • Date1 > ‘1993/03/10’ ( 68 MM rows) • DateIndex = 14 secs • HNG = 29 sec • HG = 200 sec • Date1 between ‘1993/03/10’ and ‘1996/08/04’ (33 MM) • DateIndex = 38 secs • HNG = 64 sec • HG = 211 sec

  33. Time Index • For use with columns defined as TIME data type • Any other data type returns an error • CANNOT be declared Unique • Time Index will outperform HNG for Range Queries • Will work best with LF or HG on same column • LF & HG indexes provide distinct counts for the optimizer • LF/HG are best for equality predicates

  34. Time Index for Queries • Designed to support these queries - • Date Functions • Hour, Minute, Second • With these Operators • GT, GE, LT, LE, EQ, NE, Between • Will NOT support • Sum, Average, SumDistinct • Min, Max • You will need a HNG or HG for these operators

  35. Datetime Index • Similar functionality of Date and Time indexes • For Datetime data types • You should drop existing HNG indexes and replace then with one of the new indexes on data types • Date • Datetime • Time

  36. Datetime Indexes The components of the index are persistent bitmaps, similar in construction to an LF Bitmap structure

  37. Word Index Enhancement • The like operator will support the Word Index • Previously supported with contains operator • Both predicates below would use the Word Index Where company_name contains (‘Sybase’) and Where company_name like ‘%pSybasep%’

  38. III. Load Table Command • Load Table has been modified to - • Limit the number of Integrity Constraint violations before a Load Table transaction will Rollback • Rows in violation are NOT inserted • Can Log violations to a file (optional) • The existing Load Table command is otherwise intact and will continue to support table load operations Note: There is no change to the INSERT, UPDATE or DELETE commands.

  39. Integrity Constraints Affected by Load Table • UNIQUE Constraint • NULL Constraint • DATA Value Constraint • With option CONVERSION_ERROR = ‘OFF’ • FOREIGN KEY Constraint

  40. DATA CONVERSION ERROR Option • The CONVERSION_ERROR option plays a part • This option is not new and is still supported • Default is ON • Any data conversion causes a ROLLBACK • Does not change with the new Load Table options • For this discussion, assume CONVERSION_ERROR option is set OFF • All data conversion violations will cause NULL to be entered in the offending column

  41. Load Table Command – 12.4.x LOAD TABLE table-name ( LOAD-specification [, …] ) FROM ‘filename’ [, …] QUOTES OFF ESCAPES OFF < other load options >

  42. Load Table Command – 12.5 LOAD TABLE table-name ( LOAD-specification [, …] ) FROM ‘filename’ [, …] QUOTES OFF ESCAPES OFF < other load options > [ IGNORE CONSTRAINT constrainttype [, …] ] [ MESSAGE LOG ‘file’ ROW LOG ‘file’ [ONLY LOG logwhat, [ …] ] [ LOG DELIMITED BY ‘string’ ] constrainttype { UNIQUE integer | NULL integer | FOREIGN KEY integer | DATA VALUE integer | ALL integer } logwhat: { ALL | NULL | UNIQUE | DATA VALUE | FOREIGN KEY } - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  43. IGNORE CONSTRAINT Clause [ IGNORE CONSTRAINT constrainttype [, …] ] constrainttype { UNIQUE integer | NULL integer | FOREIGN KEY integer | DATA VALUE integer | ALL integer } • When any one the constrainttypes are specified with a value • If zero, then all violations of that type are ignored • If non-zero (say N) the N+1 occurrence will cause a ROLLBACK

  44. ALL Constraints Specified • The ALL constrainttype has a cumulative effect Example:IGNORE CONSTRAINT NULL 50, UNIQUE 100, ALL 125 • Rollback will occur when – • 50 NULL constraint violations occur, or • 100 UNIQUE constraint violations occur, or • Any FOREIGN KEY violation occurs, or • 125 total violations (ALL types combined) occur • DATA VALUE violations • Depends onCONVERSION_ERROR setting

  45. Constraint Violation Logs [ MESSAGE LOG ‘file’ ROW LOG ‘file’ [ONLY LOG logwhat, [ …] ] [ LOG DELIMITED BY ‘string’ ] logwhat: { ALL | NULL | UNIQUE | DATA VALUE | FOREIGN KEY } • Options to specify what and where to log constraint violations • MESSAGE LOG (this is NOT the IQ Message Log) • Logs Integrity Violations as specified by logwhat • ROW LOG • Logs the ROWID and all input data values • Comma delimited (default delimiter) • Delimiter may be specified

  46. MESSAGE LOG - Three Parts • Header (Datetime load started) • Row Information (rowid, type, column number) • ROWID • ROWID in table where row would have been loaded • Type of Integrity Violation • One row per violation • Column number in the table schema • Trailer Message (Datetime load completed)

  47. MESSAGE LOG - Sample Load Table foo …Ignore Constraint UNIQUE 200Message Log ‘msg.log’ Row Log ‘row.log’Log Unique, Null, Data ValueLog Delimited By ‘|’ Message Log (msg.log) contents 2002-07-15 15:00:23 Load Table FOO: Integrity Constraint Violations1267 DATA VALUE 43126 UNIQUE 13216 NULL 32002-07-15 16:00:10 LOAD TABLE FOO Completed

  48. ROW LOG – Three Parts • Header (Datetime load started) • Row Data (rowid, delimited data values for row) • Binary/Varbinary data appear as ascii hexadecimal • Date/Datetime values appear as configured by the • DATE_FORMAT or DATETIME FORMAT options • Null values appear as NULL • Filler fields will not appear • Trailer Message (Datetime load completed)

  49. ROW LOG - Sample Load Table foo …Ignore Constraint UNIQUE 200Message Log ‘msg.log’Row Log ‘row.log’Log Unique, Null, Data ValueLog Delimited By ‘|’ Row Log (row.log) Contents 2002-07-15 15:00:23 Load Table FOO: Integrity Constraint Violations 1267 |Mary Smith|56|M|ABCDEFG|1943/03/31|MC| 3216 |John Jones|NULL|NULL|S|1945/02/28|NULL … 2002-07-15 16:00:10 LOAD TABLE FOO Completed

  50. Putting both together 2002-07-15 15:00:23 Load Table FOO: Integrity Constraint Violations1267 DATA VALUE 43216 UNIQUE 13216 NULL 32002-07-15 16:00:10 LOAD TABLE FOO Completed 2002-07-15 15:00:23 Load Table FOO: Integrity Constraint Violations 1267 |Mary Smith|56|M|ABCDEFG|1943/03/31|MC| 3216 |John Jones|NULL|NULL|S|1945/02/28|NULL … 2002-07-15 16:00:10 LOAD TABLE FOO Completed

More Related