January 13, 2011
(Back to the Previous Post in the Series)
I found a couple of more Oracle Database related quizzes on the Internet. These quizzes, I would guess, are designed for Oracle Database 8.1 and earlier. I missed one question in each of the quizzes, but I suspect that I could have missed many more if someone had simply handed the quizzes to me and asked that I answer the questions. I think that I would intentionally answer the questions as if the quiz were designed for Oracle Database 11.2, because the database release version for the quiz is not specified. How well would you do if:
- You answer the questions knowing that the quizzes were designed for Oracle Database 8.1 and earlier?
- You answer the questions believing that the quizzes were designed for the latest release version of Oracle Database?
The Quizzes:
Consider providing feedback on the individual questions if you decide to post your results here.
Thank you for that – i was not aware of this kind of optimization in 11gR2 ( maybe 11.1 – can’t test it at the moment)
– if an indexed column is not nullable, the index range/unique scan can be used for nvl(column,value), as optimizer probably understand
now, the null can’t occur anyway.
Best regards
Maxim
Hi Maxim,
I am thinking that optimization must be specific to 11g R2, although I am not sure that I recall testing it on 11g R2. A brief test case table:
CREATE TABLE T1( C1 NUMBER, C2 VARCHAR2(200), PRIMARY KEY(C1)); INSERT INTO T1 SELECT ROWNUM C1, LPAD('A',200,'A') C2 FROM DUAL CONNECT BY LEVEL<=1000000; COMMIT; EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)Test case script:
This is the output that I receive on Oracle Database 10.2.0.2 (very similar results were seen on 11.1.0.7):
SQL_ID gywbrs9kx94v7, child number 0 ------------------------------------- SELECT C1, SUBSTR(C2,1,10) C2 FROM T1 WHERE C1=10000 Plan hash value: 1462557378 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 205 | 3 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | SYS_C0020654 | 1 | | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C1"=10000) ---- SQL_ID b6g33508zt1nz, child number 0 ------------------------------------- SELECT C1, SUBSTR(C2,1,10) C2 FROM T1 WHERE NVL(C1,0)=10000 Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1507 (100)| | |* 1 | TABLE ACCESS FULL| T1 | 1 | 205 | 1507 (5)| 00:00:08 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(NVL("C1",0)=10000) ---- SQL_ID 43fdysjbtscam, child number 0 ------------------------------------- SELECT /*+ INDEX(T1) */ C1, SUBSTR(C2,1,10) C2 FROM T1 WHERE NVL(C1,0)=10000 Plan hash value: 4057798311 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1919 (100)| | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 205 | 1919 (3)| 00:00:10 |* 2 | INDEX FULL SCAN | SYS_C0020654 | 1 | | 1918 (3)| 00:00:10 ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(NVL("C1",0)=10000)The unhinted execution plan from 11.1.0.7:
SQL_ID b6g33508zt1nz, child number 0 ------------------------------------- SELECT C1, SUBSTR(C2,1,10) C2 FROM T1 WHERE NVL(C1,0)=10000 Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3543 (100)| | |* 1 | TABLE ACCESS FULL| T1 | 1 | 205 | 3543 (2)| 00:00:15 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(NVL("C1",0)=10000)The index hinted execution plan (note that the calculated cost is lower):
SQL_ID 43fdysjbtscam, child number 0 ------------------------------------- SELECT /*+ INDEX(T1) */ C1, SUBSTR(C2,1,10) C2 FROM T1 WHERE NVL(C1,0)=10000 Plan hash value: 3968247418 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1909 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 205 | 1909 (2)| 00:00:08 | |* 2 | INDEX FULL SCAN | SYS_C0017730 | 1 | | 1908 (2)| 00:00:08 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(NVL("C1",0)=10000)I will have to test 11.2.0.1/11.2.0.2 later to see if I am able to reproduce the behavior – thanks for the tip.