Informix SQL Tips and Tricks
Download
Report
Transcript Informix SQL Tips and Tricks
Informix
SQL Tips and Tricks
SQL Tips and Tricks
Bob Carts
Senior Data Engineer, SAIC
[email protected]
Warren Donovan
Senior DBA, SAIC
[email protected]
3/6/2002
Slide 2
Introduction
Certified Informix DBAs
WAIUG Board of Directors
Work for SAIC
NT and UNIX (Solaris, IBM AIX)
IDS 7.31, 9.21, 9.3 and XPS 8.3
Data Warehouse and OLTP Applications
Informix
3/6/2002
Slide 3
Introduction
Some Tips/Syntax work on specific releases
Be careful with your data!
Many other techniques in shell script and stored
procedures not included here
What release are you using?
3/6/2002
Slide 4
Introduction
Data Warehouse Project
ETL Programming
Evolution
C
programs
Stored Procedures
SQL
Good Introduction to SQL
3/6/2002
Slide 5
New Commands (or sort of new)
Case - Useful in transforming data (7.3 on)
decode - (7.3 on)
NVL function -
first - Just get a few rows (7.3 on)
middle - Just get some middle rows (XPS only)
3/6/2002
Slide 6
Case Statement
Generic or Linear
Can be nested and contain subqueries, functions
Can be contained within functions
Result must be a common datatype
Finds first true statement and then stops
select hospital,
case
when beds < 100 then “small”
when beds > 99 then “big”
else “No Value”
Linear Format
case beds
when < 100 then “small”
when > 99 then “big”
else “No Value”
3/6/2002
Slide 7
Case Statement
Example:
case
when MOD("11-30-2001"-(enterdate),30))/30)+1 > 1 then
((servicesraw)/(0.993712-(1.046021*EXP(-0.362649*
POW((("11-30-2001"- (enterdate)-MOD("11-30-2001"(enterdate),30))/30)+1,0.925358)))))
else null
end
Example:
sum(case
when donotcount_flag ="0" then 0
when donotcount_flag is null then 0
else entry_count
end
3/6/2002
Slide 8
Other Statements
Decode
can’t
decode a null
select manufacturer,
DECODE(beertype, 1, “stout”
2, “ale”
3, “lager”,
“other”) …
NVL function
Select applicant, NVL(age, “age not available”)…
Example
1 - If middle name is null then entire result is null
select
trim(last_name)||", "||trim(first_name)||" "|(middle_name[1],"") from staff;
Example2
- Fixed with NVL
select
trim(last_name)||", "||trim(first_name)||" "||nvl(middle_name[1],"") from staff;
3/6/2002
Slide 9
Other Statements
First (7.3, 8.3, 9.2, 9.3)
Select first 10 * from bigtable;
Can’t say into temp/scratch table
Can’t use in subquery or select clause of insert statement
Can’t uses as embedded select statement expression
Can use order by (first 10 after sort)
Middle (XPS only)
returns
middle rows!
3/6/2002
Slide 10
Cleaning up Data
Delete from
Rename table
Truncate
The
TRUNCATE TABLE <tablename> command quickly drops all
data in a table and resets the extents. A quick way to clear out a table,
is equivalent to dropping and rebuilding a table: it not only instantly
drops all the data, but actually resets all of the tables extents at the
same time!
WARNING: this command cannot be rolled back! Available in 8.3x
Alter table drop Column/add Column
3/6/2002
Slide 11
Cleaning up Data
Cleaning up a column
Example:
update tablename
set status = “”;
Another
Example
Alter table tablename
drop status;
Alter table tablename
add status;
3/6/2002
Slide 12
Testing and Test Data
Test your SQL without mangling your database
Issue a “begin work;” statement, then run your code,
then issue a “rollback;” statement
Only
works with logged databases!!
Only works for code that doesn’t have commits !!
Always make sure you have a backup of your data!!
Add a where condition that can’t be true: where 1= 2;
Set Explain On Avoid Execute;
3/6/2002
Slide 13
Testing and Test Data
Table is huge, just want a few rows for test data
Create
testtable
Unload to “filename” Select first 100 * from bigtable;
Load from filename insert into testtable;
No test data is available
Alter
statement to add the new columns and populate using the default
clause
Then write another alter to remove the default. It leaves the test data
behind
3/6/2002
Slide 14
Using Directives
Directives are available in IDS 9.X and XPS 8.32 and up
Directives force the database engine to run the query the
way you tell it to: your directives will only be as good as
your knowledge of the data
Force the engine to query tables in the order you specify
Force the engine to use a certain index
Force the engine to ignore indexes
Force the engine to Join tables in a specific way
Optimize for all data
Optimize a quick response
3/6/2002
Slide 15
Using Directives: ORDERED
Forces the engine to query tables in the order you specify
in the FROM clause of your SQL statement
Example:
SELECT --+ORDERED
c.x,c.y,b.z
FROM a,b,c
WHERE c.x=b.n AND b.z=a.z;
Will force the query to scan table a first, table b second and table c
third, which would be especially beneficial if tables a and b were both
small reference tables
3/6/2002
Slide 16
Using Directives: INDEX / AVOID_INDEX
Forces the engine to use / ignore a specific index
Example:
SELECT {+ORDERED, INDEX(a y), AVOID_INDEX(b n)}
c.x,c.y,b.z
FROM a,b,c
WHERE c.x=b.n AND b.z=a.z;
Will force the query to scan in the order in the from clause, and to use
the index on the a.y column, and to ignore indexes on the b.n column.
3/6/2002
Slide 17
Using Directives: FULL / AVOID_FULL
Forces the engine to perform or to avoid performing a
full table scan, even if an index exists.
Example:
SELECT {+ORDERED, INDEX(a y), AVOID_INDEX(b n), FULL(c)}
c.x,c.y,b.z
FROM a,b,c
WHERE c.x=b.n AND b.z=a.z;
Will force the query to scan in the order in the from clause, to use the
index on the a.y column, to ignore indexes on the b.n column and to
perform only full table scans on table c.
3/6/2002
Slide 18
Using Directives: JOIN METHOD DIRECTIVES
Forces the engine to perform or to avoid performing
certain join methods, such as Nested Loop or Hash Joins.
Example:
SELECT {+ORDERED, INDEX(a y), AVOID_INDEX(b n), FULL(c),
USE_HASH(c/BUILD)}
c.x,c.y,b.z FROM a,b,c WHERE c.x=b.n AND b.z=a.z;
Will force the query to scan in the order in the from clause, to use the
index on the a.y column, to ignore indexes on the b.n column and to
perform only full table scans on table c. Furthermore, on the first
join, table c will be used as the table from which the hash index is
built for the join with table b, which will be probed.
3/6/2002
Slide 19
Using Directives: OPTIMIZATION GOAL
DIRECTIVES
Forces the engine to query the tables in such a way as to
produce either the full result set as fast as possible
(default) or to get the first rows as quickly as possible.
Example:
SELECT {+ORDERED,FIRST_ROWS}
c.x,c.y,b.z FROM a,b,c WHERE c.x=b.n AND b.z=a.z;
Will force the query to scan in the order in the from clause and to
perform the rest of the operations in such a way as to get the first
rows of the result set out as quickly as possible. The method may
differ significantly from the method optimized to get ALL_ROWS as
quickly as possible.
3/6/2002
Slide 20
External Tables
External Tables (XPS only)
External Tables
replace the HPL on 8.3x systems. Essentially, allows
you to create a table that you can query with SQL, but is not located in
a dbspace: instead, the table points to a flatfile
Advantages: extremely fast data load / unload, you can perform SQL
against the data flatfiles when loading.
Disadvantages: if you insert data into the external table, it immediately
overwrites all the data in the table (ie: your flatfiles), cannot update
the data, cannot update statistics, so if your SQL query joins it to
another table, be sure to use DIRECTIVES.
3/6/2002
Slide 21
External Tables
External Tables (XPS only) basic syntax:
CREATE EXTERNAL TABLE <tablename>
<define table> *OR* SAMEAS <target tablename>
USING (
DATAFILES(location of files),
FORMAT <delimited or fixed>,
DELIMITER “!”,
REJECTFILE “<location of rejectfile>”,
EXPRESS or DELUXE (mode)
3/6/2002
Slide 22
External Tables
External Tables (XPS only) basic syntax example:
create external table cap_rateext
sameas cap_rate
using (
datafiles(
"disk:1:/bigfs/cap_rate/cap_rate.unl"
),
delimiter '|',
rejectfile "/informix/current_version/dump/cap_rate.%c",
maxerrors 10,
express
);
3/6/2002
Slide 23
Using Temp Tables
Need to create table structures dynamically for
temporary use
Select * from permanent_table where 1=0
into temp tmptab with no log
Then load the tables with the data you need to evaluate
load from “filename”
insert into tmptab;
3/6/2002
Slide 24
Using Temp Tables
Example: You have a multi-join query that is taking
forever to run
Reduce the number of initial joins and put the results in a
temp table, then use the temp table to join to the
remaining tables
Worked well in XPS using dynamic indexes
3/6/2002
Slide 25
Generating SQL using SQL
Example: 500 table database needs to have a new
datetime column added to every table!
Use a query against the systables table to generate the sql
syntax to perform the alter statement.
Careful with long, 128 character names and
ping
Simple example shown, you can do most anything limited
only by your understanding of the system tables
generate
wra
similar stored procedures and triggers
3/6/2002
Slide 26
Generating SQL using SQL
The Generating Query:
output to addcolumn.sql without headings
select "alter table "||tabname,
"add chg_dte date;"
from systables
where tabid > 99 and tabtype = "T";
The result (addcolumn.sql)
alter table code_typ_dm
add chg_dte date;
.
alter table lookup_dm
add chg_dte date;
(and so on …)
3/6/2002
Slide 27
Group by
Task to create a table with id, subid, zip5 and name from
a wider table to lookup names
Multiple names exist, need to get only one
id
subid zip5 type name
010211494 0001 04240 46
CENTRAL MAINE
010211494 0001 04240 10
CENTRAL MAINE
010211494 0001 04240 70
CENTRAL MAINE
First Try
select id, subid, zip5, name
from storenames
group by 1,2,3
#
^
# 294: The column (name) must be in the
SERVICE
MOTORS CTR
PARTS
GROUP BY list.
3/6/2002
Slide 28
Group by
Second Try-Wrong Name!
select id, subid, zip5, (min)name
from storenames
group by 1,2,3
id
subid zip5
010211494 0001 04240
(min)
CENTRAL MAINE PARTS
New info, best single name associated with
select id, subid, zip5, min(type||name)
from storenames
group by 1,2,3
lowest type
id
subid zip5 (min)
010211494 0001 04240 10CENTRAL MAINE SALES CTR
Can strip off the “10” later using [3,43]
3/6/2002
Slide 29
Summary
case
decode
nvl
first
middle
directives
external tables
temp tables
making sql using sql
concatenating group by
3/6/2002
Slide 30
Suggestions
Submit your tips ([email protected],
[email protected] )
Will be placed on WAIUG site
Indexed by “How do I”?
Thank You !!!
3/6/2002
Slide 31