2 Replies Latest reply: Nov 20, 2012 9:52 AM by Keith Jamieson RSS

    Dynamic partition use

    a.stoyanov
      Hi all,
      In my 11g Oracle i have the following issue:
      create table TEST
      (
        COL_DATE DATE
      )
      partition by range (COL_DATE)
      (
        partition TEST2012 values less than (TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace NRA_DWH_DATA
          pctfree 10
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          )
      );
      insert into  test values (sysdate);
      .... and ...

      declare 
      a varchar2(20);
      b date;
      
      begin 
        
        a := 'TEST2012';
        
        
        select col_date into b from test partition(a) ;
      
      
      
      end ;
      /
      when i execute this i receive the following error:
      ORA-02149: Specified partition does not exist
      Any ideas?


      Thanks in advance,
      Bahchevanov.

      Edited by: a.stoyanov on 2012-11-20 7:42
        • 1. Re: Dynamic partition use
          Dom Brooks
          You can't bind in the partition name.
          If you really need to do this then you'd have to use dynamic sql.

          e.g.
          declare 
           a varchar2(20);
           b date;
          begin 
            a := 'TEST2012';
            execute immediate 'select col_date from test partition('||a||')' into b;
          end ;
          / 
          • 2. Re: Dynamic partition use
            Keith Jamieson
            Yes, you can not do that.


            You should just use the following:
            select col_date into b from test
            However this will cycle through all partitions because you have not given the optimiser any help whatsoever.


            If you said
            --explain plan for
            select col_date from test where col_date >=to_date('01-01-2012','DD-MM-YYYY') 
            and col_date < to_date('01-01-2013','DD-MM-YYYY') 
            then the optimiser will use the partition.



            To see this just remove '--' from before explain plan for '
            and then
            select * from table(dbms_xplan.display());
            You should see the partition in your output.

            Your previous query will show it is iterating through all the partitions.

            Edited by: Keith Jamieson on Nov 20, 2012 3:52 PM
            removed into