Retrieve Data faster from a huge Oracle Table

indexoracle

I have a huge Oracle table ( ttdinv700 ) which contains almost 20 million rows.

I am trying to execute the below query, on the table:

   select max(ttdinv700.t$trdt) 
   from ttdinv700, ttiitm001
   where ttdinv700.t$item = rpad(ttiitm001.t$item,16,' ')
   and ttdinv700.t$koor = 1
   and ttdinv700.t$kost in (2,7);

The execution is taking a lot of time. Could you please suggest a way to retrieve the data faster?

I cannot partition the table as this is a production database.

Best Answer

When I recall correctly this is a query on BaaN version 4 or so probably. The official BaaN policy is that the Oracle drivers require ALL text fields to be VARCHAR which has some different semantics than VARCHAR2. I guess that your rpad was introduced by someone to compensate for that but it should not be there.

Also, you should try to avoid adding indexes etc yourself. The BaaN middleware takes care of that and although it is possible to add indexes things can go wrong during upgrades etc.

I don't have a BaaN system on Oracle present at this moment, but I think you are missing some leading columns of the index in your query, something like company/warehouse since you seem to be determing possibly the last inventory transaction per logistic item. Oracle version 9 and higher sometimes can compensate for that.

My advice is to press F4 in TOAD or Invantive Query Tool on ttdinv700 and check the columns listed in the index. Add missing entries to the where clause. Or check contents of user_ind_columns.

Then do the same for ttditm700 (the items). The rpad effectively disables index use but depending on your statistics it should be the driving table for the query (most companies have relatively few items compared to their stocks). Try to enable the index on ttditm700.

IMHO this question belongs on stackoverflow and not here.

Related Question