Solved

oracle query tune joining date field to timestamp on date

Posted on 2014-11-26
4
590 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 77

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
help on oracle query 5 53
PL/SQL Two changes 7 34
Trying to get a Linked Server to Oracle DB working 21 70
Oracle function to insert records? 15 48
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

830 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