RE: Slow running Query.
- From: "Kumar, Dharminder" <Dharminder.Kumar@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 24 Jun 2004 11:33:17 -0400
First of all it is not type mismatch as clcode column in all the tables is
defined as VARCHAR.
Based on the data the sub query for cl_clcode will return a maximum of 5 rows.
The new addition is that I have found from the DBA that we run the database in
RULE optimizer MODE. So I tried to run the explain for following query.
select * from me_client_assets -- on the table itself not VIEW assets_view
where ca_clcode in
(select cl_clcode from client where cl_decmkr='299292')
and substr(ca_date,1,4)||substr(ca_date,6,2)='20012'
and got the following explain plan.
OPERATION OPTIONS OBJECT_NAME
POSITION
------------------------- ------------------------- --------------------
----------
SELECT STATEMENT
NESTED LOOPS
1
TABLE ACCESS BY INDEX ROWID CLIENT
1
INDEX RANGE SCAN CL_DECMKR_IDX
1
TABLE ACCESS BY INDEX ROWID ME_CLIENT_ASSETS
2
INDEX RANGE SCAN PK_ME_CLIENT_ASSETS
1
Now as you can see that this query uses the index PK_ME_CLIENT_ASSETS despited
the query clause
substr(ca_date,1,4)||substr(ca_date,6,2)='20012
So it seems to me as if original query does use the required index because of
the definition of View assets_view.
I am attaching with following file, if someone is interested in doing some test.
1. create_schema.sql ( will create required tables and view).
2. drop_schema.sql ( will drop all the objects created by above script).
3. q1.sql ( My original query based on view, which does not use required Index)
4. q2.sql (The above query where I use substr(column) in the where clause, but
still used Index).
Anyway thanks for all of you who provided your valuable inputs.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Wolfgang Breitling
Sent: June 22, 2004 7:10 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Slow running Query.
How many cl_clcodes does the subselect return and how does that number
compare to the cardinality of the VW_NSO_1 view in the explain plan?
At 11:03 AM 6/22/2004, you wrote:
>Mark,
>I tried both the options suggested by you. Following is the result.
>
>Thought 1.
>Query
>
>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 != '0000-00-00' -- dkumar added
> AND
> substr(pc_grpdte,1,4)||substr(pc_grpdte,6,2) <= '200312')
> and as_date ='200312'
regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
<FONT SIZE =
1>****************************************************************************
This e-mail and any attachments may contain confidential and privileged
information. If you are not the intended recipient, please notify the sender
immediately 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 intended
recipient is unauthorized and may be illegal. Unless otherwise stated, opinions
expressed in this e-mail are those of the author and are not endorsed by the
author's employer.</FONT>
-- Binary/unsupported file stripped by Ecartis --
-- Type: application/octet-stream
-- File: create_schema.sql
-- Desc: create_schema.sql
-- Binary/unsupported file stripped by Ecartis --
-- Type: application/octet-stream
-- File: drop_schema.sql
-- Desc: drop_schema.sql
-- Binary/unsupported file stripped by Ecartis --
-- Type: application/octet-stream
-- File: q1.sql
-- Desc: q1.sql
-- Binary/unsupported file stripped by Ecartis --
-- Type: application/octet-stream
-- File: q2.sql
-- Desc: q2.sql
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
