-- Create table
create table ESS.EXT_LOG
(
  CHARGE_TYPE NUMBER,
  ANUM        VARCHAR2( 50 ),
  CDATE       DATE,
  CDUR        DATE,
  CSUM        NUMBER,
  DIR_ID      NUMBER,
  DIR_NAME    VARCHAR2( 150 ),
  CNUM        VARCHAR2( 50 ),
  DOG_NMB_ID  NUMBER,
  PROV_ID     NUMBER,
  LDATE       DATE default sysdate,
  ATR         VARCHAR2( 10 ),
  INS_NMB     VARCHAR2( 10 ),
  DST_ROWID   ROWID,
  NAPR_ID     NUMBER,
  YEAR        NUMBER,
  MONTH       NUMBER,
  DAY         NUMBER
)
-- Create/Recreate indexes 
create index ESS.ALL_IND on ESS.EXT_LOG (CDATE, ANUM, CNUM, CDUR)
  tablespace USERS
  pctfree  10 
  initrans  2 
  maxtrans  255 
  storage
  (
    initial 64K
    minextents  1 
    maxextents unlimited
  )
  compress  1 ;

create bitmap index ESS.DATE_SIC on ESS.EXT_LOG (YEAR, MONTH, DAY)
  tablespace USERS
  pctfree  10 
  initrans  2 
  maxtrans  255 
  storage
  (
    initial 64K
    minextents  1 
    maxextents unlimited
  );

create bitmap index ESS.DM_BIT on ESS.EXT_LOG (TO_CHAR(CDATE,'rrmm'))
  tablespace USERS
  pctfree  10 
  initrans  2 
  maxtrans  255 
  storage
  (
    initial 64K
    minextents  1 
    maxextents unlimited
  );

create index ESS.INDCDURE on ESS.EXT_LOG (CDUR)
  tablespace USERS
  pctfree  10 
  initrans  2 
  maxtrans  255 
  storage
  (
    initial 64K
    minextents  1 
    maxextents unlimited
  );

create index ESS.INDCNUME on ESS.EXT_LOG (CNUM)
  tablespace USERS
  pctfree  10 
  initrans  2 
  maxtrans  255 
  storage
  (
    initial 64K
    minextents  1 
    maxextents unlimited
  );

create bitmap index ESS.INDDATEE on ESS.EXT_LOG (CDATE)
  tablespace USERS
  pctfree  10 
  initrans  2 
  maxtrans  255 
  storage
  (
    initial 64K
    minextents  1 
    maxextents unlimited
  );

select --+ index(ess.ext_log all_ind)
  лог.anum,
  лог.cnum,
  лог.cdate st_zv,
  лог.cdur dlit,
  лог.csum,
  NVL(to_char(лог.dir_id),'') dir_id,
  лог.dir_name
from ess.ext_log лог
where
  лог.prov_id= 2 --:P51_find_prov
  and лог.dog_nmb_id=DeCode(/*:P51_find_dog*/ 0 , 0 ,лог.dog_nmb_id,/*:P51_find_dog*/ 0 )
  and лог.cdate between to_date('01.10.2009 00:00:00','dd.mm.rrrr hh24:mi:ss') and to_date('02.11.2009 00:00:00','dd.mm.rrrr hh24:mi:ss')