| Index [message #331333] |
Thu, 03 July 2008 00:56  |
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  |
Frank
Messages: 7901
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 |
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 |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
