Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

oracle query tune joining date field to timestamp on date

Posted on 2014-11-26
4
Medium Priority
?
662 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 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40466946
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
ID: 40466967
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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40467807
You can create a Function-Based index on the TRUNC(column).  Then the trunc calls will likley use the index.
0
 
LVL 14

Expert Comment

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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

963 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