Oracle????????????????????????~????????????????????
- by Yusuke.Yamamoto
RDBMS ???????·?????????????????????????????????????????????????????????????????????????
????????Oracle ?????????????????????????????????? Oracle Database ????????????????????????????????
?????????????????????
????Oracle????????????????????????????????????????????????????????????????????????????
?????????????? Oracle Database ????????????????????????
??????????????????????????????????2?????????????
1. ??????(Query Transformation)
Query Transformation ???????SQL??????????????????SQL?????????????????????
Query Transformation ???Predicate Transformation ? Common Sub-expression Elimination (CSE), Order-BY Elimination (OBYE), Outer Join Elimination (OJE), Simple View Meging (SVM), Predicate Move around (PM), Complex View Merging (CVM), Sub-query Unnesting (SU), Join Predicate Push Down (JPPD) ???? OR Expansion, Star Transformation (ST) ?????????????
···???????????????????????????????????????????????????? Predicate Transformation ?????? Transitive Predicate Generation ?????????????
?????????????SQL???deptno ? 10 ?????????????????????????????
select e.ename, d.loc from emp e, dept d
where e.deptno=d.deptno and e.deptno=10;
???????????????emp ??? deptno=10 ??????????????dept ??? d.deptno=10 ??????????????????? emp ?? deptno=10 ????????????????????emp ?? deptno=10 ??????10???????10? dept ????????????dept ??20???????????????????????10?*20?=200?????(??????????·?????????)?
??SQL?? Transitive Predicate Generation ??????SQL?????????????????
select e.ename, d.location from emp e, dept d
where e.deptno=d.deptno and e.deptno=10 and d.deptno=10;
^^^^^^^^^^^
??????dept ?????? deptno=10 ??????????????????????????10?*1?=10(dept.deptno ?unique????)?1/20????????????????1/20????????????????10??????????30???????????????Query Transformation ????????????????????????????
?:??????????? dept ?? 1-row table ??????dept ?? driving ???(Outer Table)??? emp ?? probe ???(Inner Table)????????????1?*10?=10 ????????????????????????????????????????????????????????1/20?????????????
?????? Query Transformation ??????SQL????????????????????????????????? Transformation ???????????????????????????????????
2. ????·????(Access Path Analysis)
Access Path Analysis ??Query Transformation ??SQL????????????(Access Path)?????????(Join Method)?????(Join Order)??????????
??????????????????(FTS)?ROWID?????????????????????????????·?????(Nested Loop Join)???????(Hash Join)????/?????(Sort Merge Join)?????????????????????????????????????????????????????????????????????????
Oracle Database ????????? Query Transformation ???? Logical Optimizer?Access Path Analysis ???? Physical Optimizer ?????????
???????????????????????????????????????????????????????????????????????????????????????????????????
?????????????????????????????????????????????????????
Oracle Database ????????????????????? "Oracle ????????"
?????????? Sustaining Engineering?? ?(??? ???)
???????????????? Sustaining Engineering ????????????????????????Oracle Database ????????????????????????
?????????????????????Ruby?????????????????????????
Oracle????????????????????????!
Oracle?????????????
Oracle????????????????????????