Prepared Quizzes, How Would You Answer that Question? 3

13 01 2011

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.


Actions

Information

2 responses

13 01 2011
Maxim

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

13 01 2011
Charles Hooper

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:

SELECT
  C1,
  SUBSTR(C2,1,10) C2
FROM
  T1
WHERE
  C1=10000;
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
 
SELECT
  C1,
  SUBSTR(C2,1,10) C2
FROM
  T1
WHERE
  NVL(C1,0)=10000;
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
 
SELECT /*+ INDEX(T1) */
  C1,
  SUBSTR(C2,1,10) C2
FROM
  T1
WHERE
  NVL(C1,0)=10000;
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 143 other followers

%d bloggers like this: