| Function based indexes [message #371935] |
Fri, 22 December 2000 11:12  |
Jurek
Messages: 2
Registered: December 2000
|
Junior Member |
|
|
I created function based index (on: TO_CHAR(a.visit_dt,'yyyymm'). When I am trying to run the following query it does not use the index. Adding hint also did not help. Query is doing full scan on mainactivity table.
Query:
SELECT /*+ index(a mainact_visitdt_fndx) */
a.mainactivity_seq, a.registration_seq ,a.contactmode_no
FROM accs_tgt.MAINACTIVITY a
WHERE TO_CHAR(a.visit_dt,'yyyymm') = '200008'
AND NOT EXISTS (SELECT * FROM
accs_rpt.accsvisit
WHERE mainactivity_seq = a.mainactivity_seq)
Thanks for help,
Jurek
|
|
|
|
|
|
| Re: Function based indexes [message #371959 is a reply to message #371935] |
Wed, 27 December 2000 09:06  |
Jurek
Messages: 2
Registered: December 2000
|
Junior Member |
|
|
ORACLE 8.1.7 that I am using allows for function based indexes. This option can be used in oracle 8i (I am not sure if all version have it).
'NOT EXISTS' clause does not prevent using indexes. I tried runnining the same query with WHERE clause like this:
...
WHERE load_id = 2222
AND NOT EXISTS (....
Of course there is index on load_id column.
It is using indexes without any problem. If in doubt wheather query is using index you can always check it by using Explain Plan.
Jurek
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
