| PARTITIONING [message #59150] |
Fri, 31 October 2003 11:33  |
Gopala K Tasupalli
Messages: 14
Registered: October 2002
|
Junior Member |
|
|
Hi
I want to partition one table depending on the STATE field (EXAMPLE : NJ, NY, PA, CA etc.,). can some one tell me the exact syntax? I know i can use LIST PARTITIONING to do this in ORACLE9i but i have only oracle 8i, can some one tell me how to do this in 8i?
Thanks in Advance
Gopal
|
|
|
|
| Re: PARTITIONING [message #59161 is a reply to message #59150] |
Sat, 01 November 2003 10:29  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Its easier with list partitioning. Do you have state_id too? Perhaps you could range partition on (state_name,state_id) ?
thiru@9.2.0:SQL>create table t(state_id integer,state_name varchar2(2)) partition by range(state_name,state_id)
2 ( partition p1 values less than ('AZ',2) ,
3 partition p2 values less than ('NY',3),
4 partition p3 values less than ('PA',4),
5 partition p4 values less than ('SC',5),
6 partition p5 values less than (MAXVALUE,MAXVALUE)
7 );
Table created.
thiru@9.2.0:SQL>set feedback off
thiru@9.2.0:SQL>insert into t values(1,'AZ');
thiru@9.2.0:SQL>insert into t values(2,'NY');
thiru@9.2.0:SQL>insert into t values(3,'PA');
thiru@9.2.0:SQL>insert into t values(4,'SC');
thiru@9.2.0:SQL>insert into t values(5,'NJ');
thiru@9.2.0:SQL>commit;
thiru@9.2.0:SQL>select * from t;
STATE_ID ST
---------- --
1 AZ
2 NY
5 NJ
3 PA
4 SC
thiru@9.2.0:SQL>set autotrace on explain
thiru@9.2.0:SQL>select
2 * from t where state_name='AZ';
STATE_ID ST
---------- --
1 AZ
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=16)
1 0 PARTITION RANGE (ITERATOR)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=16)
-- Following shows it does partition elimination. Pstart=1 , Pstop=2
thiru@9.2.0:SQL>explain plan for select * from t where state_name='AZ';
thiru@9.2.0:SQL>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 2 | |
| 1 | PARTITION RANGE ITERATOR| | | | | 1 | 2
|* 2 | TABLE ACCESS FULL | T | 1 | 16 | 2 | 1 | 2
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."STATE_NAME"='AZ')
Note: cpu costing is off
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 COLLECTION ITERATOR (PICKLER FETCH) OF 'DISPLAY'
--Creating a local index
thiru@9.2.0:SQL>create index t_idx on t(state_name,state_id) local;
thiru@9.2.0:SQL>analyze table t estimate statistics;
thiru@9.2.0:SQL>set feedback on
thiru@9.2.0:SQL>select * from t where state_name='PA';
STATE_ID ST
---------- --
3 PA
1 row selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=4)
1 0 PARTITION RANGE (ITERATOR)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=1 Bytes=4)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
