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 Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136107.jpg) |
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.
|
|
|
|
Re: Index issue [message #404400 is a reply to message #404397] |
Thu, 21 May 2009 12:05 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #404402 is a reply to message #404396] |
Thu, 21 May 2009 12:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136107.jpg) |
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
|
|
|
|
Re: Index issue [message #404479 is a reply to message #404404] |
Thu, 21 May 2009 23:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
Re: Index issue [message #404634 is a reply to message #404398] |
Fri, 22 May 2009 09:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 thatQuote: | 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 Go to previous message](/forum/theme/orafaq/images/up.png) |
cookiemonster
Messages: 13937 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.
|
|
|
Goto Forum:
Current Time: Thu Aug 22 07:55:38 CDT 2024
|