ORACLE-Select语句执行顺序及如何提高Oracle 基本查询效率.doc

ORACLE-SelectOracle ORCALE/blog/static/1176427201110345049428/OracleSqlsql1234 SQL 56 ORACLE ORACLE 7 ORACLE 89OracleSQL(shared buffer pool)SQL(), Oracle . SQLSQLOracleSQLSQLSQLSelectSQLSQLFROMSELECTSQL(8) SELECT (9) DISTINCT (11) (1) FROM (3) JOIN (2) ON (4) WHERE (5) GROUP BY (6) WITH CUBE | ROLLUP(7) HAVING (10) ORDER BY ()1 FROMFROM()VT12 ONVT1ONTV23 OUTER (JOIN):OUTER JOIN(CROSS JOININNER JOIN)VT2TV3FROM134 WHERETV3WHEREtrueTV45 GROUP BYGROUP BYTV4TV56 CUTE|ROLLUPVT5VT67 HAVINGVT6HAVINGtrueVT7Having 8 SELECTSELECTVT89 DISTINCTVT8VT910ORDER BYVT9ORDER BY(VC10)11TOPVC10TV11SQLSQLA(1)SELECT *(2)SQL, Column.,ColumnB(1)WHEREWHERESQL(2) SELECT TOP N * -WHEREABCDUPDATEEUPDATEDELETE+INSERTABCDE(1)(2)()FSELECT INTOCREATE TABLE + INSERT INTOSELECT INTOCREATE TABLE + INSERT INTOSELECT INTOTEMPDBSYSOBJECTSSYSINDEXESSYSCOLUMNSCREATE TABLE + INSERT INTOSELECT INTO SELECT SELECTINSERTUPDATEDELETE INNOT INEXISTSNOT EXISTS (1)ANOT INNOT EXISTSLEFT JOINSELECT PUB_NAMEFROM PUBLISHERSWHERE PUB_ID NOT IN (SELECT PUB_ID FROM TITLES WHERE TYPE = BUSINESS)SELECT A.PUB_NAMEFROM PUBLISHERS A LEFT JOIN TITLES B ON B.TYPE = BUSINESS AND A.PUB_ID=B. PUB_IDWHERE B.PUB_ID IS NULL(2)SELECT TITLEFROM TITLESWHERE NOT EXISTS (SELECT TITLE_ID FROM SALES WHERE TITLE_ID = TITLES.TITLE_ID)SELECT TITLEFROM TITLES LEFT JOIN SALES ON SALES.TITLE_ID = TITLES.TITLE_IDWHERE SALES.TITLE_ID IS NULLB INEXISTSINNER JOIN SELECT PUB_NAMEFROM PUBLISHERSWHERE PUB_ID IN (SELECT PUB_ID FROM TITLESWHERE TYPE = BUSINESS)SELECT DISTINCT A.PUB_NAMEFROM PUBLISHERS A INNER JOIN TITLES B ON B.TYPE = BUSINESS AND A.PUB_ID=B. PUB_IDC INEXISTSSELECT PUB_NAMEFROM PUBLISHERSWHERE PUB_ID IN (SELECT PUB_ID FROM TITLES WHERE TYPE = BUSINESS)SELECT PUB_NAMEFROM PUBLISHERSWHERE EXISTS (SELECT 1 FROM TITLES WHERE TYPE = BUSINESS AND PUB_ID= PUBLISHERS.PUB_ID) DCOUNT(*)LEFT JOINEXISTSSELECT JOB_DESCFROM JOBSWHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)=0SELECT JOBS.JOB_DESCFROM JOBS LEFT JOIN EMPLOYEE ON EMPLOYEE.JOB_ID=JOBS.JOB_IDWHERE EMPLOYEE.EMP_ID IS NULLSELECT JOB_DESC FROM JOBS WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)0SELECT JOB_DESC FROM JOBSWHERE EXISTS (SELECT 1 FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID),SQLSERVERSQLASELECT ID FROM T WHERE NUM/2=100:SELECT ID FROM T WHERE NUM=100*2-SELECT ID FROM T WHERE NUM/2=NUM1NUM:SELECT ID FROM T WHERE NUM=NUM1*2NUM1-SELECT , FROM WHERE 100*+=2010*100+10SELECT , FROM WHERE =2010 AND=10B WHERE CONVERT(VARCHAR(10), ,120)=2010-07-15WHERE=2010-07-15 AND 2010-07-16 IS NULLWHERE ISNULL(,):WHEREWHERE ISNULL(,)=WHERE ISNULL(,F) =T: WHERE=TWHERE ISNULL(,F)TC WHERE LEFT(NAME, 3)=ABC WHERE SUBSTRING(NAME,1, 3)=ABC: WHERE NAME LIKE ABC%WHERE DATEDIFF(DAY, ,2010-06-30)=0:WHERE =2010-06-30 AND 0:WHERE =0:WHERE 2010-07-01WHERE DATEDIFF(DAY, ,2010-06-30)=2010-07-01WHERE DATEDIFF(DAY, ,2010-06-30)=2010-06-30DWHERE FAME+ . +LNAME=HAIWEI.YANG:WHERE FNAME=HAIWEI AND LNAME=YANGABCONWHEREHAVING ON WHEREHAVINGONWHERE HAVINGSUMONWHEREHAVING12INNER JOIN3LEFT JOIN (RIGHT JOIN LEFT JOIN )4CROSS JOINAINBUNIONUNION ALL-UNION ALLCDISTINCTDTRUNCATE TABLE DELETE E-starto