Hello All,
I created a simple view and when I execute the view it takes more time and when I execute the query it give response quickly.
Below are the explain plans
Query :
Execution Plan
----------------------------------------------------------
Plan hash value: 2867537711
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 153 | 107 (1)| 00:00:02 |
| 1 | COUNT | | | | | |
|* 2 | HASH JOIN OUTER | | 1 | 153 | 107 (1)| 00:00:02 |
| 3 | NESTED LOOPS | | 1 | 101 | 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TBLWEBINSPR | 1 | 58 | 2 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | IDX_SUBSRIBER_WEBINSPR | 1 | | 1 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| TBLMACCOUNT | 1 | 43 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_USERNAME_ACCOUNT | 1 | | 1 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | TBLTNETWORKSERVICEINSTANCE | 12828 | 651K| 103 (1)| 00:00:02 |
-------------------------------------------------------------------------------------------------------------
Explain Plan of View :
Execution Plan
----------------------------------------------------------
Plan hash value: 3639776364
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12889 | 61M| 824 (1)| 00:00:10 |
|* 1 | VIEW | V5 | 12889 | 61M| 824 (1)| 00:00:10 |
| 2 | COUNT | | | | | |
|* 3 | HASH JOIN RIGHT OUTER| | 12889 | 1925K| 824 (1)| 00:00:10 |
|* 4 | TABLE ACCESS FULL | TBLTNETWORKSERVICEINSTANCE | 12828 | 651K| 103 (1)| 00:00:02 |
|* 5 | HASH JOIN | | 12889 | 1271K| 721 (1)| 00:00:09 |
|* 6 | TABLE ACCESS FULL | TBLMACCOUNT | 12865 | 540K| 583 (1)| 00:00:07 |
| 7 | TABLE ACCESS FULL | TBLWEBINSPR | 17680 | 1001K| 137 (1)| 00:00:02 |
------------------------------------------------------------------------------------------------------
Query is :
select rownum as id, tw.subscriberidentity as user_identity, tw.password, tw.customertype, tw.expirydate, tw.status, nvl(decode(lower(tn.isportal),'yes','yes','no','no'),'yes') as passwordcheck,
fn_getEncryptionType(tm.encryptiontypeid) as encryptiontype, tw.servicetype, tn.maccount, tn.isroaming, tn.isportal, tn.iprange, lower(replace(fn_getmodemmac(tn.referenceaccountid),'-')) as modemmac,
nvl(fn_getclientmac(tn.referenceaccountid),'A4-BA-DB-B7-FA-10') as clientmac, 'DEN Delhi Address' as address, tn.networkkeyid, decode(lower(tn.isroaming),'yes',null,'no','0:8='||tn.iprange) as checkroaming,
'ACC0000000' as cacno, 'abc@dd.com' as cemailid, 'Account Name' as cname, '7567077034' as cmobile, '66099965606' as coffice from (select distinct referenceaccountid, param2 maccount, param4
isroaming, param5 isportal, param6 iprange, NTWRKSRVINSTANCEID networkkeyid from tbltnetworkserviceinstance where systemgenerated = 'N') tn , tblwebinspr tw,tblmaccount tm where
tn.referenceaccountid(+)=tm.accountid and tw.subscriberidentity=tm.username and tm.accounttypeid = 'ACT08' and tm.systemgenerated = 'N'
Please suggest.
Regards,
Ronak Mandowara