Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jul 12 '13 at 8:30

This question came from our site for professional and enthusiast programmers.

    
1. show us your table definition and execution plan. 2. execute the same query in SQL*Plus and check the timing. 3. you mean, 376, not 376000? –  be here now Jul 12 '13 at 6:00
    
@beherenow I have updated my question.... –  Kalyan Urimi Jul 12 '13 at 6:43
    
Why the jdbc tag? jdbc does not matter for database internals. –  TomTom Jul 12 '13 at 8:40

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.