CODEWITHSUNDEEP
sqloracle-databasejoincomposite-indexfull-table-scan
Zaplatki
Zaplatki

Reputation: 35

Composite index is ignored when selecting unindexed columns (Oracle)

We have a simple join statement in which some of the when clauses may turn into is null. The statement is generated by an application. A problem with a query plan arises when we have this is null constraint.
We followed the approach described in the article on StackExchange and created a composite index for columns - nullable and the one we join on. It helps only if we select only indexed columns. If we select unindexed columns it is ignored, while the query result is the same - e.g., no rows selected.

The only option we see - to change the logic of the application, but may be there is still a way to solve this on db-level?

--Illustrative sample. Prepare tables and indexes:
create table tableA 
 (
  Acol1 varchar2(32) NOT NULL,
  Acol2 varchar2(32),
  Acol3 varchar2(32)
 );
insert into tableA (Acol1, Acol2, Acol3)
            values ('abcd1','abcd2A','abcd3A'); 

create table tableB 
 (
  Bcol1 varchar2(32) NOT NULL, 
  Bcol2 varchar2 (32),
  Bcol3 varchar2 (32)
 ); 
insert into tableB (Bcol1, Bcol2, Bcol3) 
values             ('abcd1','abcd2B','abcd3B'); 

create index tableA_col12 on tableA (acol1, acol2); 
create index tableB_col1 on tableB (Bcol1); 
commit;

Then we check the plans:

1.

 select a.Acol1 from tableA a join tableB b on a.Acol1 = b.Bcol1 where Acol2 is null; 
 --no rows selected

Plan1 - Range scan

2.

 select * from tableA a join tableB b on a.Acol1 = b.Bcol1 where Acol2 is null;
--no rows selected

Plan2 (same link above) - Full table scan

What would be the best way to improve performance: change the queries, use smarter indexes or by applying fixed plan?

*Update* While I was preparing this question, the plan for my sample changed by itself, now we have Plan2* instead of Plan2 - no Full table scan. However, if I recreate the sample (drop tables and prepare them again) - the plan is Plan2 again (Full table scan). This trick does not happen in actual DB.

Upvotes: 1

Views: 700

Answers (1)

Hogan
Hogan

Reputation: 70538

My comment was a bit flippant so I will try and give you some more detail. Here are some general tips for getting better at optimizing SQL systems and specific queries

  • First of all @GordonLinoff is right (as always) you won't get anything meaningful out of a tiny table. The optimizer knows and will work differently.

  • Second after you have a decent sized table (at least 50k rows depending on your memory) you need to make sure you run statistics on you tables or the optimizer (and the indexes) just won't work

  • Third you need to use the tools, learn how to understand an execution plan -- you can't get better at these techniques without having a deep understanding of what the system is telling you. Modern sql databases have tools that will look at a query and suggest indexes -- use them, just like the execution plan you can learn a lot. Remember, these tools are not foolproof you need to try out the suggestions and see if they work.

  • Finally, read a lot. One source that I think is particularly interesting is stackoverflow user Quassnoi who has a blog at explainextended. While not as active recently this blog (and many of his answers) are quite illuminating, I expect you will enjoy them. There are many blogs and books about the subject and every bit helps.

In this case, for your bigger table, I think (and this has the caveat there are many things about your DB and data model I don't know) just adding more columns to the index will work -- but use the Oracle tool and see what it suggests. Try that first.

Upvotes: 0

Related Questions