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

Thanks Jonathan for the clarification.. these days i need to find an alternate solution to iCloud drive or do something about this...... it really has trouble syncing eclipse workspace between different macs when eclipse is open... I posted the old code and forgot to paste the explain plan... I apologize for creating confusion here...

Thanks,
Vishnu

On Mon, Jan 6, 2020 at 11:47 PM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
> A couple of points:
>
> a) The code you've supplied doesn't populate the table that you declared
> b) Still no sign of execution plans to show that you've interpreted what
> you're seeing correctly
> c) 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
>
> Since your predicate will be transformed to "mark1 = 11199" it should not
> be possible for Oracle to use the mark1_roll_idx index without visiting the
> table and then doing a sort; alternatively if the mark1_desc_roll_idx still
> exists then you should get the path you got for the original query using
> that index. I could assume that you had intended to type "mark1 - 1 =
> {constant}" - but you can appreciate that any misunderstanding could have
> been avoided if you'd supplied the dbms_xplan output.
>
>
>
> 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
>
>
>
> ________________________________________
> From: Vishnu Potukanuma <vishnupotukanuma_at_gmail.com>
> Sent: 06 January 2020 10:27
> To: Jonathan Lewis
> Cc: Oracle-L Freelists
> Subject: Re: Descending index - Order by Clause - sorting? optimizer - Bug?
>
> 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
>
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>
> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on
> behalf of Vishnu Potukanuma <vishnupotukanuma_at_gmail.com<mailto:
> vishnupotukanuma_at_gmail.com>>
> Sent: 06 January 2020 05:46
> To: Oracle-L Freelists
> Subject: Descending index - Order by Clause - sorting? optimizer - Bug?
>
> 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
>
>
>

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