Home » SQL & PL/SQL » SQL & PL/SQL » regarding index & partition.
regarding index & partition. [message #264421] |
Mon, 03 September 2007 04:45 |
ramabhupalrr
Messages: 69 Registered: July 2005 Location: India
|
Member |
|
|
Hi All,
i have a table like this which is having indexes and also i partitioned the table.(primary key automatically creates indexes)
CREATE TABLE partition_by_test (
PROGRAM_ID VARCHAR2 (60) NOT NULL,
STATUS NUMBER NOT NULL,
YEAR NUMBER NOT NULL,
FIELD_ID NUMBER NOT NULL,
LABOR_EXPENSE NUMBER,
SWL_EXPENSE NUMBER,
FIELD_VALUE VARCHAR2 (2000),
S1S2MANAGER_FLAG VARCHAR2 (4) DEFAULT 'ENPP' NOT NULL,
CONSTRAINT partition_by_test
PRIMARY KEY ( YEAR,PROGRAM_ID, STATUS,FIELD_ID,S1S2MANAGER_FLAG )
)
partition by range(year)
(partition p1 values less than (2004),
partition p2 values less than (2006),
partition p3 values less than (2008),
partition p4 values less than (maxvalue))
My question is - How the optimizer will evaluate the access path for the following sql statement.
select * from npit_h_rscplan where year=2007 and PROGRAM_ID='02-1002.2'
Mean first it will take the partition or index in to the criteria. how to justify this? Becasue i have explain plan for this statement it is showing index.
Please can you explain me for this?
Thanks,
Rama Bhupal.
[Updated on: Mon, 03 September 2007 04:47] by Moderator Report message to a moderator
|
|
|
|
Re: regarding index & partition. [message #264431 is a reply to message #264422] |
Mon, 03 September 2007 05:21 |
ramabhupalrr
Messages: 69 Registered: July 2005 Location: India
|
Member |
|
|
Hi Michel,
Thank you for your instant reponse. Here is my work on this.
How we can identify whether the optimizer has considered only p1 parition. is there any way please let me know?
SQL> set autotrace traceonly explain
SQL> CREATE TABLE partition_by_test (
2 PROGRAM_ID VARCHAR2 (60) NOT NULL,
3 STATUS NUMBER NOT NULL,
4 YEAR NUMBER NOT NULL,
5 FIELD_ID NUMBER NOT NULL,
6 LABOR_EXPENSE NUMBER,
7 SWL_EXPENSE NUMBER,
8 FIELD_VALUE VARCHAR2 (2000),
9 S1S2MANAGER_FLAG VARCHAR2 (4) DEFAULT 'ENPP' NOT NULL,
10 CONSTRAINT PARTITION_BY_TEST
11 PRIMARY KEY ( YEAR,PROGRAM_ID, STATUS,FIELD_ID,S1S2MANAGER_FLAG )
12 )
13 partition by range(year)
14 (partition p1 values less than (2004),
15 partition p2 values less than (2006),
16 partition p3 values less than (2008),
17 partition p4 values less than (maxvalue));
Table created.
SQL> SELECT * FROM PARTITION_BY_TEST;
591771 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=255 Card=591771 Byte
s=15977817)
1 0 PARTITION RANGE (ALL)
2 1 TABLE ACCESS (FULL) OF 'PARTITION_BY_TEST' (Cost=255 Car
d=591771 Bytes=15977817)
SQL> SELECT * FROM partition_by_test
2 WHERE year=2002 and PROGRAM_ID='02-1002.2';
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=38 Bytes=950
)
1 0 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'PARTITION_BY_TEST
' (Cost=10 Card=38 Bytes=950)
2 1 INDEX (RANGE SCAN) OF 'PARTITION_BY_TEST' (UNIQUE) (Cost
=3 Card=38)
SQL> DROP TABLE PARTITION_BY_TEST;
Table dropped.
SQL>
SQL> CREATE TABLE partition_by_test (
2 PROGRAM_ID VARCHAR2 (60) NOT NULL,
3 STATUS NUMBER NOT NULL,
4 YEAR NUMBER NOT NULL,
5 FIELD_ID NUMBER NOT NULL,
6 LABOR_EXPENSE NUMBER,
7 SWL_EXPENSE NUMBER,
8 FIELD_VALUE VARCHAR2 (2000),
9 S1S2MANAGER_FLAG VARCHAR2 (4) DEFAULT 'ENPP' NOT NULL,
10 CONSTRAINT partition_by_test
11 PRIMARY KEY ( YEAR,PROGRAM_ID, STATUS,FIELD_ID,S1S2MANAGER_FLAG ) USING INDEX LOCAL
12 )
13 partition by range(year)
14 (partition p1 values less than (2004),
15 partition p2 values less than (2006),
16 partition p3 values less than (2008),
17 partition p4 values less than (maxvalue));
Table created.
SQL> SELECT * FROM PARTITION_BY_TEST;
591771 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=328 Bytes=361
784)
1 0 PARTITION RANGE (ALL)
2 1 TABLE ACCESS (FULL) OF 'PARTITION_BY_TEST' (Cost=2 Card=
328 Bytes=361784)
SQL> exec dbms_stats.gather_table_stats('TBNPI','PARTITION_BY_TEST',CASCADE=>TRUE,GRANULARITY=>'ALL');
PL/SQL procedure successfully completed.
SQL> SELECT * FROM PARTITION_BY_TEST WHERE YEAR=2002 AND PROGRAM_ID='02-1002.2
';
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=38 Bytes=950)
1 0 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'PARTITION_BY_TEST'
(Cost=6 Card=38 Bytes=950)
2 1 INDEX (RANGE SCAN) OF 'PARTITION_BY_TEST' (UNIQUE) (Cost
=3 Card=38)
SQL>
Thanks,
Rama Bhupal.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Nov 07 23:34:11 CST 2024
|