- 最后登录
- 2015-5-21
- 在线时间
- 100 小时
- 威望
- 0
- 金钱
- 345
- 注册时间
- 2012-12-19
- 阅读权限
- 10
- 帖子
- 99
- 精华
- 0
- 积分
- 0
- UID
- 824
|
1#
发表于 2013-1-17 14:43:45
|
查看: 3529 |
回复: 3
本帖最后由 张沛 于 2013-1-17 15:13 编辑
- sql语句中存在外连接是不是就不可能避免全表扫描了?
- 例子explain plan for (
- select AVT.tax_code,AVT.vat_tax_id,CTL.org_id
- from ar_vat_tax_all AVT,ra_customer_trx_lines_all CTL
- where AVT.vat_tax_id(+) = CTL.vat_tax_id
- AND nvl(AVT.org_id(+),-99) = nvl(CTL.org_id,-99)
- )
- 1
- 2 ----------------------------------------------------------------------------------------
- 3 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
- 4 ----------------------------------------------------------------------------------------
- 5 | 0 | SELECT STATEMENT | | 1693K| 45M| 23572 (2)|
- 6 |* 1 | HASH JOIN RIGHT OUTER| | 1693K| 45M| 23572 (2)|
- 7 | 2 | TABLE ACCESS FULL | AR_VAT_TAX_ALL_B | 14 | 266 | 5 (0)|
- 8 | 3 | TABLE ACCESS FULL | RA_CUSTOMER_TRX_LINES_ALL | 1693K| 14M| 23549 (2)|
- 9 ----------------------------------------------------------------------------------------
- 10
- 11 Predicate Information (identified by operation id):
- 12 ---------------------------------------------------
- 13
- 14 1 - access("AVT"."VAT_TAX_ID"(+)="CTL"."VAT_TAX_ID" AND
- 15 NVL("AVT"."ORG_ID"(+),(-99))=NVL("CTL"."ORG_ID",(-99)))
- 16
- 17 Note
- 18 -----
- 19 - 'PLAN_TABLE' is old version
- 注:avt表14行
- ctl表170万行
- 其中select vat_tax_id,org_id from ar_vat_tax_all 返回数据
- vat_tax_id org_id
- 1 10003 103
- 2 10005 103
- 3 10007 103
- 4 10061 136
- 5 10063 136
- 6 10101 103
- 7 10021 136
- 8 10023 136
- 9 10009 104
- 10 10011 104
- 11 10013 104
- 12 10015 109
- 13 10017 109
- 14 10019 109
复制代码 |
|