| 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.
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Wolfgang Breitling
Sent: June 22, 2004 7:10 PM
To: oracle-l_at_freelists.org
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
-- 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 = 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_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 Thu Jun 24 2004 - 10:30:34 CDT
 |
 |