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.

  • 7
    Please show the execution plan, all defined indexes and the table structure. And I don't understand why you couldn't introduce partitioning into a production database. That's where partitioning makes the most sense. Btw: what is a "crore record"? – a_horse_with_no_name Jan 30 '14 at 20:06
  • Is the rpad() really necessary? Why would the join key have different formats in different tables? – Gordon Linoff Jan 30 '14 at 20:51
  • yup.. rpad kills. might a function based index help any chance?? both has to be varchar2 anyways. – OracleUser Jan 30 '14 at 20:58
  • yes . The ' t$item ' column in both the tables ttdinv700 and ttiitm001 is described as char(16) , And not Varchar2 . Thats why RPAD was used – user3254725 Jan 30 '14 at 21:22
  • 2
    If t$item is char(16) in both tables, I don't think you need the rpad. If you remove the rpad it might be able to take advantage of indexes on one or both of those columns. Also, the magic values (1, 2, 7) look suspicious - are they really like that in your code, or do you use bind variables? – Jeffrey Kemp Jan 31 '14 at 5:54

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.