OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Index issue (10.2.0.3, Windows 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Index issue [message #404396]

Thu, 21 May 2009 11:29

sant_new
Messages: 165
Registered: June 2008

Senior Member

Hi all,

I'm trying to get the data from the below query on our client's staging database. I waited for 8 minutes for the results, then cancelled it and saw the explain plan and found its doing a full table scan for table OMT

Here is the query:

select r.inv_no,r.crt_no,r.otp,r.inv_bill_dt,r.app,sum(o.tramt) 
from ohst r, cli c, omt o
where r.inv_bll_dt > '1-mar-2008'
and r.app <> 1
and r.inv_no <> 1
and r.inv_no = c.inv_no
and o.item_id = c.item_id
and o.sce='O'
and o.ptype='P'
group by r.inv_no,r.crt_no,r.otp,r.inv_bll_dt,r.app



SELECT STATEMENT Optimizer Mode=CHOOSE  794 M   10 M                         
  HASH GROUP BY  794 M 37G 10 M                         
    HASH JOIN  794 M 37G 111470                           
      TABLE ACCESS BY INDEX ROWID PBS.OHST 34 K 764 K 19267                           
        INDEX RANGE SCAN PBS.OHST__INV_BLL_DT__IDX 203 K   274                           
      HASH JOIN  1 M 40 M 83659                           
        TABLE ACCESS FULL PBS.OMT 1 M 23 M 53247                           
        VIEW PBS.index$_join$_002 4 M 49 M 23403                           
          HASH JOIN                                 
            INDEX FAST FULL SCAN PBS.CLI_PK 4 M 49 M 5048                           
            INDEX FAST FULL SCAN PBS.CLI__INV_NO__IDX 4 M 49 M 6793                  

I tried to force the index for OMT as:


select r.inv_no,r.crt_no,r.otp,r.INV_BLL_DT,r.app,sum(o.tramt) /*+ index (FK_cli_ITEM_ID_OMT) */
from ohst r, cli c, omt o
where r.inv_bll_dt > '1-mar-2008'
and r.app <> 1
and r.inv_no <> 1
and r.inv_no = c.inv_no
and o.item_id = c.item_id
and o.sce='O'
and o.ptype='P'
group by r.inv_no,r.crt_no,r.otp,r.inv_bll_dt,r.app

Still the same. The tables and indexes are analyzed. Any suggestions??

Thanks.

[Updated on: Thu, 21 May 2009 11:31] by Moderator

Report message to a moderator

Re: Index issue [message #404397 is a reply to message #404396]

Thu, 21 May 2009 11:33

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SELECT   r.inv_no,
         r.crt_no,
         r.otp,
         r.inv_bill_dt,
         r.app,
         Sum(o.tramt)
FROM     ohst r,
         cli c,
         omt o
WHERE    r.inv_bll_dt > '1-mar-2008'
         AND r.app <> 1
         AND r.inv_no <> 1
         AND r.inv_no = c.inv_no
         AND o.item_id = c.item_id
         AND o.sce = 'O'
         AND o.ptype = 'P'
GROUP BY r.inv_no,
         r.crt_no,
         r.otp,
         r.inv_bll_dt,
         r.app

'This is a string; not a date'
'1-mar-2008' when you need a DATE then use TO_DATE() function
functions preclude use of index.

Report message to a moderator

Re: Index issue [message #404398 is a reply to message #404396]

Thu, 21 May 2009 11:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Why is table CLI in FROM clause when it contribute no data to the SELECT clause?

It might result in Cartesian Product & delay results being returned.

Report message to a moderator

Re: Index issue [message #404400 is a reply to message #404397]

Thu, 21 May 2009 12:05

sant_new
Messages: 165
Registered: June 2008

Senior Member

Thanks for your reply.

I tried with to_date(), it still doesnt use the index..

SELECT   r.inv_no,
         r.crt_no,
         r.otp,
         r.inv_bill_dt,
         r.app,
         Sum(o.tramt)
FROM     ohst r,
         cli c,
         omt o
WHERE    r.inv_bll_dt > to_date('03/01/2008','MM/DD/YYYY')
         AND r.app <> 1
         AND r.inv_no <> 1
         AND r.inv_no = c.inv_no
         AND o.item_id = c.item_id
         AND o.sce = 'O'
         AND o.ptype = 'P'
GROUP BY r.inv_no,
         r.crt_no,
         r.otp,
         r.inv_bll_dt,
         r.app

I need to use cli table for the where clauses - r.inv_no = c.inv_no and o.item_id = c.item_id..

Any other suggestions, please?

Thanks a lot

Report message to a moderator

Re: Index issue [message #404401 is a reply to message #404396]

Thu, 21 May 2009 12:14

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I need to use cli table for the where clauses - r.inv_no = c.inv_no and o.item_id = c.item_id..

The way the SQL is coded c.inv_no can be in a different row than c.item_id.

As long as one row exists where r.inv_no = c.inv_no this will ALWAYS be true.

As long as one row exists where o.item_id = c.item_id this will ALWAYS be true.

Neither provides any effective filtering.

Perhaps it should be
AND (r.inv_no, o.item_id) IN (SELECT c.inv_no, c.item_id FROM cli c)

after eliminating CLI out of FROM clause

Report message to a moderator

Re: Index issue [message #404402 is a reply to message #404396]

Thu, 21 May 2009 12:32

sant_new
Messages: 165
Registered: June 2008

Senior Member

Hi Blackswan,

I tried this now:

select r.inv_no,r.crt_no,r.otp,r.INV_BLL_DT,r.app,sum(o.tramt) /*+ index (FK_cli_ITEM_ID_OMT) */
from ohst r, cli c, omt o
where r.inv_bll_dt > to_date('03/01/2008','MM/DD/YYYY')
and r.app <> 1
and r.inv_no <> 1
AND (r.inv_no, o.item_id) IN (SELECT c.inv_no, c.item_id FROM cli c)
and o.sce='O'
and o.ptype='P'
group by r.inv_no,r.crt_no,r.otp,r.inv_bll_dt,r.app

Still not using the index..

Thanks for your help

Report message to a moderator

Re: Index issue [message #404403 is a reply to message #404396]

Thu, 21 May 2009 12:37

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SELECT   r.inv_no,
         r.crt_no,
         r.otp,
         r.inv_bll_dt,
         r.app,
         Sum(o.tramt)
FROM     ohst r,
         omt o
WHERE    r.inv_bll_dt > To_date('03/01/2008','MM/DD/YYYY')
         AND r.app <> 1
         AND r.inv_no <> 1
         AND (r.inv_no,o.item_id) IN (SELECT  /*+ index (FK_cli_ITEM_ID_OMT) */ c.inv_no,
                                                                                c.item_id
                                      FROM   cli c)
         AND o.sce = 'O'
         AND o.ptype = 'P'
GROUP BY r.inv_no,
         r.crt_no,
         r.otp,
         r.inv_bll_dt,
         r.app

Report message to a moderator

Re: Index issue [message #404404 is a reply to message #404396]

Thu, 21 May 2009 12:43

sant_new
Messages: 165
Registered: June 2008

Senior Member

Tried it, still not using the forced index... Could there be anything else wrong?

Thanks

Report message to a moderator

Re: Index issue [message #404479 is a reply to message #404404]

Thu, 21 May 2009 23:25

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

where r.inv_bll_dt > '1-mar-2008'
and r.app <> 1
and r.inv_no <> 1
and o.sce='O'
and o.ptype='P'

What percentage of all possible rows do the last two filter predicates eliminate?
What percentage of all possible rows do the first two predicates eliminate?
Depending on the answer to these questions, a full table scan might be the best plan.

Ross Leishman

Report message to a moderator

Re: Index issue [message #404634 is a reply to message #404398]

Fri, 22 May 2009 09:36

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

@Blackswan

Quote:

Why is table CLI in FROM clause when it contribute no data to the SELECT clause?

It might result in Cartesian Product & delay results being returned.

Why do you think that a table that is in the FROM clause, and joined to two other tables would cause a cartesian join simply because columns aren't being selected.

Can you provide a test case showing that this is even possible?

Report message to a moderator

Re: Index issue [message #404638 is a reply to message #404401]

Fri, 22 May 2009 09:43

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

@Blackswan

In reference to this query:

Quote:

select r.inv_no,r.crt_no,r.otp,r.inv_bill_dt,r.app,sum(o.tramt)
from ohst r, cli c, omt o
where r.inv_bll_dt > '1-mar-2008'
and r.app <> 1
and r.inv_no <> 1
and r.inv_no = c.inv_no
and o.item_id = c.item_id
and o.sce='O'
and o.ptype='P'
group by r.inv_no,r.crt_no,r.otp,r.inv_bll_dt,r.app

You write that

Quote:

The way the SQL is coded c.inv_no can be in a different row than c.item_id.

As long as one row exists where r.inv_no = c.inv_no this will ALWAYS be true.

As long as one row exists where o.item_id = c.item_id this will ALWAYS be true.

I'm afraid I'm going to have to call you on this one - what you've said (unless I've totally misunderstood you) is just plain wrong.

That query looks for sets of rows from the table C,R & O where R.inv_no = C.inv_no and O.item_id = c.item_id.

To get the situation you describe where the values of C.inv_no and C.item_id can be on different rows of table C, you'd have to have table C listed twice in the where clause.

Report message to a moderator

Re: Index issue [message #404639 is a reply to message #404397]

Fri, 22 May 2009 09:55

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

While we're at it:

BlackSwan wrote on Thu, 21 May 2009 17:33

SELECT   r.inv_no,
         r.crt_no,
         r.otp,
         r.inv_bill_dt,
         r.app,
         Sum(o.tramt)
FROM     ohst r,
         cli c,
         omt o
WHERE    r.inv_bll_dt > '1-mar-2008'
         AND r.app <> 1
         AND r.inv_no <> 1
         AND r.inv_no = c.inv_no
         AND o.item_id = c.item_id
         AND o.sce = 'O'
         AND o.ptype = 'P'
GROUP BY r.inv_no,
         r.crt_no,
         r.otp,
         r.inv_bll_dt,
         r.app

'This is a string; not a date'
'1-mar-2008' when you need a DATE then use TO_DATE() function
functions preclude use of index.

Functions on indexed columns preclude the use of indexes.
Since the to_date would be needed on the '1-mar-2008' and not the column that doesn't apply here.

Report message to a moderator

Previous Topic:	Difference between awr metrics
Next Topic:	Query tuning

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 13:58:50 CST 2024