Hello DBAs,

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

in the query given above I have 2 b-tree indexes on deptno and sal and
using CBO, pls tell me oracle will use which index and
why...................

"aman.oracle.dba" <aman.oracle.dba@gmail.com> wrote in message
news:1148934219.754175.228340@j33g2000cwa.googlegr oups.com...
> Hello DBAs,
>
> "select empno,ename,sal,deptno from emp where deptno=10 and sal>1000"
>
> in the query given above I have 2 b-tree indexes on deptno and sal and
> using CBO, pls tell me oracle will use which index and
> why...................
>


The answer depends on the data volume and scatter,
collection of statistics, and version of Oracle

If 1,000 is a very high salary, paid to just a few people,
then the optimizer might use just the index on sal. If there
are only a few people in department 10 in a very large
company and there is a histogram on deptno then the
optimizer might use the index on deptno. In circumstances
where neither index seems to be a good individual choice,
9i might do a b-tree/bitmap conversion on the rowid ranges
from both indexes, do a bitmap AND, then convert back
to rowids to visit the table.


--
Regards

Jonathan Lewis
http://www.oracle.com/technology/com...ce1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

On Mon, 29 May 2006 21:50:04 +0100, Jonathan Lewis wrote:

> If 1,000 is a very high salary, paid to just a few people,

Johnatan, USD is sinking like a rock. If company is paying $1000,
it's Wal-Mart, paying the illegal immigrants. So, it's very many
employees. Depending on the statistics, CBO can select either index.
That is why query should have been written like this:

"select empno,ename,sal,deptno from emp where deptno=10 and sal+0>1000"

Now, there is no more doubt.

--
http://www.mgogala.com

"Mladen Gogala" <gogala@sbcglobal.net> wrote in message
newsan.2006.05.30.01.33.20.191837@sbcglobal.net. ..
> On Mon, 29 May 2006 21:50:04 +0100, Jonathan Lewis wrote:
>
>> If 1,000 is a very high salary, paid to just a few people,
>
> Johnatan, USD is sinking like a rock. If company is paying $1000,
> it's Wal-Mart, paying the illegal immigrants. So, it's very many
> employees. Depending on the statistics, CBO can select either index.
> That is why query should have been written like this:
>
> "select empno,ename,sal,deptno from emp where deptno=10 and sal+0>1000"
>
> Now, there is no more doubt.
>
> --
> http://www.mgogala.com
>


Never make assumptions about other people's data.

How do you know which currency, which country, and
whether the rate is per hour, per day, per week or per
year ?


--
Regards

Jonathan Lewis
http://www.oracle.com/technology/com...ce1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

>> Never make assumptions about other people's data <<

Half the time it isn't even safe to make assumptions about your own
data. Customers and developers conspire to do all kind of stange
things. 8-D

-- Mark D Powell --

aman.oracle.dba wrote:
> Hello DBAs,
>
> "select empno,ename,sal,deptno from emp where deptno=10 and sal>1000"
>
> in the query given above I have 2 b-tree indexes on deptno and sal and
> using CBO, pls tell me oracle will use which index and
> why..................


Index over dept will be used .. CBO feels that selecting based on
dept=10 will be of lesser cost as there are only 3 employees of this
dept (assuming the std emp table in scott schema)
Than the cost of selecting sail > 1000 as there are more employees with
salary > 1000, so therefore .. even if you reverse the conditions (i.e.
sal> 10000 and dept=10), the index of dept will be used.

I hope i'm clear.

Sachin

oraclearora@googlemail.com wrote:
> aman.oracle.dba wrote:
>> Hello DBAs,
>>
>> "select empno,ename,sal,deptno from emp where deptno=10 and sal>1000"
>>
>> in the query given above I have 2 b-tree indexes on deptno and sal and
>> using CBO, pls tell me oracle will use which index and
>> why..................
>
>
> Index over dept will be used .. CBO feels that selecting based on
> dept=10 will be of lesser cost as there are only 3 employees of this
> dept (assuming the std emp table in scott schema)
> Than the cost of selecting sail > 1000 as there are more employees with
> salary > 1000, so therefore .. even if you reverse the conditions (i.e.
> sal> 10000 and dept=10), the index of dept will be used.
>
> I hope i'm clear.
>
> Sachin

You are both clear and incorrect. Reread Jonathan's response. Much as
Connor was able to create any hit ratio desired one can easily modify
the data and optimizer settings to either use or not use the indexes.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

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...tm#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!

"joel garry" <joel-garry@home.com> wrote in message
news:1149026936.249459.69310@38g2000cwa.googlegrou ps.com...
>
> So you are _all_ wrong, it's NO INDEX! (Sorry Jonathan :-)
>

No need to apologize - after all, I said "might use",
not "would use".

>
> 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.
>

Sad, but true - especially if the interviewer is depending on
a script to do their interviewing.

On the other hand, the default Scott install doesn't have an
index on SAL - so perhaps this interviewer (or interview-script
writer) is aware of the impact of the relationship between
data content, data distribution, and query predicates.




--
Regards

Jonathan Lewis
http://www.oracle.com/technology/com...ce1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

what I understand, if your have short table then index is not in use
(full table scan) but if you have large table then that index will be
used which has less no. of records like if in table if we have 1000
entries where deptno=10 and 1500 entries where sal>1000 then deptno
index will be in use.
Is it correct.............................