BLOCKS        570227
EMPTY_BLOCKS  3213
AVG_SPACE     975
AVG_ROW_LEN   139
LAST_ANALYZED 11/10/2007 9:23:44 PM
PARTITIONED   YES

PART_LATEST_N    21658507
PART_LATEST_Y     6899650

CREATE INDEX BNM_FI ON BUSINESS_NAME 
(UPPER("NAME_VALUE_TX"))
  INITRANS   2
  MAXTRANS   255
LOCAL (  
  PARTITION PART_LATEST_N
    LOGGING
    NOCOMPRESS
    TABLESPACE LATESTN
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION PART_LATEST_Y
    LOGGING
    NOCOMPRESS
    TABLESPACE LATESTY
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                BUFFER_POOL      KEEP
               )
)
NOPARALLEL;

  CREATE INDEX TX_ID ON BUSINESS_NAME
   (NAME_VALUE_TX)
   INDEXTYPE IS CTXSYS.CONTEXT;

SELECT name_value_tx
  FROM business_name bnm
 WHERE bnm.latest_in = 'Y'
   AND contains (bnm.name_value_tx, 'IBM') > 0


SELECT name_value_tx
  FROM business_name bnm
 WHERE bnm.latest_in = 'Y'
   AND upper(bnm.name_value_tx) like 'IBM%' 

CREATE INDEX BNM_FI ON BUSINESS_NAME 
(UPPER("NAME_VALUE_TX"))