Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

oracle query tune joining date field to timestamp on date

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses
Course of the Month15 days, 1 hour left to enroll

577 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