Hi all,
I have one large table(10 GB-13Million rows) which i would like to convert to a parition table.I am on 10.2.0.3 running on sun solaris 5.9
Folllowing is table structure:
CREATE TABLE XYZ
(
TICKER VARCHAR2(22 BYTE) NOT NULL,
EXCH_CODE VARCHAR2(25 BYTE) NOT NULL,
ID_ISIN VARCHAR2(12 BYTE),
HIGH_52WEEK NUMBER(28,10),
LOW_52WEEK NUMBER(28,10),
PX_OPEN NUMBER(28,10),
PX_HIGH NUMBER(28,10),
BLOOMBERG_FILE_SOURCE VARCHAR2(100 BYTE),
LATEST_VERSION_FLAG CHAR(1 BYTE),
)
PARTITION BY LIST (EXCH_CODE)
(
PARTITION BBO_ASIA VALUES ('SL','IS','SP','JF','JN','PK','KP','VM','JS','IN','TB','KQ','JP','NV','JJ','MK','HK','IJ','JT','TT','PA','CS','JX',
'IB','AU','FS','VN','NZ','KS','PM','CH','BD','JQ','VH','CG','JO')
,
PARTITION BBO_NAMR VALUES ('UO','US','UN','PQ','TR','UD','UP','TX','UL','UB','UU','UX','UT','TN','UQ','UR','UW','UV','TA','CT','CV','UC','CJ','UA','UM','CN','UF','CF')
,
PARTITION BBO_LAMR VALUES ('AR','BS','AC','CR','EG','EK','VB','BN','EQ','PE','AF','CX','KY','CC','MM','BM','TP','BV','BH','UY','BZ','ED','VC','VS','BO','CI','CB','PP','BA','JA','CE')
,
PARTITION BBO_EURO VALUES (DEFAULT)
);
Most of the queries on this tables are filtered like :
WHERE LATEST_VERSION_FLAG='Y' and EXCH_CODE in (long list of exchanges);
}
We have 5 different regions (continent wise:ASIA1,ASIA2,EURO,LATIN AMERICA,NORTH AMERICA Etc) and exch_codes are filtered for a perticular region.Though there is no separate column like REGION in table.
There are large numbers of exchanges in a pertucular region.
ASIA1-36
ASIA2-29
EURO-139
LAMR-43
NAMR-69
Here are data distribution:
select count(*) from xyz partition (BBO_ASIA);
COUNT(*)
----------
1856036
select count(*) from xyz partition (BBO_NAMR);
COUNT(*)
----------
6308692
select count(*) from xyz partition (BBO_LAMR);
COUNT(*)
----------
204498
select count(*) from xyz partition (BBO_EURO);
COUNT(*)
----------
4971417
I tried to create LIST partiton on EXCH_CODE,but did not observe any performance gain.Latest_version_flag has two values ('Y' AND 'N').
I have created local indexes on both EXCH_CODE and LASTET_VERSION_FLAG col.
I used following query:
explain plan for
SELECT
* from XYZ
WHERE exch_code IN ('UA','UW','UQ','UR','UN','UP','CT') and latest_version_flag='Y';
select * from table(dbms_xplan.display());
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 313K| 110M| 76119 (3)| 00:15:14 | | |
| 1 | PARTITION LIST INLIST| | 313K| 110M| 76119 (3)| 00:15:14 |KEY(I) |KEY(I) |
|* 2 | TABLE ACCESS FULL | XYZ | 313K| 110M| 76119 (3)| 00:15:14 |KEY(I) |KEY(I) |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("EXCH_CODE"='CT' OR "EXCH_CODE"='UA' OR "EXCH_CODE"='UN' OR "EXCH_CODE"='UP' OR
"EXCH_CODE"='UQ' OR "EXCH_CODE"='UR' OR "EXCH_CODE"='UW') AND "LATEST_VERSION_FLAG"='Y')
15 rows selected.
Intention for partitioning is to have pruning and reduce queries elapsed time.I believe,run time pruning is happening {KEY(I)}, but there is no change in elapsed time.
Table stats are gathered lon weekly basis.And histograms are created on both of these columns.
Table is being analyzed as :
EXECUTE DBMS_STATS.GATHER_TABLE_STATS('ED','XYZ',granularity=>'all',METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE AUTO',CASCADE=>TRUE,DEGREE=>8,NO_INVALIDATE=>FALSE);
Please consider issues caused by object stats out of scope for this discussion, as core intention of this thread is to have proper paritioning design.
Regards,
Bhavik Desai