troubleshooting Question

Index not being used after Java upgrade in same Sybase database

Avatar of Prameet
Prameet asked on
Sybase DatabaseJava
5 Comments1 Solution780 ViewsLast Modified:
After upgrading to Java 11 driver from Java 8 : one of the query in my sybase production database (12.5.4) has significantly slowed down.
From execution plan, it looks like one index is not being used:

Question: Why the index is not veing used?
Note that same query in the same database is running fine in Java 8.

Query details:
select s.n_client, 
       pa.i_ac, s.n_ac, 
       pa.n_ac, 
       ...
       pa.posn_grp_desc, pa.posn_grp_nme, pa.n_iss_typ_grp, 
  from position_asset pa, auto_client_pf_stat s, auto_load_stage l 
 where pa.i_ac = s.i_ac and l.i_ac = pa.i_ac 
   and pa.d_valn_as_of = s.d_valn_as_of 
   and pa.c_valn_freq like 'D_' and 
   (s.c_freq = 'W' or s.c_freq like 'D%') 
   and (s.d_rpt_run = '01/01/2001'or s.d_rpt_run = '12/31/9999') 
   and s.d_btch = '01/01/2001'
   and l.i_strm = '290J'
Unfortunately, position_Asset is a view as shown below. All other database objects are table.
 
select p.n_ac,
          p.d_valn_as_of,
          p.c_valn_freq,
          ..
          ...
   r.qlty_rat_typ_spstfrn,
    r.qlty_rat_typ_spstlcl,
    r.qlty_rat_typ_md,
    r.qlty_rat_typ_mdlt,
    r.qlty_rat_typ_mdst
 from position p,
      asset    a,
      assetb   b,
      asset    u,                           
      aip_irqr r
where p.i_sybase_ast     =  a.i_sybase_ast
  and p.i_sybase_ast     =  b.i_sybase_ast
  and r.instr_issr_id    =* b.instr_issr_id         
  and r.instr_issr_id_01 =* b.instr_issr_id_01
  and u.i_sybase_ast     =* b.i_undrlyng_sybase_id
Query Plan:
In Java 8: query plan is using index scan in position table (inside the view). See below:

FROM TABLE
  position
            p
  Nested iteration.
      Index : xp1
      Forward scan.
      Positioning by key.
     Keys are:
          i_ac  ASC
          d_valn_as_of  ASC
          c_valn_freq  ASC

However, in Java 11 full table scan is being used:

FROM TABLE
   position
            p
      Nested iteration.
      Table Scan.      Forward scan.
      Positioning at start of table.
      Using I/O Size 16 Kbytes for data pages.
      With LRU Buffer Replacement Strategy for data pages

Note that index xp1 is not being used anymore while the query is calling the view. Everything else in both the query-plans look similar.
ASKER CERTIFIED SOLUTION
phoffricSoftware Engineering and Matlab Analyst

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros