|
By Column
Into Equal Partitions
By Column into Equal Partitions
Using a Validation Column
Using Expressions
Generate Environment File Entry
Indexes
Partitioning
1. Analysis
2. Partition the Table
3. Environment File Entry
4. Indexing
Examples
|
Example - Environment File
 Other
Partitioning Examples
Environment
File
The following example shows partitioning a table using the ODXSQL PARTITION
command and the environment file entry (below) generated
for the partition using the ENVIRONMENTSNIPPET=filename WITH option.
>partition orders into 5 by product_no in dat with environmentsnippet=ordenv.src
Partitioned 200 rows into 5 partitions
>view partition.rpt
| OmniAccess Partitioning Report
|
Database:
Table:
Approach: |
Star:
Orders:
200 rows split by PRODUCT_NO into 5 equal partitions
|
Partition Value
----------------- |
Cardinality
------------- |
Valid
------- |
Total %
--------- |
Valid %
--------- |
ORDERS01
H672
H100
A940
HL6P
MPOP |
40
9
9
8
8
6 |
|
20.000%
4.500%
4.500%
4.000%
4.000%
4.000% |
20.000%
0.000%
0.000%
0.000%
0.000%
0.000%
|
ORDERS02
G523
DM20
P900
G520
IC9S
T750 |
39
7
7
7
7
6
5 |
|
19.500%
3.500%
3.500%
3.500%
3.500%
3.000%
2.500% |
20.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
|
ORDERS03
B290
B390
B455
P225
HO30
H400
P224
B190
BRSP
|
40
5
5
5
5
5
5
4
4
2
|
|
20.000%
2.500%
2.500%
2.500%
2.500%
2.500%
2.500%
2.000%
2.000%
1.000%
|
20.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
|
ORDERS04
H736
N820
N972
PH65
S100
OSER
HPMP
EDMP
DM23
C478
3MTP
GEHE |
40
4
4
4
4
4
3
3
3
3
3
3
2 |
|
20.000%
2.000%
2.000%
2.000%
2.000%
2.000%
1.500%
1.500%
1.500%
1.500%
1.500%
1.500%
1.000% |
20.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000% |
ORDERS05
P160
L722
HLTC
L433
3SAD
MTAC
3DST
PBSP
SCFP
CGPP
BTCT
SSST
SWFL
AEWP
OSTC
AQPP
GTLC
3RGS
3P23
BMLF
PDMP
PGMP
BMED
XPCP
S25P
SCDR
ACPC
ADLB
AAWP
GUOC
MMRB |
41
3
3
2
2
2
2
2
2
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1 |
|
20.500%
1.500%
1.500%
1.000%
1.000%
1.000%
1.000%
1.000%
1.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000% |
20.500%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000%
0.000% |
-----------------
Total |
-------------
200 |
------- |
---------
100.00% |
---------
100.00% |
End of report |
|
|
|
---------------- End of file -------------- |
> view ordenv.src
| table
|
"ORDERS" |
|
|  as
|
"select * from ORDERS01
union all
select *
from ORDERS02 union all
select *
from ORDERS03 union all
select *
from ORDERS04 union all
select *
from ORDERS05" |
| column "ACCT"
column "PRODUCT_NO"
column "ORDER_DATE"
column "STATUS"
column "TAX_STATE"
column "SOURCE"
column "PMT_METHOD"
column "DISCOUNT"
column "QUANTITY"
column "SALES_TAX"
column "AMOUNT"
column "TOTAL" |
datatype INTEGER
datatype CHARACTER
datatype OMNIDEX DATE
datatype CHARACTER
datatype CHARACTER
datatype INTEGER
datatype INTEGER
datatype INTEGER
datatype INTEGER
datatype FLOAT
datatype FLOAT
datatype FLOAT
|
length 4
length 4
length 4 format YYYYMMDD
length 2
length 2
length 1
length 1
length 1
length 1
length 4
length 4
length 4 |
| table
physical
partition by
|
"ORDERS01"
"dat\orders01.dat"
"PRODUCT_NO in ('H672','H100','A940','HL6P','MPOP')" |
| foreign "ACCT"
foreign "PRODUCT_NO"
foreign "ORDER_DATE"
foreign "STATUS"
foreign "TAX_STATE"
foreign "SOURCE"
foreign "PMT_METHOD" |
references "PROSPECTS"
("ACCT")
references "PRODUCTS" ("PRODUCT_NO")
references "DATES" ("DT")
references "STATUSES" ("STATUS")
references "STATES" ("STATE")
references "SOURCES" ("SOURCE")
references "PMT_METHODS" ("PMT_METHOD") |
| table
physical
partition by
|
"ORDERS02"
"dat\orders02.dat"
"PRODUCT_NO in ('G523','DM20','P900','G520','IC9S','T750')" |
| foreign "ACCT"
foreign "PRODUCT_NO"
foreign "ORDER_DATE"
foreign "STATUS"
foreign "TAX_STATE"
foreign "SOURCE"
foreign "PMT_METHOD" |
references "PROSPECTS"
("ACCT")
references "PRODUCTS" ("PRODUCT_NO")
references "DATES" ("DT")
references "STATUSES" ("STATUS")
references "STATES" ("STATE")
references "SOURCES" ("SOURCE")
references "PMT_METHODS" ("PMT_METHOD") |
| table
physical
partition by
|
"ORDERS03"
"dat\orders03.dat"
"PRODUCT_NO in ('B290','B390','B455','P225', 'HO30','H400','P224','B190','BRSP')" |
| foreign "ACCT"
foreign "PRODUCT_NO"
foreign "ORDER_DATE"
foreign "STATUS"
foreign "TAX_STATE"
foreign "SOURCE"
foreign "PMT_METHOD" |
references "PROSPECTS"
("ACCT")
references "PRODUCTS" ("PRODUCT_NO")
references "DATES" ("DT")
references "STATUSES" ("STATUS")
references "STATES" ("STATE")
references "SOURCES" ("SOURCE")
references "PMT_METHODS" ("PMT_METHOD") |
| table
physical
partition by
|
"ORDERS04"
"dat\orders04.dat"
"PRODUCT_NO in ('H736','N820','N972','PH65','S100','OSER',
'HPMP','EDMP','DM23','C478','3MTP','GEHE')" |
| foreign "ACCT"
foreign "PRODUCT_NO"
foreign "ORDER_DATE"
foreign "STATUS"
foreign "TAX_STATE"
foreign "SOURCE"
foreign "PMT_METHOD" |
references "PROSPECTS"
("ACCT")
references "PRODUCTS" ("PRODUCT_NO")
references "DATES" ("DT")
references "STATUSES" ("STATUS")
references "STATES" ("STATE")
references "SOURCES" ("SOURCE")
references "PMT_METHODS" ("PMT_METHOD") |
| table
physical
partition by
|
"ORDERS05"
"dat\orders05.dat"
"PRODUCT_NO in ('P160','L722','HLTC','L433','3SAD','MTAC','3DST',
'PBSP','SCFP','CGPP','BTCT','SSST','SWFL','AEWP',
'OSTC','AQPP','GTLC', '3RGS','3P23','BMLF','PDMP',
'PGMP','BMED','XPCP','S25P','SCDR','ACPC','ADLB',
'AAWP','GUOC','MMRB')" |
| foreign "ACCT"
foreign "PRODUCT_NO"
foreign "ORDER_DATE"
foreign "STATUS"
foreign "TAX_STATE"
foreign "SOURCE"
foreign "PMT_METHOD" |
references "PROSPECTS"
("ACCT")
references "PRODUCTS" ("PRODUCT_NO")
references "DATES" ("DT")
references "STATUSES" ("STATUS")
references "STATES" ("STATE")
references "SOURCES" ("SOURCE")
references "PMT_METHODS" ("PMT_METHOD") |
Top
|
