select ank.sales_code as sales_code, cus.company_name as company_name, cus.branch_name as branch_name, cus.depart_name as depart_name, ank.keyman_name as keyman_name, cus.company_code as company_code, ank.tel_no as tel_no, ank.keyman_post as keyman_post, ank.sales_name as sales_name, ank.sales_type_code as sales_type_code, ank.end_customer_name as end_customer_name, ank.repeat_flag as repeat_flag
from
customer cus,
(select * from anken where complete_flag != 1) ank,
report_latest rep
where
cus.company_code = ank.company_code
and ank.sales_code = rep.sales_code
AND (ank.repeat_flag is null or ank.repeat_flag != 1)
and rep.EMPLOYEE_CODE in (430,438,439,440,446,450,451,458,470,488,104,107,113,114,115,121,133,137,144,150,155,162,164,165,166,170,183,189,190,195,196,197,206,210,225,226,227,232,242,247,248,250,263,271,272,279,282,283,284,288,291,292,301,302,303,304,305,310,319,323,334,340,350,359,360,361,362,364,370,371,376,383,390,391,396,402,405,406,413,747,750,751,754,757,815,529,557,562,564,565,566,567,570,574,579,581,583,584,587,596,597,598)
order by company_code ,repeat_flag ,sales_code desc
from
customer cus,
(select * from anken where complete_flag != 1) ank,
report_latest rep
where
cus.company_code = ank.company_code
and ank.sales_code = rep.sales_code
AND (ank.repeat_flag is null or ank.repeat_flag != 1)
and rep.EMPLOYEE_CODE in (430,438,439,440,446,450,451,458,470,488,104,107,113,114,115,121,133,137,144,150,155,162,164,165,166,170,183,189,190,195,196,197,206,210,225,226,227,232,242,247,248,250,263,271,272,279,282,283,284,288,291,292,301,302,303,304,305,310,319,323,334,340,350,359,360,361,362,364,370,371,376,383,390,391,396,402,405,406,413,747,750,751,754,757,815,529,557,562,564,565,566,567,570,574,579,581,583,584,587,596,597,598)
order by company_code ,repeat_flag ,sales_code desc
cus.company_name as company_name,
cus.branch_name as branch_name,
cus.depart_name as depart_name,
ank.keyman_name as keyman_name,
cus.company_code as company_code,
ank.tel_no as tel_no,
ank.keyman_post as keyman_post,
ank.sales_name as sales_name,
ank.sales_type_code as sales_type_code,
ank.end_customer_name as end_customer_name,
ank.repeat_flag as repeat_flag
from customer cus
inner join anken ank on cus.company_code = ank.company_code
inner join report_latest rep on ank.sales_code = rep.sales_code
where ank.complete_flag != 1
and (ank.repeat_flag is null or ank.repeat_flag != 1)
and rep.EMPLOYEE_CODE in
(430, 438, 439, 440, 446, 450, 451, 458, 470, 488, 104, 107, 113, 114, 115, 121, 133, 137, 144, 150, 155, 162, 164, 165, 166, 170, 183, 189, 190, 195, 196, 197, 206, 210, 225, 226, 227, 232, 242, 247, 248, 250, 263, 271, 272, 279, 282, 283, 284, 288, 291, 292, 301, 302, 303, 304, 305, 310, 319, 323, 334, 340, 350, 359, 360, 361, 362, 364, 370, 371, 376, 383, 390, 391, 396, 402, 405, 406, 413, 747, 750, 751, 754, 757, 815, 529, 557, 562, 564, 565, 566, 567, 570, 574, 579, 581, 583, 584, 587, 596, 597, 598)
order by company_code, repeat_flag, sales_code desc
(select * from anken where complete_flag != 1) ank,
改写成
(select * from anken where complete_flag != 1 and (repeat_flag is null or repeat_flag != 1)) ank,
这张表中的记录好果小的话,
FROM 写成
customer cus,
report_latest rep,
(select * from anken where complete_flag != 1 and (repeat_flag is null or repeat_flag != 1)) ank,这样的话后边的
AND (ank.repeat_flag is null or ank.repeat_flag != 1) 可以去掉了。
sql
/
@?/rdbms/admin/utlxplp.sql贴出来看看..
2.1 SORT ORDER BY
3.1 CONCATENATION
4.100 FILTER
5.1 NESTED LOOPS
6.1 NESTED LOOPS
7.1 TABLE ACCESS BY INDEX ROWID REPORT
8.1 INDEX RANGE SCAN EMPLOYEECODE_ON_REPORT NON-UNIQUE
7.2 TABLE ACCESS BY INDEX ROWID ANKEN
8.1 INDEX UNIQUE SCAN SYS_C00356681 UNIQUE
6.2 TABLE ACCESS BY INDEX ROWID CUSTOMER
7.1 INDEX UNIQUE SCAN SYS_C00356820 UNIQUE
5.2 SORT AGGREGATE
6.1 TABLE ACCESS BY INDEX ROWID REPORT
7.1 INDEX RANGE SCAN SALESCODE_ON_REPORT NON-UNIQUE
4.101 FILTER
5.1 NESTED LOOPS
6.1 NESTED LOOPS
7.1 TABLE ACCESS BY INDEX ROWID REPORT
8.1 INDEX RANGE SCAN EMPLOYEECODE_ON_REPORT NON-UNIQUE
7.2 TABLE ACCESS BY INDEX ROWID ANKEN
8.1 INDEX UNIQUE SCAN SYS_C00356681 UNIQUE
6.2 TABLE ACCESS BY INDEX ROWID CUSTOMER
7.1 INDEX UNIQUE SCAN SYS_C00356820 UNIQUE
5.2 SORT AGGREGATE
6.1 TABLE ACCESS BY INDEX ROWID REPORT
7.1 INDEX RANGE SCAN SALESCODE_ON_REPORT NON-UNIQUE
4.102 FILTER
5.1 NESTED LOOPS
6.1 NESTED LOOPS
7.1 TABLE ACCESS BY INDEX ROWID REPORT
8.1 INDEX RANGE SCAN EMPLOYEECODE_ON_REPORT NON-UNIQUE
7.2 TABLE ACCESS BY INDEX ROWID ANKEN
8.1 INDEX UNIQUE SCAN SYS_C00356681 UNIQUE
6.2 TABLE ACCESS BY INDEX ROWID CUSTOMER
7.1 INDEX UNIQUE SCAN SYS_C00356820 UNIQUE
5.2 SORT AGGREGATE
6.1 TABLE ACCESS BY INDEX ROWID REPORT
7.1 INDEX RANGE SCAN SALESCODE_ON_REPORT NON-UNIQUE
4.1 FILTER
5.1 NESTED LOOPS
6.1 NESTED LOOPS
7.1 TABLE ACCESS BY INDEX ROWID REPORT
8.1 INDEX RANGE SCAN EMPLOYEECODE_ON_REPORT NON-UNIQUE
7.2 TABLE ACCESS BY INDEX ROWID ANKEN
8.1 INDEX UNIQUE SCAN SYS_C00356681 UNIQUE
6.2 TABLE ACCESS BY INDEX ROWID CUSTOMER
7.1 INDEX UNIQUE SCAN SYS_C00356820 UNIQUE
5.2 SORT AGGREGATE
6.1 TABLE ACCESS BY INDEX ROWID REPORT
7.1 INDEX RANGE SCAN SALESCODE_ON_REPORT NON-UNIQUE
4.2 FILTER
5.1 NESTED LOOPS
6.1 NESTED LOOPS
7.1 TABLE ACCESS BY INDEX ROWID REPORT
8.1 INDEX RANGE SCAN EMPLOYEECODE_ON_REPORT NON-UNIQUE
7.2 TABLE ACCESS BY INDEX ROWID ANKEN
8.1 INDEX UNIQUE SCAN SYS_C00356681 UNIQUE
6.2 TABLE ACCESS BY INDEX ROWID CUSTOMER
7.1 INDEX UNIQUE SCAN SYS_C00356820 UNIQUE
5.2 SORT AGGREGATE
6.1 TABLE ACCESS BY INDEX ROWID REPORT
7.1 INDEX RANGE SCAN SALESCODE_ON_REPORT NON-UNIQUE
4.3 FILTER
5.1 NESTED LOOPS
6.1 NESTED LOOPS
7.1 TABLE ACCESS BY INDEX ROWID REPORT
8.1 INDEX RANGE SCAN EMPLOYEECODE_ON_REPORT NON-UNIQUE
7.2 TABLE ACCESS BY INDEX ROWID ANKEN
8.1 INDEX UNIQUE SCAN SYS_C00356681 UNIQUE
6.2 TABLE ACCESS BY INDEX ROWID CUSTOMER
7.1 INDEX UNIQUE SCAN SYS_C00356820 UNIQUE
5.2 SORT AGGREGATE
6.1 TABLE ACCESS BY INDEX ROWID REPORT
7.1 INDEX RANGE SCAN SALESCODE_ON_REPORT NON-UNIQUE
4.4 FILTER
5.1 NESTED LOOPS
6.1 NESTED LOOPS
7.1 TABLE ACCESS BY INDEX ROWID REPORT
8.1 INDEX RANGE SCAN EMPLOYEECODE_ON_REPORT NON-UNIQUE
7.2 TABLE ACCESS BY INDEX ROWID ANKEN
8.1 INDEX UNIQUE SCAN SYS_C00356681 UNIQUE
6.2 TABLE ACCESS BY INDEX ROWID CUSTOMER
7.1 INDEX UNIQUE SCAN SYS_C00356820 UNIQUE
5.2 SORT AGGREGATE
6.1 TABLE ACCESS BY INDEX ROWID REPORT
7.1 INDEX RANGE SCAN SALESCODE_ON_REPORT NON-UNIQUE
4.5......in 个数的循环
4.1 FILTER
5.1 NESTED LOOPS
6.1 NESTED LOOPS
7.1 TABLE ACCESS BY INDEX ROWID REPORT
8.1 INDEX RANGE SCAN EMPLOYEECODE_ON_REPORT NON-UNIQUE
7.2 TABLE ACCESS BY INDEX ROWID ANKEN
8.1 INDEX UNIQUE SCAN SYS_C00356681 UNIQUE
6.2 TABLE ACCESS BY INDEX ROWID CUSTOMER
7.1 INDEX UNIQUE SCAN SYS_C00356820 UNIQUE
5.2 SORT AGGREGATE
6.1 TABLE ACCESS BY INDEX ROWID REPORT
7.1 INDEX RANGE SCAN SALESCODE_ON_REPORT NON-UNIQUE