Re: Interview Index problem

The biggest dependency is the interviewer. I see what Sachin says:

SQL> create index deptno on emp (deptno);

Index created.

SQL> create index sal on emp (sal);

Index created.

SQL> select empno,ename,sal,deptno from emp where deptno=10 and sal>1000;

     EMPNO ENAME SAL DEPTNO ---------- ---------- ---------- ----------

      7782 CLARK            2450         10
      7839 KING             5000         10
      7934 MILLER           1300         10

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'    2 1 INDEX (RANGE SCAN) OF 'DEPTNO' (NON-UNIQUE) Statistics

          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        773  bytes sent via SQL*Net to client
        651  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

but...

SQL> exec dbms_stats.gather_schema_stats(ownname=> 'SCOTT');

PL/SQL procedure successfully completed.

SQL> select empno,ename,sal,deptno from emp where deptno=10 and sal>1000;

     EMPNO ENAME SAL DEPTNO ---------- ---------- ---------- ----------

      7782 CLARK            2450         10
      7839 KING             5000         10
      7934 MILLER           1300         10

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=4 Bytes=68)    1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=4 Bytes=68)

Statistics

          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        773  bytes sent via SQL*Net to client
        651  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

So you are _all_ wrong, it's NO INDEX! (Sorry Jonathan :-)

It is clearly stated in the manuals that the equivalency will be considered lesser cost than than the greater-than - if there are no statistics
(http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#sthref1256).  It kinda doesn't say what happens if there are statistics.

Of course, Jonathan is correct, explicitly stating the dependencies to properly answer such a question. So is it a bad question? It is if the interviewer _only_ scores it correct given Sachin's answer. Sachin's answer is clearly incorrect in stating unequivocally that a particular index will be used, as well as being wrong with statistics. Jonathan's dependencies are necessary for the former. But they aren't part of the problem domain as asked. (For that matter, is scott these days? utlsampl.sql _is_ in XE... Are we to assume proper statistics? I think not, as the sample load from the days of RBO doesn't make them.)

If the answer is scored on a scale that adds points based on how close to Jonathan's answer the interviewee comes, that might be ok. I have my doubts it would be used that way - I can even visualize situations where the interviewer shuts down and thinks the interviewee is BS'ing, as such things have happened to me.

So Aman: Where _did_ this question come from?

jg

-- 
@home.com is bogus.
She blinded me... with science!