This is a question about performance tuning for tuning.
There is currently one table in which there are two date types (TIMESTAMP(6) with local timezobe).
When I write SQL, those two dates need to match, so for example, I write as follows.
The Query Plan will always be Full Table Access, increasing SQL costs.
I added the index as below, but there is no change in the status of the table full scan.
How can I prevent Table Full Scan in this situation?sql oracle
I think it's appropriate to have a full scan because both the left and right queries in the question statements can only be determined by looking at the records.
You can determine if
date2 are equivalent in the virtual column and index this virtual column to make a scan using an index.
(However, I couldn't read from the question how the title "Join" relates to this query.)
How can I prevent Table Full Scan in this situation?
This is an ad hoc (but expected) response, but you can force the optimizer to use the index with INDEX Tip.
Another possible response is to stock a trigger that works when a record is updated, then flag the update record by determining if date1 and date2 are the same, or to add/remove keys to another table for the update record.
Some operations may also be useful to perform the above actions on records that have more than a certain update date before processing the query.
If you want to throw that query over and over again, you can also update the physicalized view with the date1 and date2 values limited to the same record before starting the process.
© 2022 OneMinuteCode. All rights reserved.