CODEWITHSUNDEEP
sqloracle-database
sbrbot
sbrbot

Reputation: 6447

Compound index for columns with OR operator

I have two tables with two matching columns (c1,c2);

TABLE_A    TABLE_B
=======    =======
id         id
c1         c1
c2         c2
double     col_y
           col_z

and in my query I need to know whether records are matched per column c1 OR c2 like:

UPDATE table_a a SET double='Y'
 WHERE EXISTS (SELECT * 
                 FROM table_b b 
                WHERE a.c1=b.c1 OR a.c2=b.c2);

Now I have one index for column c1 and one index for column c2.

In order to speed up the query, can I create compound index from columns c1 and c2 since I search c1 OR c2 (not c1 AND c2)?

Upvotes: 2

Views: 80

Answers (2)

XING
XING

Reputation: 9886

Yes you can create a compound index..!!

Compound indexes are most useful when queries commonly use all (or at least most) of the columns in the index. They can also be especially useful if the data being retrieved is contained within the index. For example in the below Select Statement:

SELECT c1, c2
FROM   sometable
WHERE  c1 = somevalue;

If you had individual indexes on c1 and c2, Oracle would access the index for c1 then use the records from the index to pull in the required table blocks. The c2 index would not be used at all. If there was a compound index on c1 and c2, then the optimizer would almost certainly scan the index and use the index blocks to return the data -- never touching the table.

The more columns in a compound index, the more expensive they are to scan. If the queries against that table do not routinely use most/all of the columns in the index, it will likely be used less than a corresponding single-column indices on the individual columns.

Upvotes: 1

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

It's not possible to create that index. But you should do rewrite your query to two separate query. 

UPDATE table_a a SET double='Y'
 WHERE EXISTS (SELECT 1
                 FROM table_b b 
                WHERE a.c1=b.c1                
                  union all 
                SELECT 1
                 FROM table_b b 
                WHERE a.c2=b.c2);

There is good article about "or expansion" or expansion

Upvotes: 1

Related Questions