Count query taking too long to execute.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count query taking too long to execute.

Author

Topic

shashankkoul
Starting Member

5 Posts

Posted - 2013-04-18 : 09:57:56

I have 2 SQL queries. First gets me the count of the pending dealer applications needing approval of a particular user and second gets me the details of the pending dealer applications needing approval of a particular user.

Count Query:
select count(mdealerapp0_.SEQ_DEALER) as col_0_0_
from M_DEALER_APPLY mdealerapp0_
where (exists (
select mdealerapp1_.SEQ_DEALER
from M_DEALER_APPLY mdealerapp1_, M_USER_BRANCH muserbranc2_, M_BRANCH msalesgrp3_, VIEW_SUB_BRANCH msubbranch4_
where mdealerapp1_.CD_SALES_GRP=msalesgrp3_.CD_SALES_GRP
and msalesgrp3_.CD_SUB_BRANCH=msubbranch4_.CD_SUB_BRANCH
and 1=1
and msubbranch4_.CD_BRANCH=muserbranc2_.CD_BRANCH
and muserbranc2_.USER_ID='200009'
and muserbranc2_.CD_BIZ='2'
and mdealerapp1_.SEQ_DEALER=mdealerapp0_.SEQ_DEALER)
or exists (
select mdealerapp5_.SEQ_DEALER
from M_DEALER_APPLY mdealerapp5_, M_DEALER mdealer6_, M_USER_BRANCH muserbranc7_, M_BRANCH msalesgrp8_, VIEW_SUB_BRANCH msubbranch9_
where mdealer6_.CD_SALES_GRP=msalesgrp8_.CD_SALES_GRP
and msalesgrp8_.CD_SUB_BRANCH=msubbranch9_.CD_SUB_BRANCH
and 1=1
and mdealerapp5_.CD_DEALER_FROM=mdealer6_.CD_DEALER
and msubbranch9_.CD_BRANCH=muserbranc7_.CD_BRANCH
and muserbranc7_.USER_ID='200009'
and muserbranc7_.CD_BIZ='2'
and mdealerapp5_.SEQ_DEALER=mdealerapp0_.SEQ_DEALER)
)
and (exists (
select tsfeapprov10_.APPR_USERID
from T_SFE_APPROVAL_IT tsfeapprov10_
where tsfeapprov10_.APPR_USERID='200009'
and tsfeapprov10_.CD_BIZ='2'
and tsfeapprov10_.POS=mdealerapp0_.CUR_POS
and tsfeapprov10_.SEQ_DELIV=mdealerapp0_.SEQ_DEALER
and mdealerapp0_.ST_DEALER_APP=4) or mdealerapp0_.CREATE_USER='200009' and mdealerapp0_.CD_CREATE_BIZ='2' and mdealerapp0_.ST_DEALER_APP=1
)
and mdealerapp0_.CREATE_USER<>'SFE0201B02' order by mdealerapp0_.SEQ_DEALER;

Data Query:
select * from ( select mdealerapp0_.SEQ_DEALER as SEQ1_93_0_, msubsectio1_.CD_SUB_SECTION as CD1_17_1_, mchannel3_.CD_CHANNEL as CD1_11_2_, msalesgrp2_.CD_SALES_GRP as CD1_25_3_, viewalluse4_.USER_ID as USER1_19_4_, mdealerapp0_.ZZ_DT_UPDATED as ZZ2_93_0_, mdealerapp0_.CD_DEALER as CD3_93_0_, mdealerapp0_.NM_DESC as NM4_93_0_, mdealerapp0_.ST_DEALER as ST5_93_0_, mdealerapp0_.PROV_ID as PROV6_93_0_, mdealerapp0_.CITY_ID as CITY7_93_0_, mdealerapp0_.VAT as VAT93_0_, mdealerapp0_.ACCOUNT_BANK as ACCOUNT9_93_0_, mdealerapp0_.DETAIL_BANK as DETAIL10_93_0_, mdealerapp0_.NM_CONTACT as NM11_93_0_, mdealerapp0_.EMAIL as EMAIL93_0_, mdealerapp0_.CD_GRP as CD13_93_0_, mdealerapp0_.PL_TYP as PL14_93_0_, mdealerapp0_.ADDRESS_ACCOUNT as ADDRESS15_93_0_, mdealerapp0_.TEL_ACCOUNT as TEL16_93_0_, mdealerapp0_.POSTCODE_ACCOUNT as POSTCODE17_93_0_, mdealerapp0_.CD_NAS_GRP as CD18_93_0_, mdealerapp0_.VAIO_GRP_CODE as VAIO19_93_0_, mdealerapp0_.CREDIT_A_HIGH as CREDIT20_93_0_, mdealerapp0_.CREDIT_A_LOW as CREDIT21_93_0_, mdealerapp0_.PAY_LIMIT_DAY as PAY22_93_0_, mdealerapp0_.CREDIT_B_HIGH as CREDIT23_93_0_, mdealerapp0_.CREDIT_B_LOW as CREDIT24_93_0_, mdealerapp0_.INVOICE_LT as INVOICE25_93_0_, mdealerapp0_.TRANS_TYPE as TRANS26_93_0_, mdealerapp0_.TRANS_AREA as TRANS27_93_0_, mdealerapp0_.DELIVERY_FAC as DELIVERY28_93_0_, mdealerapp0_.DT_CREATE as DT29_93_0_, mdealerapp0_.CREATE_USER as CREATE30_93_0_, mdealerapp0_.ADDRESS_INV as ADDRESS31_93_0_, mdealerapp0_.TEL_INV as TEL32_93_0_, mdealerapp0_.POSTCODE_INV as POSTCODE33_93_0_, mdealerapp0_.TAX_NO as TAX34_93_0_, mdealerapp0_.SHORT_NAME_CN as SHORT35_93_0_, mdealerapp0_.CD_SUB_SECTION as CD36_93_0_, mdealerapp0_.FLG_CTO as FLG37_93_0_, mdealerapp0_.FLG_DUMMY as FLG38_93_0_, mdealerapp0_.CD_SALES_GRP as CD39_93_0_, mdealerapp0_.TYP_DEALER as TYP40_93_0_, mdealerapp0_.TYP_SELLOUT as TYP41_93_0_, mdealerapp0_.TYP_CREDIT as TYP42_93_0_, mdealerapp0_.FLG_SIX as FLG43_93_0_, mdealerapp0_.FLG_FIXREB as FLG44_93_0_, mdealerapp0_.FLG_IPN as FLG45_93_0_, mdealerapp0_.FLG_IOC as FLG46_93_0_, mdealerapp0_.FLG_IOC_GRP as FLG47_93_0_, mdealerapp0_.REMARK as REMARK93_0_, mdealerapp0_.FLG_VAIO as FLG49_93_0_, mdealerapp0_.FLG_LOCAL_DEALER as FLG50_93_0_, mdealerapp0_.SALESMAN as SALESMAN93_0_, mdealerapp0_.CD_COMPANY as CD52_93_0_, mdealerapp0_.COUNT_PROV_ID as COUNT53_93_0_, mdealerapp0_.COUNT_CITY_ID as COUNT54_93_0_, mdealerapp0_.FAX_INV as FAX55_93_0_, mdealerapp0_.FLG_CP as FLG56_93_0_, mdealerapp0_.FLG_EDI as FLG57_93_0_, mdealerapp0_.NM_EN_DEALER as NM58_93_0_, mdealerapp0_.NM_EN_SHORT as NM59_93_0_, mdealerapp0_.ID_SAP as ID60_93_0_, mdealerapp0_.CUSTOMER_COND_GRP as CUSTOMER61_93_0_, mdealerapp0_.CD_DEALER_FROM as CD62_93_0_, mdealerapp0_.ZZ_CREATED_USER as ZZ63_93_0_, mdealerapp0_.ZZ_DT_CREATED as ZZ64_93_0_, mdealerapp0_.ZZ_UPDATED_USER as ZZ65_93_0_, mdealerapp0_.ST_DEALER_APP as ST66_93_0_, mdealerapp0_.CUR_POS as CUR67_93_0_, mdealerapp0_.TYP_APPLY as TYP68_93_0_, mdealerapp0_.FLG_DEF_SHIPTO as FLG69_93_0_, mdealerapp0_.MEM_FILE_NAME as MEM70_93_0_, mdealerapp0_.IOC_FILE_NAME as IOC71_93_0_, mdealerapp0_.MEM_FILE_ID as MEM72_93_0_, mdealerapp0_.IOC_FILE_ID as IOC73_93_0_, mdealerapp0_.CD_CREATE_BIZ as CD74_93_0_, mdealerapp0_.FLG_UPDATE_SALESSH as FLG75_93_0_, mdealerapp0_.FLG_UPDATE_SALESSB as FLG76_93_0_, mdealerapp0_.FLG_UPDATE_SALESSBSH as FLG77_93_0_, mdealerapp0_.FLG_SEND as FLG78_93_0_, mdealerapp0_.FLG_EDI_AR_ORDER as FLG79_93_0_, mdealerapp0_.DT_CLOSE as DT80_93_0_, mdealerapp0_.RFC_FILE_ID as RFC81_93_0_, mdealerapp0_.RFC_FILE_NAME as RFC82_93_0_, mdealerapp0_.RFC_MAIL_FLAG as RFC83_93_0_, mdealerapp0_.RFC_MAIL_ID as RFC84_93_0_, mdealerapp0_.RFC_MAIL_NAME as RFC85_93_0_, msubsectio1_.CD_CHANNEL as CD2_17_1_, msubsectio1_.NM_SUB_SECTION as NM3_17_1_, msubsectio1_.NM_SUB_SECTION_EN as NM4_17_1_, msubsectio1_.ZZ_CREATED_USER as ZZ5_17_1_, msubsectio1_.ZZ_DT_CREATED as ZZ6_17_1_, msubsectio1_.ZZ_DT_UPDATED as ZZ7_17_1_, msubsectio1_.ZZ_UPDATED_USER as ZZ8_17_1_, mchannel3_.NM_CHANNEL as NM2_11_2_, mchannel3_.NM_CHANNEL_EN as NM3_11_2_, msalesgrp2_.NM_SALES_GRP as NM2_25_3_, msalesgrp2_.CD_SUB_BRANCH as CD3_25_3_, msalesgrp2_.NM_SUB_BRANCH as NM4_25_3_, msalesgrp2_.CD_BRANCH as CD5_25_3_, msalesgrp2_.NM_BRANCH as NM6_25_3_, viewalluse4_.NM_USER as NM2_19_4_, viewalluse4_.MAIL as MAIL19_4_, viewalluse4_.TEL as TEL19_4_, viewalluse4_.TYP_USER as TYP5_19_4_
from M_DEALER_APPLY mdealerapp0_
inner join M_SUB_SECTION msubsectio1_ on mdealerapp0_.CD_SUB_SECTION=msubsectio1_.CD_SUB_SECTION
inner join M_CHANNEL mchannel3_ on msubsectio1_.CD_CHANNEL=mchannel3_.CD_CHANNEL
inner join M_BRANCH msalesgrp2_ on mdealerapp0_.CD_SALES_GRP=msalesgrp2_.CD_SALES_GRP
left outer join VIEW_ALL_USER viewalluse4_ on mdealerapp0_.SALESMAN=viewalluse4_.USER_ID
where (exists (
select mdealerapp5_.SEQ_DEALER
from M_DEALER_APPLY mdealerapp5_, M_USER_BRANCH muserbranc6_, M_BRANCH msalesgrp7_, VIEW_SUB_BRANCH msubbranch8_
where mdealerapp5_.CD_SALES_GRP=msalesgrp7_.CD_SALES_GRP
and msalesgrp7_.CD_SUB_BRANCH=msubbranch8_.CD_SUB_BRANCH
and 1=1
and msubbranch8_.CD_BRANCH=muserbranc6_.CD_BRANCH
and muserbranc6_.USER_ID='200009'
and muserbranc6_.CD_BIZ='2'
and mdealerapp5_.SEQ_DEALER=mdealerapp0_.SEQ_DEALER)
or exists (
select mdealerapp9_.SEQ_DEALER
from M_DEALER_APPLY mdealerapp9_, M_DEALER mdealer10_, M_USER_BRANCH muserbranc11_, M_BRANCH msalesgrp12_, VIEW_SUB_BRANCH msubbranch13_
where mdealer10_.CD_SALES_GRP=msalesgrp12_.CD_SALES_GRP
and msalesgrp12_.CD_SUB_BRANCH=msubbranch13_.CD_SUB_BRANCH
and 1=1
and mdealerapp9_.CD_DEALER_FROM=mdealer10_.CD_DEALER
and msubbranch13_.CD_BRANCH=muserbranc11_.CD_BRANCH
and muserbranc11_.USER_ID='200009'
and muserbranc11_.CD_BIZ='2'
and mdealerapp9_.SEQ_DEALER=mdealerapp0_.SEQ_DEALER)
)
and (exists (
select tsfeapprov14_.APPR_USERID
from T_SFE_APPROVAL_IT tsfeapprov14_
where tsfeapprov14_.APPR_USERID='200009'
and tsfeapprov14_.CD_BIZ='2'
and tsfeapprov14_.POS=mdealerapp0_.CUR_POS
and tsfeapprov14_.SEQ_DELIV=mdealerapp0_.SEQ_DEALER
and mdealerapp0_.ST_DEALER_APP=4) or mdealerapp0_.CREATE_USER='200009' and mdealerapp0_.CD_CREATE_BIZ='2' and mdealerapp0_.ST_DEALER_APP=1
)
and mdealerapp0_.CREATE_USER<>'SFE0201B02' order by mdealerapp0_.SEQ_DEALER ) where rownum <= 10;

Both the queries have identical conditions but there seems be a lot of difference in the performance. The data query seems fine and returns me results in about 0.2 - 1 second. But the count query seems to taking inadvertently long. It executes in 120-130 seconds.

Could someone please have a look at the above queries and help me out with what could possibly be wrong with the count query?

Thanks in advance!

Thanks & Regards,
Shashank

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-18 : 10:32:28

While they have identical where conditions, you are doing inner join with a few other tables in the second query. That can eliminate a lot of rows. Logically, the query processer computes the results of the join and then feeds that to the where clause. Try the second query without the joins and after removing columns from the joined tables, and you may see that that is equally slower.

To figure out why it is slow and to optimize it, you need to look at the query plan and see what parts of the query are causing problems.

shashankkoul
Starting Member

5 Posts

Posted - 2013-04-18 : 15:31:22

quote:
Originally posted by James K

While they have identical where conditions, you are doing inner join with a few other tables in the second query. That can eliminate a lot of rows. Logically, the query processer computes the results of the join and then feeds that to the where clause. Try the second query without the joins and after removing columns from the joined tables, and you may see that that is equally slower.

To figure out why it is slow and to optimize it, you need to look at the query plan and see what parts of the query are causing problems.

Thanks for the useful info!

In my case, the joins before the where clause will actually not eliminate any records as they're placed only to fetch the data from the respective tables for the corresponding records in main table i.e. M_DEALER_APPLY. Anyhow, I tried running the first count query with those extra joins, but the count query is still as slow. Also, in fact the second data query runs faster after removing those join conditions as eliminating those joins reduces the amount of data retrieved.

I also ran the explain plan for both the queries. Unfortunately, I've never worked with explain plans before, hence not in a position to understand them.

Explain plan for slow count query:
[url]https://www.box.com/s/0vtkzesdro1qp8cl4jf5[/url]

Explain plan for fast data query:
[url]https://www.box.com/s/i15lh3nodp8diurklsc5[/url]
[url]https://www.box.com/s/3sizesfjiur3qb7wl7oa[/url]

Any help in understanding these would be appreciable

Thanks & Regards,
Shashank

chadmat
The Chadinator

1974 Posts

Posted - 2013-04-18 : 15:43:27

Not sure where you got these execution plans, not what I'm used to seeing, however the one thing I notice is the slow query accesses about 6MB of data from M_DEALER_APPLY, and the quick one only about 250k.

-Chad

shashankkoul
Starting Member

5 Posts

Posted - 2013-04-19 : 01:47:35

quote:
Originally posted by chadmat

Not sure where you got these execution plans, not what I'm used to seeing, however the one thing I notice is the slow query accesses about 6MB of data from M_DEALER_APPLY, and the quick one only about 250k.

-Chad

It's actually the other way round. If you check from the explain plans, the count query (slower one) is accessing only 250426 bytes (~250KB) data from M_DEALER_APPLY, whereas the data query (faster one) is accessing 6328948 bytes (~6MB) data from M_DEALER_APPLY.

I got those explain plans from PL/SQL Developer. Could you please let me know if there are any alternatives for the same?

Thanks & Regards,
Shashank

shashankkoul
Starting Member

5 Posts

Posted - 2013-04-19 : 02:37:53

Posting Explain plans from SQL developer

Count Query (Slower):
Plan hash value: 1705640374

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 381 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 22 | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL | M_DEALER_APPLY | 11383 | 244K| 381 (1)| 00:00:05 |
| 4 | NESTED LOOPS | | 1 | 44 | 383 (1)| 00:00:05 |
| 5 | NESTED LOOPS | | 19 | 589 | 383 (1)| 00:00:05 |
| 6 | NESTED LOOPS | | 1 | 24 | 382 (1)| 00:00:05 |
|* 7 | TABLE ACCESS FULL | M_DEALER_APPLY | 1 | 14 | 381 (1)| 00:00:05 |
| 8 | TABLE ACCESS BY INDEX ROWID | M_BRANCH | 1 | 10 | 1 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | PK_M_BRANCH | 1 | | 0 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | IDX_M_BRANCH | 16 | 112 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | P_M_USER_BRANCH | 1 | 13 | 0 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 55 | 384 (1)| 00:00:05 |
| 13 | NESTED LOOPS | | 1 | 42 | 384 (1)| 00:00:05 |
| 14 | NESTED LOOPS | | 1 | 35 | 383 (1)| 00:00:05 |
| 15 | NESTED LOOPS | | 1 | 25 | 382 (1)| 00:00:05 |
|* 16 | TABLE ACCESS FULL | M_DEALER_APPLY | 1 | 11 | 381 (1)| 00:00:05 |
| 17 | TABLE ACCESS BY INDEX ROWID| M_DEALER | 1 | 14 | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PK_M_DEALER | 1 | | 0 (0)| 00:00:01 |
| 19 | TABLE ACCESS BY INDEX ROWID | M_BRANCH | 1 | 10 | 1 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | PK_M_BRANCH | 1 | | 0 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | IDX_M_BRANCH | 16 | 112 | 1 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | P_M_USER_BRANCH | 1 | 13 | 0 (0)| 00:00:01 |
|* 23 | FILTER | | | | | |
|* 24 | INDEX RANGE SCAN | IDX_T_SFE_APPROVAL_IT_0401 | 1 | 27 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(( EXISTS (SELECT 0 FROM VSOP."M_BRANCH" "M_BRANCH","M_BRANCH"
"MSALESGRP3_","M_USER_BRANCH" "MUSERBRANC2_","M_DEALER_APPLY" "MDEALERAPP1_" WHERE
"MDEALERAPP1_"."SEQ_DEALER"=:B1 AND "CD_BRANCH"="MUSERBRANC2_"."CD_BRANCH" AND
"MUSERBRANC2_"."CD_BIZ"=2 AND "MUSERBRANC2_"."USER_ID"=U'200009' AND
"MDEALERAPP1_"."CD_SALES_GRP"="MSALESGRP3_"."CD_SALES_GRP" AND
"MSALESGRP3_"."CD_SUB_BRANCH"="CD_SUB_BRANCH") OR EXISTS (SELECT 0 FROM VSOP."M_BRANCH"
"M_BRANCH","M_BRANCH" "MSALESGRP8_","M_USER_BRANCH" "MUSERBRANC7_","M_DEALER"
"MDEALER6_","M_DEALER_APPLY" "MDEALERAPP5_" WHERE "MDEALERAPP5_"."CD_DEALER_FROM" IS NOT NULL AND
"MDEALERAPP5_"."SEQ_DEALER"=:B2 AND "MDEALERAPP5_"."CD_DEALER_FROM"="MDEALER6_"."CD_DEALER" AND
"CD_BRANCH"="MUSERBRANC7_"."CD_BRANCH" AND "MUSERBRANC7_"."CD_BIZ"=2 AND
"MUSERBRANC7_"."USER_ID"=U'200009' AND "MDEALER6_"."CD_SALES_GRP"="MSALESGRP8_"."CD_SALES_GRP" AND
"MSALESGRP8_"."CD_SUB_BRANCH"="CD_SUB_BRANCH")) AND ("MDEALERAPP0_"."CREATE_USER"=U'200009' AND
"MDEALERAPP0_"."CD_CREATE_BIZ"=2 AND TO_NUMBER("MDEALERAPP0_"."ST_DEALER_APP")=1 OR EXISTS (SELECT 0
FROM "T_SFE_APPROVAL_IT" "TSFEAPPROV10_" WHERE TO_NUMBER(:B3)=4 AND "TSFEAPPROV10_"."POS"=:B4 AND
"TSFEAPPROV10_"."CD_BIZ"=2 AND "TSFEAPPROV10_"."APPR_USERID"='200009' AND
SYS_OP_C2C("TSFEAPPROV10_"."SEQ_DELIV")=:B5)))
3 - filter("MDEALERAPP0_"."CREATE_USER"<>U'SFE0201B02')
7 - filter("MDEALERAPP1_"."SEQ_DEALER"=:B1)
9 - access("MDEALERAPP1_"."CD_SALES_GRP"="MSALESGRP3_"."CD_SALES_GRP")
10 - access("MSALESGRP3_"."CD_SUB_BRANCH"="CD_SUB_BRANCH")
11 - access("MUSERBRANC2_"."USER_ID"=U'200009' AND "MUSERBRANC2_"."CD_BIZ"=2 AND
"CD_BRANCH"="MUSERBRANC2_"."CD_BRANCH")
16 - filter("MDEALERAPP5_"."CD_DEALER_FROM" IS NOT NULL AND "MDEALERAPP5_"."SEQ_DEALER"=:B1)
18 - access("MDEALERAPP5_"."CD_DEALER_FROM"="MDEALER6_"."CD_DEALER")
20 - access("MDEALER6_"."CD_SALES_GRP"="MSALESGRP8_"."CD_SALES_GRP")
21 - access("MSALESGRP8_"."CD_SUB_BRANCH"="CD_SUB_BRANCH")
22 - access("MUSERBRANC7_"."USER_ID"=U'200009' AND "MUSERBRANC7_"."CD_BIZ"=2 AND
"CD_BRANCH"="MUSERBRANC7_"."CD_BRANCH")
23 - filter(TO_NUMBER(:B1)=4)
24 - access("TSFEAPPROV10_"."APPR_USERID"='200009' AND "TSFEAPPROV10_"."CD_BIZ"=2 AND
"TSFEAPPROV10_"."POS"=:B1)
filter(SYS_OP_C2C("TSFEAPPROV10_"."SEQ_DELIV")=:B1)

Note
-----
- 'PLAN_TABLE' is old version

Data Query (faster):
Plan hash value: 1345404573

-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1031 | 1170 (1)| 00:00:15 |
| 1 | SORT ORDER BY | | 1 | 1031 | 1170 (1)| 00:00:15 |
|* 2 | FILTER | | | | | |
| 3 | NESTED LOOPS | | 1 | 1031 | 1169 (1)| 00:00:15 |
| 4 | NESTED LOOPS | | 1 | 985 | 1168 (1)| 00:00:15 |
| 5 | NESTED LOOPS | | 1 | 964 | 1167 (1)| 00:00:15 |
| 6 | NESTED LOOPS OUTER | | 1 | 892 | 1166 (1)| 00:00:14 |
|* 7 | HASH JOIN | | 1 | 566 | 1162 (1)| 00:00:14 |
| 8 | VIEW | VW_SQ_1 | 868 | 8680 | 779 (1)| 00:00:10 |
| 9 | HASH UNIQUE | | 1 | 38214 | 779 (51)| 00:00:10 |
| 10 | UNION-ALL | | | | | |
| 11 | NESTED LOOPS | | 2 | 110 | 388 (1)| 00:00:05 |
| 12 | NESTED LOOPS | | 2 | 96 | 386 (1)| 00:00:05 |
| 13 | NESTED LOOPS | | 2 | 76 | 384 (1)| 00:00:05 |
| 14 | MERGE JOIN CARTESIAN | | 2 | 48 | 382 (1)| 00:00:05 |
|* 15 | INDEX RANGE SCAN | P_M_USER_BRANCH | 1 | 13 | 1 (0)| 00:00:01 |
| 16 | BUFFER SORT | | 30 | 330 | 381 (1)| 00:00:05 |
|* 17 | TABLE ACCESS FULL | M_DEALER_APPLY | 30 | 330 | 381 (1)| 00:00:05 |
| 18 | TABLE ACCESS BY INDEX ROWID| M_DEALER | 1 | 14 | 1 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | PK_M_DEALER | 1 | | 0 (0)| 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID | M_BRANCH | 1 | 10 | 1 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | PK_M_BRANCH | 1 | | 0 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | IDX_M_BRANCH | 1 | 7 | 1 (0)| 00:00:01 |
|* 23 | HASH JOIN | | 866 | 38104 | 390 (1)| 00:00:05 |
|* 24 | HASH JOIN | | 59 | 1770 | 9 (12)| 00:00:01 |
| 25 | NESTED LOOPS | | 4 | 80 | 3 (0)| 00:00:01 |
| 26 | INDEX FAST FULL SCAN | IDX_M_BRANCH | 775 | 5425 | 3 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | P_M_USER_BRANCH | 1 | 13 | 0 (0)| 00:00:01 |
| 28 | TABLE ACCESS FULL | M_BRANCH | 775 | 7750 | 5 (0)| 00:00:01 |
| 29 | TABLE ACCESS FULL | M_DEALER_APPLY | 11409 | 155K| 381 (1)| 00:00:05 |
|* 30 | TABLE ACCESS FULL | M_DEALER_APPLY | 11383 | 6180K| 382 (1)| 00:00:05 |
| 31 | VIEW | VIEW_ALL_USER | 1 | 326 | 4 (0)| 00:00:01 |
| 32 | UNION ALL PUSHED PREDICATE | | | | | |
| 33 | TABLE ACCESS BY INDEX ROWID | M_USER | 1 | 71 | 2 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | PK_M_USER | 1 | | 1 (0)| 00:00:01 |
| 35 | NESTED LOOPS ANTI | | 1 | 65 | 2 (0)| 00:00:01 |
| 36 | TABLE ACCESS BY INDEX ROWID | M_USER_EHR | 1 | 51 | 2 (0)| 00:00:01 |
|* 37 | INDEX UNIQUE SCAN | PK_M_USER_EHR | 1 | | 1 (0)| 00:00:01 |
|* 38 | INDEX UNIQUE SCAN | PK_M_USER | 1 | 14 | 0 (0)| 00:00:01 |
| 39 | TABLE ACCESS BY INDEX ROWID | M_SUB_SECTION | 1 | 72 | 1 (0)| 00:00:01 |
|* 40 | INDEX UNIQUE SCAN | PK_M_SUB_SECTION | 1 | | 0 (0)| 00:00:01 |
| 41 | TABLE ACCESS BY INDEX ROWID | M_CHANNEL | 1 | 21 | 1 (0)| 00:00:01 |
|* 42 | INDEX UNIQUE SCAN | PK_M_CHANNEL | 1 | | 0 (0)| 00:00:01 |
| 43 | TABLE ACCESS BY INDEX ROWID | M_BRANCH | 1 | 46 | 1 (0)| 00:00:01 |
|* 44 | INDEX UNIQUE SCAN | PK_M_BRANCH | 1 | | 0 (0)| 00:00:01 |
|* 45 | FILTER | | | | | |
|* 46 | INDEX RANGE SCAN | IDX_T_SFE_APPROVAL_IT_0401 | 1 | 27 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("MDEALERAPP0_"."CREATE_USER"=U'200009' AND "MDEALERAPP0_"."CD_CREATE_BIZ"=2 AND
TO_NUMBER("MDEALERAPP0_"."ST_DEALER_APP")=1 OR EXISTS (SELECT 0 FROM "T_SFE_APPROVAL_IT" "TSFEAPPROV14_"
WHERE TO_NUMBER(:B1)=4 AND "TSFEAPPROV14_"."POS"=:B2 AND "TSFEAPPROV14_"."CD_BIZ"=2 AND
"TSFEAPPROV14_"."APPR_USERID"='200009' AND SYS_OP_C2C("TSFEAPPROV14_"."SEQ_DELIV")=:B3))
7 - access("VW_COL_1"="MDEALERAPP0_"."SEQ_DEALER")
15 - access("MUSERBRANC11_"."USER_ID"=U'200009' AND "MUSERBRANC11_"."CD_BIZ"=2)
17 - filter("MDEALERAPP9_"."CD_DEALER_FROM" IS NOT NULL)
19 - access("MDEALERAPP9_"."CD_DEALER_FROM"="MDEALER10_"."CD_DEALER")
21 - access("MDEALER10_"."CD_SALES_GRP"="MSALESGRP12_"."CD_SALES_GRP")
22 - access("MSALESGRP12_"."CD_SUB_BRANCH"="CD_SUB_BRANCH" AND "CD_BRANCH"="MUSERBRANC11_"."CD_BRANCH")
23 - access("MDEALERAPP5_"."CD_SALES_GRP"="MSALESGRP7_"."CD_SALES_GRP")
24 - access("MSALESGRP7_"."CD_SUB_BRANCH"="CD_SUB_BRANCH")
27 - access("MUSERBRANC6_"."USER_ID"=U'200009' AND "MUSERBRANC6_"."CD_BIZ"=2 AND
"CD_BRANCH"="MUSERBRANC6_"."CD_BRANCH")
30 - filter("MDEALERAPP0_"."CREATE_USER"<>U'SFE0201B02')
34 - access("U"."USER_ID"="MDEALERAPP0_"."SALESMAN")
37 - access("E"."USER_ID"="MDEALERAPP0_"."SALESMAN")
38 - access("MU"."USER_ID"="MDEALERAPP0_"."SALESMAN")
filter("MU"."USER_ID"="E"."USER_ID")
40 - access("MDEALERAPP0_"."CD_SUB_SECTION"="MSUBSECTIO1_"."CD_SUB_SECTION")
42 - access("MSUBSECTIO1_"."CD_CHANNEL"="MCHANNEL3_"."CD_CHANNEL")
44 - access("MDEALERAPP0_"."CD_SALES_GRP"="MSALESGRP2_"."CD_SALES_GRP")
45 - filter(TO_NUMBER(:B1)=4)
46 - access("TSFEAPPROV14_"."APPR_USERID"='200009' AND "TSFEAPPROV14_"."CD_BIZ"=2 AND
"TSFEAPPROV14_"."POS"=:B1)
filter(SYS_OP_C2C("TSFEAPPROV14_"."SEQ_DELIV")=:B1)

Note
-----
- 'PLAN_TABLE' is old version

Could anyone please have a look and help me out?

Thanks in advance!

Thanks & Regards,
Shashank

chadmat
The Chadinator

1974 Posts

Posted - 2013-04-19 : 03:48:39

Is this Oracle?

shashankkoul
Starting Member

5 Posts

Posted - 2013-04-19 : 05:13:24