♣题目 部分
在Oracle中,“OR扩展”可以有查询转换吗?
♣答案部分
同一字段:
1LHR@orclasm > SELECT * FROM SCOTT.EMP A WHERE A.EMPNO =7369 OR A.EMPNO=1; 2 3 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 4---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 5 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 6 7 8Execution Plan 9---------------------------------------------------------- 10Plan hash value: 2355049923 11 12--------------------------------------------------------------------------------------- 13| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 14--------------------------------------------------------------------------------------- 15| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 | 16| 1 | INLIST ITERATOR | | | | | | 17| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 2 (0)| 00:00:01 | 18|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 3 (0)| 00:00:01 | 19--------------------------------------------------------------------------------------- 20 21Predicate Information (identified by operation id): 22--------------------------------------------------- 23 24 3 - access("A"."EMPNO"=1 OR "A"."EMPNO"=7369) 25 26Note 27----- 28 - dynamic sampling used for this statement (level=2) 29 30 31Statistics 32---------------------------------------------------------- 33 23 recursive calls 34 5 db block gets 35 3 consistent gets 36 0 physical reads 37 1628 redo size 38 1025 bytes sent via SQL*Net to client 39 520 bytes received via SQL*Net from client 40 2 SQL*Net roundtrips to/from client 41 0 sorts (memory) 42 0 sorts (disk) 43 1 rows processed 44 45LHR@orclasm > SELECT /*+OR_EXPAND(A EMPNO)*/ * FROM SCOTT.EMP A WHERE A.EMPNO =7369 OR A.EMPNO=1; 46 47 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 48---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 49 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 50 51 52Execution Plan 53---------------------------------------------------------- 54Plan hash value: 2259546459 55 56--------------------------------------------------------------------------------------- 57| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 58--------------------------------------------------------------------------------------- 59| 0 | SELECT STATEMENT | | 2 | 174 | 2 (0)| 00:00:01 | 60| 1 | CONCATENATION | | | | | | 61| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 1 (0)| 00:00:01 | 62|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 | 63| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 1 (0)| 00:00:01 | 64|* 5 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 | 65--------------------------------------------------------------------------------------- 66 67Predicate Information (identified by operation id): 68--------------------------------------------------- 69 70 3 - access("A"."EMPNO"=1) 71 5 - access("A"."EMPNO"=7369) 72 73Note 74----- 75 - dynamic sampling used for this statement (level=2) 76 77 78Statistics 79---------------------------------------------------------- 80 23 recursive calls 81 4 db block gets 82 3 consistent gets 83 0 physical reads 84 1560 redo size 85 1021 bytes sent via SQL*Net to client 86 520 bytes received via SQL*Net from client 87 2 SQL*Net roundtrips to/from client 88 0 sorts (memory) 89 0 sorts (disk) 90 1 rows processed 91 92LHR@orclasm >
不同字段:
1LHR@orclasm > SELECT * FROM SCOTT.EMP A WHERE A.EMPNO =7369 OR A.ENAME='DBA'; 2 3 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 4---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 5 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 6 7 8Execution Plan 9---------------------------------------------------------- 10Plan hash value: 3956160932 11 12-------------------------------------------------------------------------- 13| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 14-------------------------------------------------------------------------- 15| 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 | 16|* 1 | TABLE ACCESS FULL| EMP | 1 | 87 | 3 (0)| 00:00:01 | 17-------------------------------------------------------------------------- 18 19Predicate Information (identified by operation id): 20--------------------------------------------------- 21 22 1 - filter("A"."EMPNO"=7369 OR "A"."ENAME"='DBA') 23 24Note 25----- 26 - dynamic sampling used for this statement (level=2) 27 28 29Statistics 30---------------------------------------------------------- 31 28 recursive calls 32 4 db block gets 33 16 consistent gets 34 0 physical reads 35 1544 redo size 36 1021 bytes sent via SQL*Net to client 37 520 bytes received via SQL*Net from client 38 2 SQL*Net roundtrips to/from client 39 0 sorts (memory) 40 0 sorts (disk) 41 1 rows processed 42 43LHR@orclasm > SELECT /*+OR_EXPAND(A EMPNO)*/ * FROM SCOTT.EMP A WHERE A.EMPNO =7369 OR A.ENAME='DBA'; 44 45 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 46---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 47 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 48 49 50Execution Plan 51---------------------------------------------------------- 52Plan hash value: 2453891490 53 54--------------------------------------------------------------------------------------- 55| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 56--------------------------------------------------------------------------------------- 57| 0 | SELECT STATEMENT | | 2 | 174 | 3 (0)| 00:00:01 | 58| 1 | CONCATENATION | | | | | | 59|* 2 | TABLE ACCESS FULL | EMP | 1 | 87 | 2 (0)| 00:00:01 | 60|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 1 (0)| 00:00:01 | 61|* 4 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 | 62--------------------------------------------------------------------------------------- 63 64Predicate Information (identified by operation id): 65--------------------------------------------------- 66 67 2 - filter("A"."ENAME"='DBA') 68 3 - filter(LNNVL("A"."ENAME"='DBA')) 69 4 - access("A"."EMPNO"=7369) 70 71Note 72----- 73 - dynamic sampling used for this statement (level=2) 74 75 76Statistics 77---------------------------------------------------------- 78 31 recursive calls 79 4 db block gets 80 25 consistent gets 81 0 physical reads 82 1560 redo size 83 1021 bytes sent via SQL*Net to client 84 520 bytes received via SQL*Net from client 85 2 SQL*Net roundtrips to/from client 86 0 sorts (memory) 87 0 sorts (disk) 88 1 rows processed