Home » SQL & PL/SQL » SQL & PL/SQL » regarding index & partition.
regarding index & partition. [message #264421] Mon, 03 September 2007 04:45 Go to next message
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 #264422 is a reply to message #264421] Mon, 03 September 2007 04:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68707
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Becasue i have explain plan for this statement it is showing index.

And why don't you post it for us?

Create a local index for your primary key.

Regards
Michel
Re: regarding index & partition. [message #264431 is a reply to message #264422] Mon, 03 September 2007 05:21 Go to previous messageGo to next message
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.
Re: regarding index & partition. [message #264510 is a reply to message #264431] Mon, 03 September 2007 10:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68707
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How we can identify whether the optimizer has considered only p1 parition.

Either use explain plan and dbms_xplan.display to display your plan or enable sql_trace and use tkprof to analyze it.
Post your Oracle version (always post it it is a prerequisite to get an accurate answer).

Regards
Michel

Re: regarding index & partition. [message #264759 is a reply to message #264421] Tue, 04 September 2007 08:35 Go to previous messageGo to next message
ramabhupalrr
Messages: 69
Registered: July 2005
Location: India
Member

HI Michel,

My Oracle version 9.2.0.1.0

Those statements i ran in sql*plus using the
set autotrace traceonly explain

i guess it will give the explain plan.

Thanks for your help.

Thanks,
Rama Bhupal
Re: regarding index & partition. [message #264762 is a reply to message #264759] Tue, 04 September 2007 08:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68707
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use what I said. It gives you more information.

Regards
Michel
Re: regarding index & partition. [message #264883 is a reply to message #264762] Tue, 04 September 2007 22:20 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
More precisely - the technique Michel described will tell you which partitions will be scanned.

Ross Leishman
Re: regarding index & partition. [message #265401 is a reply to message #264421] Thu, 06 September 2007 04:41 Go to previous message
ramabhupalrr
Messages: 69
Registered: July 2005
Location: India
Member

Hi Michel,

You are right and thank you for your help. Now i am able to see In Pstart and Pstop columns 
of explain plan which partition it is considering.

Thank You,
Rama Bhupaln

[Updated on: Thu, 06 September 2007 05:15] by Moderator

Report message to a moderator

Previous Topic: How to alter CLOB column to VARCHAR column?
Next Topic: Randomly failing procedure
Goto Forum:
  


Current Time: Thu Nov 07 23:34:11 CST 2024