| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Slow running Query.
Hi
In Oracle 8 i remember you have more limitations to get an index used.
1) If I'm noyt wrong columns must be in the same order (including the
function)
2) the use of NOT or != or a composite column invalides the use of the
index like substr(pc_grpdte,1,4)||substr(pc_grpdte
My suggestion is drop the index and recreate adding column by column, in the
same way put in the where clause one by one the conditions, and every step
verify you are getting the index used.
Keep the columns in the same order and you will have to add to the index the
function.
I think this can cause long querys, but I don't see other chances
SELECT sum(nvl(as_ofcsh,0)),sum(nvl(as_offxd,0)),
sum(nvl(as_ofeqty,0)),sum(nvl(as_ofai,0))
FROM assets_view
WHERE as_clcode in ( select cl_clcode from perfclient,client
WHERE cl_decmkr=3D'64501013'
AND cl_clcode=3Dpc_clcode
AND pc_prfcomb in ('B', 'Y')
AND pc_grpdte !=3D '0000-00-00' -- dkumar a=
dded
Explain Plan
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
OPERATION OPTIONS OBJECT_NAME =
POSITION
------------------------- ------------------------- -------------------- --=
Thought 2 ( Hope I got it right)
=3D=3D=3D=3D=3D=3D=3D=3D=3D
Query 2
=3D=3D=3D=3D=3D=3D=3DSELECT sum(nvl(as_ofcsh,0)),sum(nvl(as_offxd,0)),
sum(nvl(as_ofeqty,0)),sum(nvl(as_ofai,0))
FROM assets_view ,(select cl_clcode,rownum from client,perfclient
WHERE cl_decmkr=3D'64501013'
AND cl_clcode=3Dpc_clcode
AND pc_prfcomb in ('B', 'Y')
AND pc_grpdte !=3D '0000-00-00' -- dkumar added
AND substr(pc_grpdte,1,4)||substr(pc_grpdte,6,2) <=3D '200312')
where as_clcode =3D cl_clcode
Explain Plan
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
OPERATION OPTIONS OBJECT_NAME =
POSITION
------------------------- ------------------------- -------------------- --=
So you can see that in both the cases, queries are not using the INDEX in q=
uestion.
Let me know if you have any other suggestion.
Thanks.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mark W. Farnham
Sent: June 21, 2004 6:18 PM
To: oracle-l_at_freelists.org
Subject: RE: Slow running Query.
Two thoughts:
Take your first fast query and modify the where clause using the second fast query as the IN list.
where as_clcode in (select cl_clcode from .....your second fast query)
If you're on a release that supports order by, ordering the IN list query by cl_clcode *might* make the view's group by operation faster and probably costs little.
Second thought:
make the entire part of the query that delivers you the cl_clcode list you need a virtual table in the from clause. If this doesn't "fix" the plan selected, consider adding a dummy rownum column in the query part of the virtual table so it forces projection.
Now if your list of cl_clcodes actually delivered is very long, the sub-seconds for each part may add up to the few minutes you mentioned.
The existence of the referenced features varies on release, and your mileage may vary.
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Kumar, Dharminder
Sent: Monday, June 21, 2004 5:06 PM
To: oracle-l_at_freelists.org
Subject: Slow running Query.
> Hi all,
> I have the following query which is running very slow as it takes few min=
=3D
utes to complete.
>=3D20
>=3D20
>=3D20
>=3D20
>=3D20
>=3D20
>=3D20
>=3D20 >=3D20 >=3D20 >=3D20
<FONT SIZE =3D3D 1>********************************************************=**=3D
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- <FONT SIZE =3D 1>**********************************************************= ****************** This e-mail and any attachments may contain confidential and privileged inf= ormation. If you are not the intended recipient, please notify the sender i= mmediately by return e-mail, delete this e-mail and destroy any copies. Any= dissemination or use of this information by a person other than the intend= ed recipient is unauthorized and may be illegal. Unless otherwise stated, o= pinions expressed in this e-mail are those of the author and are not endors= ed by the author's employer.</FONT> ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Jun 22 2004 - 12:45:28 CDT
 |
 |