Home » SQL & PL/SQL » SQL & PL/SQL » Index (Oracle 10g)
Index [message #331333] Thu, 03 July 2008 00:56 Go to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
Dear All,
Am having index on date column which contains date and time format.When i use the below query oracle will not use the index even though i have index on this column.so,What needs to be done for such type of scenario to use index.
select * from kcp_dtls where to_date(KIT_ACTIVATION_DATE,'dd-Mon-yyyy') = '20-Apr-2008'.
Re: Index [message #331338 is a reply to message #331333] Thu, 03 July 2008 01:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you use a function on an indexed column, Oracle will no longer be able to use the index.

By the way, your logic is totally flawed:
You have a column with datatype DATE, so you should not do a TO_DATE on it. Then you compare the result of a TO_DATE, which is a date, to a string.

Either use to_char on the date-column to compare it to the string '20-APR-2008', which will render the index unusable, or compare the date itself.
Use where <your_date_column> between to_date('20-04-2008 00:00:00', 'dd-mm-yyyy hh24:mi:ss') and to_date('20-04-2008 23:59:59', 'dd-mm-yyyy hh24:mi:ss')
Re: Index [message #331343 is a reply to message #331333] Thu, 03 July 2008 01:28 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Even after 57 posts you are not having enough time to read the forum guidelines and format your post.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> create table test (date_val date, sno number);

Table created.

SQL> insert into test 
  2  select sysdate + level/(60*60) , level from dual connect by level <= 300;

300 rows created.

SQL> commit;

Commit complete.


SQL> select trunc(date_val,'hh24') , count(*) from test
  2  group by trunc(date_val,'hh24');

TRUNC(DATE_VAL,'HH24   COUNT(*)
-------------------- ----------
03-JUL-2008 08:00:00        150
03-JUL-2008 09:00:00         24
03-JUL-2008 07:00:00        126


SQL> explain plan
  2  for
  3  select * from test where trunc(date_val,'hh24') = '03-jul-2008';

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |    33 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     3 |    33 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter(TRUNC(INTERNAL_FUNCTION("DATE_VAL"),'fmhh24')='03-jul-2008
              ')

14 rows selected.

SQL> explain plan
  2  for
  3  select * from test where date_val between to_date('03-jul-2008','dd-mon-yyyy') and to_date('04-jul-2008','dd-mon-yyyy') - interval '1' second;

Explained.

SQL> /

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1389866015

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |   300 |  3300 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |   300 |  3300 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX |   300 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("DATE_VAL">=TO_DATE(' 2008-07-03 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "DATE_VAL"<=TO_DATE(' 2008-07-03 23:59:59', 'syyyy-mm-dd
              hh24:mi:ss'))

16 rows selected.


Refer this link for more information.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_5010.htm#SQLRF01209
Quote:
select * from kcp_dtls where to_date(KIT_ACTIVATION_DATE,'dd-Mon-yyyy') = '20-Apr-2008'.

BTW, why do you use a to_date on a date column and anything given in single quotes is a string and not a date. I hope you understand that.

Regards

Raj
Re: Index [message #331346 is a reply to message #331338] Thu, 03 July 2008 01:29 Go to previous messageGo to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
Even if i use to_char oracle will not use the index.
select * from kcp_dtls where to_char(KIT_ACTIVATION_DATE,'dd-Mon-yyyy') = '10-Apr-2008'.
so, what needs to be done so that oracle use index.
Re: Index [message #331350 is a reply to message #331346] Thu, 03 July 2008 01:35 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you even READ the previous two replies?
Previous Topic: query with rank function
Next Topic: Data Dictionary
Goto Forum:
  


Current Time: Fri Dec 09 17:49:42 CST 2016

Total time taken to generate the page: 0.25495 seconds