How to optimize query? I need to optimize the following query:

SELECT DISTINCT column1, column2 
  FROM tbl_name 
 WHERE column3 = 'x'  
   AND TRIM(UPPER(column4)) = TRIM(UPPER('y')) 
   AND TRIM(UPPER(column5)) = TRIM(UPPER('z')) 
 ORDER BY column2


Does not have primary key. It contains only 5 columns. 

It is taking about 2 mins when I am using JDBC statement objects for executing this query.

Following are the statments in java:

sql="SELECT DISTINCT column1, column2 FROM tbl_name"+
     "WHERE column3 = 'x'"+  
       "AND TRIM(UPPER(column4)) = TRIM(UPPER('y'))"+ 
       "AND TRIM(UPPER(column5)) = TRIM(UPPER('z')) "+
     "ORDER BY column2"
stmt = conn.prepareStatement(sql);
stmt.executeUpdate();


Table Definition:

CREATE TABLE "NAME"."tbl_name"
  (
    "column1"   VARCHAR2(20 BYTE) NOT NULL ENABLE,
    "column2" VARCHAR2(100 BYTE),
    "column3"         VARCHAR2(50 BYTE),
    "column4"           VARCHAR2(25 BYTE),
    "column5"         VARCHAR2(50 CHAR)
  )
  SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )
  TABLESPACE "DATA" ;
CREATE INDEX "NAME"."tbl_name_IDX" ON "NAME"."tbl_name"
  (
    "column3",
    UPPER(TRIM("column4")),
    UPPER(TRIM("column5"))
  )
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )
  TABLESPACE "DATA" ;


Please suggest me.
The function based index you have created uses same functions but in reverse order than your query, this prevent oracle from using this index.

Since WHERE clause of your query contains:

   AND TRIM(UPPER(column4)) = TRIM(UPPER('y')) 
   AND TRIM(UPPER(column5)) = TRIM(UPPER('z')


you must create the index in this way:

CREATE INDEX "NAME"."tbl_name_IDX" ON "NAME"."tbl_name"
  (
    column3,
    TRIM(UPPER(column4)),
    TRIM(UPPER(column5))
  )


This still doesn't guarantee that your query will use that index,
that depends also on selectivity of the query, if the query is highly selective (returns only a few rows), then oracle will probably use the index, but when the query is not selective (returns thousands of rows), than the optimiser will probably prefer full table scan over using the index.