My query (SELECT cola, colb from tableA ORDER BY cola) is slow so I created an index

CREATE indexa on schema.tableA (cola)

The index was created and my query is as slow as before. Do I miss something?

This plan works on DB2, MS SQL, MySQL. Why not on Oracle?

Thanks

>This plan works on DB2, MS SQL, MySQL. Why not on Oracle?
post EXPLAIN PLAN from all 4 DBs.

On MS SQL Server for example, my query takes 27 seconds

SELECT cola, colb from dbo.tableA ORDER BY cola

After creating index:

CREATE indexa on dba.tableA (cola)

The same query only takes 0.1 seconds.

Proves NOTHING!

>This plan works on DB2, MS SQL, MySQL. Why not on Oracle?
post EXPLAIN PLAN from all 4 DBs.

Most likely this is because you created a clustered index in MS SQL. Otherwise indexes don't generally help w/ order by speed unless its a covering index.

These 2 queries take 23 seconds on Oracle:

(1) select invline from mrcmpower.bisales order by invline desc
(2) select invline from mrcmpower.bisales where invline >0 order by invline desc

They should be the same because all 2 million rows in the table have invline > 0.

Then I added index
create index mrcmpower.index2 on mrcmpower.bisales (invline desc)

Now query (1) still takes 23 seconds and (2) only takes 0.01 second.

Why query (1) does not use the index?

Thanks

WHY MY INDEX IS NOT BEING USED
BMC Communities: Why Isn't Oracle Using My Index?!

Why isn't my index getting used?

http://www.orafaq.com/tuningguide/no...g%20index.html

More likely in the MS SQL Server case, the data is being read from cache on the first run, and the second query reads the same data (does a table scan) but reads from memory.

What is load on those table?

Try gathering the stats on the index that you have created. That might help you.

Create a concatenated index:

In this case, Oracle will not access the data segments and get all the info out of the index (you save 50% of I/O this way)