| Query tuning [message #185966] |
Fri, 04 August 2006 09:33  |
gajini
Messages: 262
Registered: January 2006
|
Senior Member |
|
|
Hi,
The following query gives a very high cost.
SELECT agr.szagreementno ,
AGR.SZORGBRANCHCODE,
AGR.CPORTFOLIOCODE,
AGR.SZPRODUCTOFFEREDCODE
FROM AGREEMENTMASTER agr
WHERE AGR.CUSERDEFFLAG1='N'
AND AGR.CACCOUNTSTATUS='L'
AND AGR.CPORTFOLIOCODE IN(SELECT SZVALUE
FROM GENERALCONDITIONMASTER
WHERE SZSYSTEMNAME='ICAPS'
AND SZCONDITION='COMPANY'
AND SZFROMVALUE=:p_COMPCODE) ;
The explain plan for the query is
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 684 | 14209 |
| 1 | HASH JOIN | | 12 | 684 | 14209 |
| 2 | SORT UNIQUE | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| GENERALCONDITIONMASTER | 1 | 31 | 3 |
| 4 | INDEX RANGE SCAN | PK_GENERALCONDITIONMASTER | 1 | | 2 |
| 5 | TABLE ACCESS FULL | AGREEMENTMASTER | 242K| 6154K| 14199 |
--------------------------------------------------------------------------------------------
In the table AGREEMENTMASTER,i've created three bitmap indexes on
columns(CUSERDEFFLAG1,CACCOUNTSTATUS,CPORTFOLIOCODE) whose cardinality is just 5.
After that also,the cost & plan are same.
Please give me some tips,so that i can reduce the cost of the query.
|
|
|
|
| Re: Query tuning [message #186152 is a reply to message #185966] |
Sun, 06 August 2006 09:53  |
Frank Naude
Messages: 4579
Registered: April 1998
|
Senior Member |
|
|
You cannot compare cost across queries. The fact that the cost looks high is irrelevant. It just shows that the most expensive operation is that last full table scan.
Whenever you see "full table scan" you must think: "do I need to add an index?", or "can I hint the query to use an existing index".
|
|
|
|
|
|
| Re: Query tuning [message #186859 is a reply to message #186682] |
Wed, 09 August 2006 22:29 |
lzfhope
Messages: 69
Registered: July 2006
|
Member |
|
|
Maybe your system could not work like below:
change sqltext '
SELECT agr.szagreementno ,
AGR.SZORGBRANCHCODE,
AGR.CPORTFOLIOCODE,
AGR.SZPRODUCTOFFEREDCODE
FROM AGREEMENTMASTER agr
WHERE AGR.CUSERDEFFLAG1='N'
AND AGR.CACCOUNTSTATUS='L'
AND AGR.CPORTFOLIOCODE IN(SELECT SZVALUE
FROM GENERALCONDITIONMASTER
WHERE SZSYSTEMNAME='ICAPS'
AND SZCONDITION='COMPANY'
AND SZFROMVALUE=:p_COMPCODE)'
to
'
SELECT agr.szagreementno ,
AGR.SZORGBRANCHCODE,
AGR.CPORTFOLIOCODE,
AGR.SZPRODUCTOFFEREDCODE
FROM AGREEMENTMASTER agr
WHERE AGR.CPORTFOLIOCODE IN(SELECT SZVALUE
FROM GENERALCONDITIONMASTER
WHERE SZSYSTEMNAME='ICAPS'
AND SZCONDITION='COMPANY'
AND SZFROMVALUE=:p_COMPCODE) and
AGR.CUSERDEFFLAG1='N'
AND AGR.CACCOUNTSTATUS='L''
pay attention to your system's Optimizer
|
|
|
|
| Re: Query tuning [message #186922 is a reply to message #186859] |
Thu, 10 August 2006 02:53 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
That change is unlikely to make any difference unless you are using the RBO, and I think you'd need an older version of the RBO.
For queries where the CBO doesn't run out of permutations, the order of clauses in the Where clause doesn't make a difference to the CBO.
|
|
|
|
|
|
| Re: Query tuning [message #187251 is a reply to message #185966] |
Fri, 11 August 2006 08:59 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
Can you post the results of following queries:
SELECT COUNT(*), COUNT(DISTINCT SZVALUE),
COUNT(DISTINCT SZSYSTEMNAME || '-' || SZCONDITION || '-' || SZFROMVALUE
FROM GENERALCONDITIONMASTER;
SELECT COUNT(*), COUNT(DISTINCT CUSERDEFFLAG1 ) ,
COUNT(DISTINCT CACCOUNTSTATUS ) ,
COUNT(DISTINCT CPORTFOLIOCODE ),
COUNT(DISTINCT CUSERDEFFLAG1 || '-' || CACCOUNTSTATUS || '-' || CPORTFOLIOCODE )
FROM AGREEMENTMASTER agr
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
