Order by sort and query output _oracle in the in-condition in Oracle Database

Order by unstable sort
Ask a question: is Oracle a stable sort algorithm when ordering by? found that after sorting with a type, do a paging query, the first page of data and the second page of the data is repeated suspected to be order by, two orders are inconsistent

See the business description of the problem can get the conclusion order by ordering instability, and the first impression is that type is definitely not unique, and there is no index bar.

Here first popular science under the stability of the order, for the simplest example, 1,2,3,1,4,5 sort of the result is 1,1,2,3,4,5, this time to observe this 1, if the first 1 or the 1 before the order, then the algorithm is stable. That is, the equality number does not exchange after sorting.

Remember several sorting algorithms in the previous data structure:

Select sort complexity to n*n, unstable sort,

Fast sort complexity for n*n, unstable sort,

Hill sort complexity is nlogn, unstable sort,

Heap sort complexity is nlogn, unstable sort,

Bubble sort complexity is n*n, stable sort,

Insert sort complexity is n*n, stable sort,

Merge sort complexity is Nlogn, stable sort

Cardinality sorting is related to the complexity and number of digits, and is a stable sort.

All right, back to the point, native test, insert a few test data, table structure on two fields, ID and name, no index

SELECT rownum,zz_test.* from Zz_test;

1  2  Test
2  2  test
3  3  Test
4  4  Test
5  1  test

As you can see, the default error is sorted by rownum.
And then sort by name,

SELECT rownum,zz_test.* from Zz_test Order by Zz_test. " Name

1  2  Test
2  2  Test
5  1  test
4  4  test
3  3  test

As you can see, the order of arrangement is not sorted according to RowNum.
Here to insert a knowledge, how to view the implementation plan in Oracle, I knocked for half a day explain found no use ...

That's what it looks like, and the message is much more detailed than MySQL. ：

SELECT * FROM table (Dbms_xplan.display ());

-------------------------------------------------------------------------------
| Id | Operation      | Name  | Rows | Bytes | Cost (%CPU) | Time   |
-------------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |     |   1 |   8 |  (7) | 00:00:01 |
|  1 | SORT ORDER by   |     | |   1 |   8 |  (7) | 00:00:01 |
|  2 |  COUNT
|  | | 3 |  TABLE ACCESS full| Zz_test |   1 |   8 |  (0) | 00:00:01
| -------------------------------------------------------------------------------

OK, so does sorting and indexing have anything to do with it?
Let's try this with an index on the type, and here I am emptying the 5 data

SELECT rownum,zz_test.* from Zz_test Order by Zz_test. " Name

1  3  Test
2  4  test
5  2  Test
4  1  test
3  5  Test

Seems not to force ah old wet.
OK, delete the index of type, index the ID, empty the table and insert 5 data

SELECT rownum,zz_test.* from Zz_test Order by Zz_test. " Name

1  3  Test
2  4  test
5  2  Test
4  1  test
3  5  Test

All right. The original with the index is not to force ah ...

But it's not right ... It always feels wrong. That's right... I've been using rownum instead of ROWID. I must have written a lot of pages recently, pit Dad.

Here's a simple distinction between rownum and rowID, RowNum is a pseudo series that returns a result set that marks the order in which the results are returned, and ROWID is a physical value used to mark the storage location. This value is unique and fixed.

rowID and RowNum are virtual columns, but their meanings are completely different. rowID is the physical address that locates the physical storage location of the specific data in Oracle, while RowNum is the order of the output results of SQL. In layman's terms: ROWID is relatively constant, and rownum will change, especially when using order by.
So let's check the ROWID again, and the table has no index.

Select rowID as rono,rownum,zz_test.* from Zz_test order by Zz_test. " Name

AAA7JJAB9AAAD+RAAA 1  3  test
Aaa7jjab9aaad+raab 2  4  test
Aaa7jjab9aaad+raag 5  2  Test
Aaa7jjab9aaad+raad 4  1  test
AAA7JJAB9AAAD+RAAC 3  5  Test

Feels like Rowno and rowID.

Empty the table, create an index on name, and insert 5 data

AAA7JJAB9AAAD+RAAA 1  3  test
Aaa7jjab9aaad+raab 2  4  test
Aaa7jjab9aaad+raag 5  2  Test
Aaa7jjab9aaad+raad 4  1  test
AAA7JJAB9AAAD+RAAC 3  5  Test

Therefore, it is not the rowid problem, the oralce of the order is not stable.
There's a trick here, because the output order of the rownum is not the result of the ordering, so how can you output the rownum of the sort order? You can use nested queries, which is a good way to work with pagination.

Select RowNum, t.* from (select rowID rono,zz_test.* to Zz_test Order by Zz_test. " Name ") t

Here to insert a little knowledge, how to look at the table under Oracle

Select  *  from  user_tables

Can query out all the user tables

Select  table_name  from  user_tables;

The results of the query are output sequentially according to the in condition order

Business needs, identify the IDs that match the search criteria through Lucene, and then find out the details of these IDs in the details

SELECT id,question,questioncomment from "askdba_question" where ID in (63,62,65,61,64);

Where the ID is sorted according to the weight of the search, the SQL is not a problem, but the results of this SQL check out the wrong sort.

61 Test Problem 101 Test problem
62 test Problem 102 Test problem 102
63 test Problem 103 test question-64 test problem
104
Test Question 65 Test question-Question # 106 

This is generally by default sorted by primary key, not by the order of the conditions in

A case on the web is a solution sorted in order, using SQL Server charindex. But only SQL Server

Select Id,title from info  
where ID the (' 3,1,2,5,4 ')  
ORDER by charindex (', ' +convert (varchar,id) + ', ', ', ' 3,1,2,5,4, ') 

The CHARINDEX function returns the starting position of a character or string in another string. The CHARINDEX function calls the following methods:

Expression1 is the character to look for in expression2, start_location is the CHARINDEX function that begins to find expression2 in expression1. The CHARINDEX function returns an integer that returns an integer that is the position of the string to find in the string being searched. If CHARINDEX does not find the string to find, then the function integer "0"
Here are a few tricks that can be used to make fuzzy matches using charindex

Select Name,pass from Dps_user where
charindex (' John ', Dps_user.name) > 0

But how does Oracle achieve the same effect? You can use the Decode function

SELECT id,question,questioncomment from "askdba_question" where ID into (63,62,65,61,64) Order BY "DECODE" (ID, 63,1,62,2,65 , 3,61,64);

63 Test Question 103 test problem
62 test Problem 102 Test problem 102
65 Test Problem 106 Test problem
test Problem 61 Test Question 101 test question
64

The results are in accordance with the conditions