??ЧSQL???????

????No SQL??No cost??SQL????????????????????????????SQLд???????????????????????????α???????????β????????Ч??SQL??????????????????????SQL???????????м????????????д??ü?·???????????ú????????洢???????????????ε??????ε????????????????????????α?д??Ч??SQL????????????????????????????????????????д??ЧSQL??????????????????????????????????????

?????????д??ЧSQL???

????1???????Ч????????????????RBO????

????ORACLE??????????????????????????FROM????е?????????FROM????к????????????????????????????FROM????????????????????????????????????????FROM?????????????Oracle?????????????FROM????к???????????????????????????????????FROM????к???????????????д????????м??????????????????к????????к?????????3????????????????????????????intersection table????????????????????????????????????????

???????????????ó?????scott??hr???μ????????

?????? EMP ??14?????

?????? DEPT ??4?????

SELECT  /*+ rule */ COUNT( * )  FROM   emp?? dept;          --??Ч??д??

scott@CNMMBO> set autotrace traceonly stat;
scott@CNMMBO> SELECT  /*+ rule */ COUNT( * )  FROM   emp?? dept;

Elapsed: 00:00:00.14

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         35  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SELECT  /*+ rule */ COUNT( * )  FROM   dept?? emp;         --??Ч??д??
scott@CNMMBO> SELECT  /*+ rule */ COUNT( * )  FROM   dept?? emp;

Elapsed: 00:00:00.02

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        105  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

????2??select ????б??????'*'

????????????SELECT??????г????е?COLUMN????????SQL?????? '*' ?????????????.????????????????????Ч?????.??????ORACLE???????????У? ?? '*' ????????????е??????? ??????????????????????????? ????ζ?????????????

????????????е???????????????????select * ?????????????????á?

????3??????????????????

?????????????SQL???Oracle ?????????????????????????SQL???????????????????????????? ?????????.????????????????????????????????????????????????

????-->???????3????????ù??????7788??7902????????

????-->??? 1 ????Ч????

select ename??job??sal from emp where empno=7788;
select ename??job??sal from emp where empno=7902;

????-->??? 2 ???ε?Ч????

????-->?????????????α??????????????β???????????emp??????Σ???????I/O

  DECLARE
    CURSOR C1(E_NO NUMBER)  IS
    SELECT ename?? job?? sal
    FROM emp
    WHERE empno = E_NO;
  BEGIN
    OPEN C1 (7788);
    FETCH C1 INTO …?? …?? …;
    ..
    OPEN C1 (7902);
    FETCH C1 INTO …?? …?? …;
    CLOSE C1;
  END;

????-->??? 3 ????Ч??

SELECT a.ename
     ?? a.job
     ?? a.sal
     ?? b.ename
     ?? b.job
     ?? b.sal
FROM   emp a?? emp b
WHERE  a.empno = 7788 OR b.empno = 7902;

?????????SQL*Plus??SQL*Forms??Pro*C??????????ARRAYSIZE???????????????????????????????????????????200.

????4?????DECODE????????????????

????-->???decode???????????????????????л??????????????

select count(*)??sum(sal) from emp where deptno=20 and ename like 'SMITH%';
select count(*)??sum(sal) from emp where deptno=30 and ename like 'SMITH%';

????-->??????decode?????????輴?????????????????????????

SELECT COUNT( DECODE( deptno?? 20?? 'x'?? NULL ) ) d20_count
     ?? COUNT( DECODE( deptno?? 30?? 'x'?? NULL ) ) d30_count
     ?? SUM( DECODE( deptno?? 20?? sal?? NULL ) ) d20_sal
     ?? SUM( DECODE( deptno?? 30?? sal?? NULL ) ) d30_sal
FROM   emp
WHERE  ename LIKE 'SMITH%';

??????????DECODE???????????????GROUP BY ??ORDER BY????С?