【DB筆試面試618】在Oracle中，「OR擴展」可以有查詢轉換嗎？
- 2019 年 10 月 10 日
- 筆記
題目部分
在Oracle中，「OR擴展」可以有查詢轉換嗎？
♣
答案部分
同一欄位：
LHR@orclasm > SELECT * FROM SCOTT.EMP A WHERE A.EMPNO =7369 OR A.EMPNO=1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 Execution Plan ---------------------------------------------------------- Plan hash value: 2355049923 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 | | 1 | INLIST ITERATOR | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 2 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."EMPNO"=1 OR "A"."EMPNO"=7369) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 23 recursive calls 5 db block gets 3 consistent gets 0 physical reads 1628 redo size 1025 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed LHR@orclasm > SELECT /*+OR_EXPAND(A EMPNO)*/ * FROM SCOTT.EMP A WHERE A.EMPNO =7369 OR A.EMPNO=1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 Execution Plan ---------------------------------------------------------- Plan hash value: 2259546459 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 174 | 2 (0)| 00:00:01 | | 1 | CONCATENATION | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 1 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 1 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."EMPNO"=1) 5 - access("A"."EMPNO"=7369) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 23 recursive calls 4 db block gets 3 consistent gets 0 physical reads 1560 redo size 1021 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed LHR@orclasm >
不同欄位：
LHR@orclasm > SELECT * FROM SCOTT.EMP A WHERE A.EMPNO =7369 OR A.ENAME='DBA'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 87 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("A"."EMPNO"=7369 OR "A"."ENAME"='DBA') Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 28 recursive calls 4 db block gets 16 consistent gets 0 physical reads 1544 redo size 1021 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed LHR@orclasm > SELECT /*+OR_EXPAND(A EMPNO)*/ * FROM SCOTT.EMP A WHERE A.EMPNO =7369 OR A.ENAME='DBA'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 Execution Plan ---------------------------------------------------------- Plan hash value: 2453891490 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 174 | 3 (0)| 00:00:01 | | 1 | CONCATENATION | | | | | | |* 2 | TABLE ACCESS FULL | EMP | 1 | 87 | 2 (0)| 00:00:01 | |* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 1 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("A"."ENAME"='DBA') 3 - filter(LNNVL("A"."ENAME"='DBA')) 4 - access("A"."EMPNO"=7369) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 31 recursive calls 4 db block gets 25 consistent gets 0 physical reads 1560 redo size 1021 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。
