Solved

oracle query tune joining date field to timestamp on date

Posted on 2014-11-26
4
564 Views
Last Modified: 2014-11-26
Hi I am wondering what the best way is to join to tables on different field types. I want to join the 2 tables on date and symbol. So far i am using trunc() like so. I thought that the trunc function was actually invalidating the index that is on the trade_time from what i was told in the past. Just looking to see if there is a better way to be joining the tables as one is a timestamp and the other a date field. thanks. I'm on Oracle 11g

select *
from table_a a, table_b b
where trunc(a.trade_time) = b.trade_date
and a.symbol = b.symbol



Table_A

trade_time           -- timestamp (index)
symbol                 -- varchar2(index)
volume                 --number

Table_B
trade_date           -- date (index)
symbol                 -- varchar2 (index)
volume                 -- number
0
Comment
Question by:Extreme66
  • 2
4 Comments
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
Could you provide us the table structure, indexes and exec plans if possible?!
Are there 2 indexes upon those 2 columns or a composite index?
What do you mean by "invalidating the index"?! In general, the CBO won't use an index whenever the corresponding columns are being accessed with the help of a function (unless you have defined a FBI)...
0
 

Author Comment

by:Extreme66
Comment Utility
hi, this is more of a theoretical question than anything, the tables i typically use this on are with composite indexes on both trade_date  and symbol or trade_time and symbol. and i  think you explained what i was trying to say, its not that its invalidating the index, but rather the indexes aren't being used when i utilize the trunc() function
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
You can create a Function-Based index on the TRUNC(column).  Then the trunc calls will likley use the index.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
A split would have been more appropriate...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now