User:Hibethy/sandbox

From Wikipedia, the free encyclopedia
Jump to: navigation, search

Limits[edit]

Information about data size limits.

Max DB size Max table size Max row size Max columns per row Max Blob/Clob size Max CHAR size Max NUMBER size Min DATE value Max DATE value Max column name size
4th Dimension limited  ?  ? 65135 200 GB (2 GiB Unicode) 200 GB (2 GiB Unicode) 64 bits  ?  ?  ?
Advantage Database Server Unlimited 16 EiB 65530 B 65135 / (10+ AvgFieldNameLength) 4 GiB  ? 64 bits  ?  ? 128
Altibase Unlimited  ?  ? 1024 2147483647 bytes 32000 bytes  ?  ?  ? 40
Apache Derby Unlimited Unlimited Unlimited 1012 (5000 in views) 2 147 483 647 chars 254 (VARCHAR: 32672) 64 bits 0001-01-01 9999-12-31 128
CUBRID 2 EB 2 EB Unlimited 6400 Unlimited 1 GB 64 bits 0001-01-01 9999-12-31 254

Note (1): Firebird 2.x maximum database size is effectively unlimited with the largest known database size >980 GB.[1] Firebird 1.5.x maximum database size: 32 TB.

Note (2): limit is 1038using DECIMAL datatype[2]

Note (3): InnoDB is limited to 8,000 bytes (excluding VARBINARY, VARCHAR, BLOB, or TEXT columns)[3]

Note (4): InnoDB is limited to 1,000 columns[3]

Note (6): Using VARCHAR (MAX) in SQL 2005 and later[4]

Note (7): When using a page size of 32 kB, and when BLOB/CLOB data is stored in the database file.

Note (8): Java array size limit of 2,147,483,648 (231) objects per array applies. This limit applies to number of characters in names, rows per table, columns per table, and characters per CHAR/VARCHAR.

Note (9): Despite the lack of a date datatype, SQLite does include date and time functions,[5] which work for timestamps between 0000-01-01 00:00:00 and 5352-11-01 10:52:47.

Note (10): Informix DATETIME type has adjustable range from YEAR only through 1/10000th second. DATETIME date range is 0001-01-01 00:00:00.00000 through 9999-12-31 23:59:59.99999.

Indexes[edit]

Information about what indexes (other than basic B-/B+ tree indexes) are supported natively.

R-/R+ tree Hash Expression Partial Reverse Bitmap GiST GIN Full-text Spatial FOT
4th Dimension ? Cluster ? ? ? ? ? ? ? ?
ADABAS ? ? ? ? ? ? ? ? ? ?
Adaptive Server Enterprise No No Yes No Yes No No No Yes ?
Advantage Database Server No No Yes No Yes Yes No No Yes ?
Altibase No No Yes No Yes Yes No No Yes ?
Apache Derby No No No No No No No No No[6] ?
CUBRID No No No No Yes No No No ? ?
Oracle Rdb No Yes ? No No ? No No ? ?
PostgreSQL Yes Yes Yes Yes Yes7 Yes8 Yes Yes Yes[7] PostGIS[8]

Note (1): The users need to use a function from freeAdhocUDF library or similar.[9]

Note (2): Can be implemented for most data types using expression-based indexes.

Note (3): Can be emulated by indexing a computed column[10] (doesn't easily update) or by using an "Indexed View"[11] (proper name not just any view works[12])

Note (4): Can be implemented by using an indexed view.[13]

Note (5): InnoDB automatically generates adaptive hash index[14] entries as needed.

Note (6): Can be implemented using Function-based Indexes in Oracle 8i and higher, but the function needs to be used in the sql for the index to be used.

Note (7): A PostgreSQL functional index can be used to reverse the order of a field.

Note (8): PostgreSQL will likely support on-disk bitmap indexes in a future version. Version 8.2 supports a related technique known as "in-memory bitmap scans".

Note (10): B+ tree and full-text only for now.

Note (11): R-Tree indexing available in base edition with Locator but some functionality requires Personal Edition or Enterprise Edition with Spatial option

References[edit]

  1. ^ Technical Specifications, Guide, Firebird SQL .
  2. ^ Library, MSDN, Microsoft .
  3. ^ a b "Column count limit", Reference Manual, MySQL Documentation (5.1), Oracle .
  4. ^ "Row-Overflow Considerations", TechNet Library, SQL Server Documentation (2012), Microsoft .
  5. ^ "Date functions", Language, SQLite .
  6. ^ "Derby", Full Text Indexing, Search, Issues, Apache .
  7. ^ Full Text Search], Documentation (8.4), PostgreSQL community .
  8. ^ "4", Manual, PostGIS, Refractions .
  9. ^ UDF, Ad Hoc Data .
  10. ^ "Create DB", Library, MSDN, Microsoft .
  11. ^ "SQL", Library, MSDN, Microsoft .
  12. ^ Petkovic, Dusan (2005). Microsoft SQL Server 2005: A Beginner’s Guide. McGraw-Hill Professional. p. 300. ISBN 978-0-07-226093-9. 
  13. ^ "Indexed Views", Library, MSDN .
  14. ^ "InnoDB adaptive Hash", Reference manual, Development documentation (5.0), Oracle .