Home » RDBMS Server » Performance Tuning » Index issue (10.2.0.3, Windows 2003)
Index issue [message #404396] Thu, 21 May 2009 11:29 Go to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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.
Re: Index issue [message #404398 is a reply to message #404396] Thu, 21 May 2009 11:51 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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.
Re: Index issue [message #404400 is a reply to message #404397] Thu, 21 May 2009 12:05 Go to previous messageGo to next message
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
Re: Index issue [message #404401 is a reply to message #404396] Thu, 21 May 2009 12:14 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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
Re: Index issue [message #404402 is a reply to message #404396] Thu, 21 May 2009 12:32 Go to previous messageGo to next message
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
Re: Index issue [message #404403 is a reply to message #404396] Thu, 21 May 2009 12:37 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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 
Re: Index issue [message #404404 is a reply to message #404396] Thu, 21 May 2009 12:43 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Tried it, still not using the forced index... Could there be anything else wrong?

Thanks
Re: Index issue [message #404479 is a reply to message #404404] Thu, 21 May 2009 23:25 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Index issue [message #404634 is a reply to message #404398] Fri, 22 May 2009 09:36 Go to previous messageGo to next message
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?
Re: Index issue [message #404638 is a reply to message #404401] Fri, 22 May 2009 09:43 Go to previous messageGo to next message
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.

Re: Index issue [message #404639 is a reply to message #404397] Fri, 22 May 2009 09:55 Go to previous message
cookiemonster
Messages: 12422
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.
Previous Topic: Difference between awr metrics
Next Topic: Query tuning
Goto Forum:
  


Current Time: Sat Dec 10 08:57:51 CST 2016

Total time taken to generate the page: 0.25305 seconds