Solved

oracle query tune joining date field to timestamp on date

Posted on 2014-11-26
4
578 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]
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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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 13

Expert Comment

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

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

775 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