This discussion is archived
2 Replies Latest reply: Nov 20, 2012 7:52 AM by KeithJamieson RSS

Dynamic partition use

a.stoyanov Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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
    KeithJamieson Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points