Re: Descending index - Order by Clause - sorting? optimizer - Bug?

A couple of points:

1. The code you've supplied doesn't populate the table that you declared
2. Still no sign of execution plans to show that you've interpreted what you're seeing correctly
3. The comment in (b) might not matter except in your initial posting you had this example:

>>  I was wondering whether function based index had to do with any of it. created the index as follows:
>>  create index mark1_roll_idx on randomload(mark1-1, roll);
>>  select roll from randomload where mark1=11200 -1  order by roll;
>>  here in this case, sorting is not performed, clearly nothing wrong with the function based index

There is a note on the basic issue 9and general principle) relating to the SORT ORDER BY that could be eliminated here: https://jonathanlewis.wordpress.com/2019/06/26/glitches/

You'll note from the article that the redundant SORT ORDER BY was only eliminated for the simplest class of index some time in 11g - you've said that your simple FBI example behaves in 19c, and my blog example show a more complex case where the workaround is still needed even in 19c. It is a sad fact that fixes to optimizer limitations tend to take some time to ripple through the code before reaching the more complex cases.

Regards
Jonathan Lewis

I should have included the details before...

database release is 19c oct 19 PSU.. I used the following code to generate a random data as this is as close as it gets to the actual production and I can influence so many things such as delay between subsequent inserts, number of sessions inserting etc etc as inserting using insert statement... we cannot truly see how ASSM truly works.. we can use the same code to generate any row length..

To generate randomvalues:

public class OraRandom {

static int randomSkewInt(int a) {

return Math.abs(Math.round((new Random().nextInt()/(new Random().nextInt()/4))%a)); //increasing 7 reduces the skew. skewed data

}

static int randomUniformInt(int a) {

return Math.abs(new Random().nextInt()%a); //Uniform distribution - almost

}

static String randomString(int a) {

    int leftLimit = 97;

    int rightLimit = 122;

    Random random = new Random();

    StringBuilder buffer = new StringBuilder(a);

    for (int i = 0; i < a; i++) {

        int randomLimitedInt = leftLimit + (int)

          (random.nextFloat() * (rightLimit - leftLimit + 1));

        buffer.append((char) randomLimitedInt);

    }

    return buffer.toString();

}

static int randomInt(int a) {

return Math.abs(new Random().nextInt()%a) + a ;

}

}

To Load the table:

void loadTable() throws InterruptedException {

ExecutorService asd = Executors.newFixedThreadPool(30);

int i = 0;

while (i < 5) { //Increasing this increases the number of threads or connections.

asd.submit(new InsertLoad());

i++;

}

i = 0 ;

System.out.println("Loading Data... Sleepin for 10 seconds");

asd.shutdown();

while(!asd.isShutdown()) {

Thread.currentThread().sleep(1000);

}

asd.shutdownNow();

asd = Executors.newFixedThreadPool(30);

}

Loading actual Data:

class InsertLoad implements Runnable{

public void run() {

try {

System.out.println("Staring Insert Thread -->" + Thread.currentThread().getName());

Connection oraCon = DBConnection.getOraConn();

PreparedStatement pstmt = oraCon.prepareStatement("insert into students (student_id, dept_id,mark1, mark2, mark3) values (?,?,?,?,?)");

int i = 0;

while (i < 30099900) { //each thread inserts 30 million rows

pstmt.setInt(1 , oraSequence.nextVal());

pstmt.setInt(2, OraRandom.randomUniformInt(100));

pstmt.setInt(3, OraRandom.randomUniformInt(100));

pstmt.setInt(4, OraRandom.randomUniformInt(100));

pstmt.setInt(5, OraRandom.randomUniformInt(300));

//pstmt.executeUpdate(); //Here i can switch from either inserting single statement at once or perform batch inserts.

pstmt.addBatch();

if (i%10000 == 0) {

pstmt.executeBatch();

System.out.println("loaded " + oraSequence.getval());

}

i++;

}

pstmt.close();

oraCon.close();

  DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss");

  LocalDateTime now = LocalDateTime.now();

  System.out.println(dtf.format(now));

}

catch(Exception E) {

E.printStackTrace();

}

}

}

This program generates data very fast...

no Jonathan... I am not playing with the boundary value... we can simulate the same behaviour with even larger row sizes.. I am seeing benefits of this sort only in handful cases where in the ORDER BY clause contained the indexed column which IN clause refers to and provided that values are extrememly close to each other (this mostly very rarely happens on actual production)

Thanks,
Vishnu

On Mon, Jan 6, 2020 at 3:04 PM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>> wrote: Vishnu,

As a general guideline remember to include the following

the version of Oracle you're using
the code that generates the data
the execution plan you've pulled from memory (dbms_xplan.display_cursor) after running the query.

You also need to think about whether you're playing around with a boundary condition: a notable defect in the examples you have described is that the rows are very short - which immediately raises the question of whether the index is bigger or smaller than the table. For all we know the your complaint about sorting or not sorting may simply mean that you didn't notice that Oracle switch from an index scan to a tablescan with sort, or index full scan to index fast full scan with sort.

Regards
Jonathan Lewis

Came across an interesting issue today.
the situation is as follows:
create table randomload(roll number, name varchar2(20), mark1 number); roll is the primary key.
create index mark1_desc_roll_idx on randomload(mark1 desc, roll); populate data uniformly for mark1, gather statistics

the following statement regardless of what it uses sorting. (sort order by) select roll from randomload where mark1=11999 order by roll ;

even if we create the index specifically which will not make any sense, but just in case. create index mark1_desc_roll_asc_idx on randomload(mark1 desc, roll asc); select roll from randomload where mark1=11999 order by roll;

we know that the roll column in the index is already sorted in the ascending order, but still why does oracle perform sorting?

I was wondering whether function based index had to do with any of it. created the index as follows: create index mark1_roll_idx on randomload(mark1-1, roll); gathered statistics, then running the query. select roll from randomload where mark1=11200 -1 order by roll; here in this case, sorting is not performed, clearly nothing wrong with the function based index and how the data is organized in the indexes, but looks like an issue with the optimizer generating the plan?

all works perfectly fine when the trailing column of the index uses desc instead of leading column...

Thanks,
Vishnu

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l