Custom Search : Technical Guides Sybase Oracle UNIX Javascript

Technical Guides

Of Interest

Business Intelligence and Analytics

Oracle Training

Sybase » Administration » DBA » Sybase IQ

Sybase IQ Overview


Sybase IQ utilizes column-based storage, thus allowing it to perform narrow scans on only the required columns or vectors required for the query. This results in 1/100 the less scanning of a typical database, and 20-100X better performance. In addition, every column is part of an index structure, and disk storage required is vastly reduced at the same time.

The Sybase IQ Paradox Out of the box, with minimum configuration changes (default memory settings), you will experience superior performance.
  • Compression is on, by default; a 10 GB table (size in another database) will take 4 GB or less in IQ
  • All columns are indexed by default, in a structure optimized for projection queries

The Paradox: With compression on, and no indexes, vast improvements in query performance can be experienced.

Critical: What you need to know about Sybase IQ

Sybase IQ is not for OLTP
Traditional inserts/updates/deletes are allowed, but should not be considered business as usual
Case 1 .. GOOD: Allowing an administrator to occasionally add new products records, for an online retailer
Case 2 .. BAD: Several hundred users are updating ticket reservations via a client-server application

Also - for those developers who run "select *" a lot: if the table has 80 or more columns, you might see decreased performance. Limit your result set to be meaningful. As Sybase IQ is column-base, there is a lot of overhead in running "select *" on a table with lots of columns.

Is it complicated to get started, once the server is installed?
There is little or no learning curve, skills on other RDBMs transfer quite easily
1) Create tables (data types are those similar to Sybase / SQL Server)
2) Load the tables (use the LOAD TABLE command, which is very easy, similar to BCP or SQL-Loader)
3) Run any ANSII or T-SQL query, to see the difference
4) Data is instantly accessible, via Business Objects, Cognos, Crystal Reports, JDBC apps, ODBC apps

  • No different to any other standard RDBMS
  • Standard SQL, commands, functions
  • Adding new data/columns/indexes is TRIVIAL and FAST; columns can also be dropped or renamed
  • Does not interfere with existing data and query development
  • Test and develop on live data - safely
  • Server-level INDEX_ADVISOR option allows engine to suggest indexes where needed

Data Types
  • Data types are the same as for Sybase ASE (and SQL Anywhere)
  • integer, smallint, tinyint, decimal, numeric, double, float, real, bigint
  • date, smalldatetime, datetime, time, timestamp
  • char, varchar, text, clob
  • varchar has max of 32,000 chars.
  • tables can have up to 16000 columns!

Main Index Types
  • HG / high_group more than 1000 distinct values enhanced b-tree index
  • HNG / high_non_group data ranges, high cardinality bitmap index (non value based)
  • LF / low_fast less than 1000 distinct values bitmap index
  • WD character strings keywords are indexed

WD Index Example
A powerful document search facility could be developed using this type of index.
 select doc_name, doc_dt from library_books where book_content contains ('Lincoln')

Loading / Exporting
You can truncate and load table - without affecting users or reports!
 begin transaction
 truncate table invoices
 load table .. (see topic for syntax)
 commit transaction

Sybase : Related Topics

Sybase : Administration : Post-installation check
Sybase : Administration : Device initialization
Sybase : Administration : Database Creation
Sybase : Administration : A backup routine
Sybase : Administration : Striping Dump Devices
Sybase : Administration : Moving the transaction log to another device
Sybase : Administration : Adding a segment to a database
Sybase : Administration : Configuring the cache
Sybase : Administration : Apply multiple transaction dumps
Sybase : Administration : Database maintenance procedure
Sybase : Administration : Database maintenance script
Sybase : Administration : Server configuration
Sybase : Administration : Dealing with a Corrupted Database
Sybase : Administration : Dealing with a Server Failure
Sybase : Administration : DBCC Notes
Sybase : Administration : Create proxy tables and external logins
Sybase : Administration : Renaming a database
Sybase : Administration : Reorg: rebuilding a table
Sybase : Administration : Sybase ASE 15 Enhancements
Sybase : Administration : Setting the thresholds
Sybase : Administration : Apply a transaction dump
Sybase : Administration : Security Tasks
Sybase : Administration : Setting Process Priorities
Sybase : Administration : Sybase ASE Architecture Overview
Sybase : Administration : Drop an alias, with objects linked to login
Sybase : Administration : Display grants, effective rights to objects
Sybase : Administration : Displaying access information from sysprotects
Sybase : Administration : Database Engines: Status and Settings
Sybase : Administration : Replication Server 15 Configuration
Sybase : Administration : Loading data into Sybase IQ from a Remote Server
Sybase : Administration : Sybase IQ Functions
Sybase : Administration : Sybase IQ - Remote Access From ASE
Sybase : Administration : Sybase IQ : Procedure Profiling / Monitoring
Sybase : Administration : Sybase IQ: Restoring a Database from Dump Files
Sybase : Administration : Sybase IQ: Minimize Storage option
Sybase : Administration : Sybase IQ: Index Types
Sybase : Administration : Sybase IQ: HUGEPAGE Support
Sybase : Administration : Sybase IQ: Database Creation (Raw Devices)
Sybase : Administration : Sybase IQ : Repairing Problems / Troubleshooting Corruption
Sybase : Administration : Sybase IQ : Moving / Re-allocating / Re-org
Sybase : Administration : Sybase IQ: Sysmon / System Performance Analysis
Sybase : Administration : Sybase IQ: Monitoring Connections / Processes
Sybase : Administration : Sybase ASE: Installing a new server via srvbuildres utility
Sybase : Administration : Sybase IQ: Copying users/logins between databases
Sybase : Administration : Sybase IQ: Managing User Accounts
Sybase : Administration : Sybase ASE: Database Schema Compare
Sybase : Administration : Sybase IQ: Listing tables, ordered by size allocated
Sybase : Administration : Sybase IQ : Comparing grants / permissions between servers
Sybase : Administration : Sybase ASE: Database page size
Sybase : Administration : Sybase ASE: Managing users and roles
Sybase : Administration : Sybase ASE: Setting resource limits for users
Sybase : Administration : Sybase ASE: Setting up user assigned custom tempdbs
Sybase : Administration : Sybase ASE: Setting up the transaction log cache using logiosize
Sybase : Administration : Sybase IQ: Server Startup Switches
Sybase : Administration : Sybase ASE: Managing Identity Columns
Sybase : Administration : Sybase IQ: Working with options and server settings
Sybase : Administration : Sybase IQ: Monitoring and Troubleshooting via System Procedures

Sybase Web Site
Sybase iAnywhere Mobile Web Site
Oracle Enterprise Web Site

Get the latest Rocket99 news and tech tips via

Site Index About this Guide to Sybase, Oracle, and UNIX Contact Us Advertise on this site

Copyright © 2016 Stoltenbar Inc All Rights Reserved.