ORACLE sql performance tuning. There are two date types of items in one table, and I would like to create an effective index to join them.

Asked 2 months ago, Updated 2 months ago, 5 views

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.

 select * from table1 where date1 = date2;

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.

CREATE INDEX table1_IDX on table1 (date1ASC, date2ASC)

How can I prevent Table Full Scan in this situation?

sql oracle

2022-09-30 14:04

2 Answers

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 date1 and date2 are equivalent in the virtual column and index this virtual column to make a scan using an index.

alter table table 1 add(
  same_date number(1)
  generated always as
      when date1 = date2then1
  ) virtual

create bitmap index idx_same_dateontable1(same_date);

select * from table1 where same_date = 1;

(However, I couldn't read from the question how the title "Join" relates to this query.)

2022-09-30 14:05

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.

       * from table1 where date1 = date2;

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-09-30 14:05

If you have any answers or tips

© 2022 OneMinuteCode. All rights reserved.