This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
dev:sql:examples:home [2011/04/22 16:34] admin |
dev:sql:examples:home [2012/10/26 14:57] (current) |
||
|---|---|---|---|
| Line 11: | Line 11: | ||
| * Update Indexes Statements | * Update Indexes Statements | ||
| * Select Specials (SAMPLE, RANDOM, EVERY) | * Select Specials (SAMPLE, RANDOM, EVERY) | ||
| + | * CREATE DATABASE Statements | ||
| + | * Connect Statements | ||
| ===== Extract Omnidex DDL from existing Omnidex Environment ===== | ===== Extract Omnidex DDL from existing Omnidex Environment ===== | ||
| Line 18: | Line 20: | ||
| odxsql> EXTRACT DDL to myfile.sql; | odxsql> EXTRACT DDL to myfile.sql; | ||
| </code> | </code> | ||
| + | |||
| + | EXTRACT DDL to myfile.sql WITH DELETE; | ||
| + | |||
| + | ===== CONNECT WITH MAINTENANCE ===== | ||
| + | |||
| + | The WITH MAINTENANCE simply means the same as exclusive access. That is needed to execute the “INSTALL INDEXES” command, which resynchronizes the actual indexes with the XML file. | ||
| + | |||
| + | ===== Partition ===== | ||
| + | |||
| + | > partition companies by "case when state in ('CA','CO','TX') then '01' when state in ('NY','FL','WA') then '02' else '03' end" ; | ||
| + | Partitioned 31,548 rows from COMPANIES into 3 partitions | ||
| + | |||
| + | |||
| + | |||
| + | <code> | ||
| + | partition households by "case when state in ('CA','CO','TX') then '01' when state in ('AZ','NM','UT') then '02' else '03' end" | ||
| + | Partitioned 1,909 rows from HOUSEHOLDS into 3 partitions | ||
| + | > dir | ||
| + | HOUSEHOLDS_01.dat | ||
| + | HOUSEHOLDS_02.dat | ||
| + | HOUSEHOLDS_03.dat | ||
| + | |||
| + | > partition households by state ; | ||
| + | Partitioned 1,909 rows from HOUSEHOLDS into 52 partitions | ||
| + | > dir | ||
| + | HOUSEHOLDS_AK.dat | ||
| + | HOUSEHOLDS_AL.dat | ||
| + | HOUSEHOLDS_AR.dat | ||
| + | HOUSEHOLDS_AZ.dat | ||
| + | HOUSEHOLDS_CA.dat | ||
| + | HOUSEHOLDS_CO.dat | ||
| + | HOUSEHOLDS_CT.dat | ||
| + | HOUSEHOLDS_DC.dat | ||
| + | HOUSEHOLDS_DE.dat | ||
| + | HOUSEHOLDS_FL.dat | ||
| + | HOUSEHOLDS_GA.dat | ||
| + | HOUSEHOLDS_HI.dat | ||
| + | HOUSEHOLDS_IA.dat | ||
| + | HOUSEHOLDS_ID.dat | ||
| + | HOUSEHOLDS_IL.dat | ||
| + | HOUSEHOLDS_IN.dat | ||
| + | HOUSEHOLDS_KS.dat | ||
| + | HOUSEHOLDS_KY.dat | ||
| + | HOUSEHOLDS_LA.dat | ||
| + | … | ||
| + | </code> | ||
| + | |||
| + | ===== Create DATABASE with environment variables ===== | ||
| + | Create database "star" | ||
| + | type oracle | ||
| + | version "{$ORAVER}" | ||
| + | userclass "DEFAULT" user "{star$(basename $PWD)}" | ||
| + | password "{star$(basename $PWD)}" | ||
| + | indexprefix "idx/STAR" | ||
| ===== Export using Oracle NLS date ===== | ===== Export using Oracle NLS date ===== | ||
| Line 64: | Line 120: | ||
| update indexes with log=odxlogs; | update indexes with log=odxlogs; | ||
| + | |||
| + | Update Indexes with limiting the rows being read and indexed per table. | ||
| + | <code> | ||
| + | os> odxsql | ||
| + | > connect myenv.xml; | ||
| + | > create directory odxlogs; | ||
| + | > setenv oa_max_rows=100000; | ||
| + | > update indexes with log=odxlogs; | ||
| + | > setenv oa_max_rows | ||
| + | > update indexes with log=odxlogs; | ||
| + | > exit | ||
| + | os> | ||
| + | </code> | ||
| + | |||
| + | |||
| + | UPDATE INDEXES WITH BUF=n | ||
| + | |||
| + | N is the number of MB to use in the build, and coincides with the BUF= in the build command in dbinstal. | ||
| + | |||
| + | |||
| ===== $CONTAIN Function ===== | ===== $CONTAIN Function ===== | ||