1. ITPUB ChinaUnix IXPUB

2. ITPUB ChinaUnix IXPUB 3. ITPUB ChinaUnix IXPUB 4. : EN-CORE CO., LTD. (www.en-core.com, www.en-core.com.cn) : : Allianz Tower 3F,45-21, Yeouido-dong, Yeongdeugpo-gu, Seoul, korea : 82-2-754-7301(FAX 82-2-754-7305) : 1997 11 13 : IT : / // ITPUB ChinaUnix IXPUB 5. Consulting SolutionAcademyConsulting ServiceEN-CORE DataWare Data Architecture DataWare DA DA Lab Database DataWare Repository Modeling NewsData QualityDataWare IDOTuning Education Data Upgrade DataWare DQ Books Data GovernanceDataWare Impact SQLOpen ClubDataWare MetaNew Tech DA MasterDataWare ETL RecruitingNext System System MonitoringISP/EAP DW / BI ReportsBAM ITPUB ChinaUnix IXPUB 6. ITPUB ChinaUnix IXPUB 7. Ford HuntAllen 0007Davis 0002 Green 0006 Jones 0004Clarke 0003 Ford 0005Hunt 0008King 0001 ITPUB ChinaUnix IXPUB 8. ITPUB ChinaUnix IXPUB 9. ITPUB ChinaUnix IXPUB 10. SQL Parsing 1 SQL2SQL 3 4 5SQL ITPUB ChinaUnix IXPUB 11. DeptNo12% Sal_Date 2010-01-01~ 2010-12-31Search DeptNoEname EmpNoJobSal_Tot Com_Tot12345KING 7839 PRESIDENT781,360.3010,60012345SCOTT7788 ANALYST268,158.156,10012345FORD 7902 MANAGER101,682.934,500SELECT deptno, y.ename, y.empno, y.job, sal_tot, comm_tot FROM ( SELECT empno, sum(sal_amt) sal_tot, sum(comm) comm_totFROM salary s WHERE s.deptno like 12% AND s.sal_date between 20100101 and 20101231 GROUP BY empno ) x, employee yWHERE y.empno = x.empno DEPTNOsalary(deptno + sal_date) Sal_Date ITPUB ChinaUnix IXPUB 12. 12 DeptNo12%Sal_Date 2005-01-01 ~2005-12-31 Search DeptNo EnameEnameEmpNo EmpNoJob JobSal_TotSal_TotCom_Tot Com_Tot 12345 SMITH KING7369 7839 CLERKPRESIDENT 781,360.30 52,831.3210,600 2,200 12643 MARTIN SCOTT 7900 7788 SALESMANANALYST113,863.15 268,158.154,100 6,100 12742 WARD FORD7521 7902 SALESMANMANAGER156,167.01 101,682.935,200 4,500SELECT deptno into :v_deptno SELECT deptno, y.ename, y.empno, y.job, x.sal_tot, x.comm_tot FROM dept FROMWHERE deptno like 12% ( SELECT empno, sum(sal_amt) sal_tot, sum(comm) comm_tot FROM salary sWHERE s.deptno = :v_deptnoAND s.sal_date between 20100101 and 20101231GROUP BY empno ) x, employee y WHERE y.empno = x.empno ITPUB ChinaUnix IXPUB 13. select col1, col2 from ATLock col1 col2where col1 = 20 commitand rownum = 1for update;10AA 20ABselect col1, col2 commit20 AB from ATwhere col1 = 2020 CMand rownum = 1for update; from AT commit 20 SKwhere col1 = 20 and rownum = 121 BAfor update; . ...AT table ..select col1, col2 from ATwhere col1 = 20and rownum = 1 .......commitfor update; ITPUB ChinaUnix IXPUB 14. CREATE SEQUENCE squ select squ.nextval into :A from dual;start with 1 select rnum, col1, col2increment by 1from (select rownum as rnum, col1, col2maxvalue10from AT where col1 = 20) col1 col2cycle where rnum = :Acache9and rownum = 1;10AA20AB select squ.nextval into :A from dual; select rnum, col1, col220ABfrom (select rownum as rnum,col1,col2 from AT 20CM where col1 = 20) where rnum = :Aand rownum = 1;20SK where rnum = :A and rownum = 1; .. .21BA select squ.nextval into :A from dual;AT table..select rnum, col1, col2. from (select rownum as rnum,col1,col2from AT where col1 = 20) where rnum = :Aand rownum = 1; ITPUB ChinaUnix IXPUB 15. 1230000 00001O123O 1 1230001 00044X123X 4 1230005 00062O123O 21230007 00093X123X 3123 0000O1230010 00101O123O 1123 0001X 11230011 00122X123X 2123 0002X 21230013 00142O123O 2123 0003X1230015 00217X123X 73123 0004X1230022 00221O123O 14123 0005O 0123 .. 123123 0006O 01239990 99999X123X 9123 0007X 1123 0008X 2123 0009X 3123 0010O 0123 0011X 1123 0012X 2123 0013O 0123 0014O 0123 0015X 1123 0016X 2123 0017X 3123 0018X 4123 0019X 5 .123 0020X 6 123 .......... .......... 123 0021X1230015 00206O123 0022O1230021 00211X123 ..1230022 00221O123 9999X123 . . ITPUB ChinaUnix IXPUB 16. OOIndex ScanOXIndex ScanXO Full Scan ITPUB ChinaUnix IXPUB 17. call countcpuelapsed disk query currentrows ------- ------ -------- ---------- ------ ------ -------- ----- Parse 20 0.811848.30000 0 Execute 20 0.01 0.01000 0 Fetch 20 0.74 1.58114 15693 050 ------- ------ -------- ---------- ------ ------ -------- ----- total 60 1.561849.89114 15693 050 ITPUB ChinaUnix IXPUB 18. Literal SQL Hard Parsing Literal SQLselect * from tab where id = 1 Connection con = null; select * from tab where id = 2 Statement stmt = null; select * from tab where id = 3 select * from tab where id = 4 String query = "select * from tab where id = " + id; select * from tab where id = 5 select * from tab where id = 6Parsing stmt = con.createStatement(stmt);select * from tab where id = 7 ResultSet rs = stmt.executeQuery();select * from tab where id = 8select * from tab where id = 9select * from tab where id = 10SQLHard parsingselect * from tab where id = 99999Static SQL(with Bind variable) Soft Parsing Static SQLselect * from tab where id = ? Connection con = null; Bind PreparedStatement pstmt = null; Variable 12 String query = "select * from tab where id = ?"; 3 pstmt = con.prepareStatement(query); 4 pstmt.setInt(1, id); 56 ResultSet rs = pstmt.executeQuery();7891Hard parsingsoft parsing 10 99999 ITPUB ChinaUnix IXPUB 19. ITPUB ChinaUnix IXPUB 20. . . . . . . NoData 1 AAAA 2 BBBB 3 CCCC 25 4 DDDD 5 EEEE6 FFFF 100 7 GGGG 8 HHHH 9 IIII10JJJJ11 KKKK12LLLL .. 1000000ZZZ ITPUB ChinaUnix IXPUB 21. WEB 10 ITPUB ChinaUnix IXPUB 22. : SELECT /*+ index(w cust_name_idx) */ROWIDTOCHAR(rowid) rid, cust_name, FROM cust_table wWHERE :v2 = FIRST AND cust_name like :v1 || %AND rownum =UNION ALLSELECT /*+ use_concat index (x cust_name_idx) */ROWIDTOCHAR(rowid) rid, cust_name, FROM cust_table x / WHERE :v2 = NEXTAND (cust_name > :v3 OR(cust_name = :v3 AND rowid > CHARTOROWID(:v4)))AND cust_name like :v1||% : cust_name_idx(cust_name)AND rownum