IN Oracle databases, order by sorting and query are output IN the ORDER of IN conditions. oracleorder

Source: Internet
Author: User

IN Oracle databases, order by sorting and query are output IN the ORDER of IN conditions. oracleorder

Order by non-stable sorting
Q: Is oracle a stable Sorting Algorithm for order by sorting? After a type is found to be used for sorting, paging query is performed. When the data on the first page and the data on the second page are repeatedly suspected to be order by, the order of the data on the second page is inconsistent.

The conclusion obtained from the problem of service description is that order by sorting is unstable. The first impression is that type is definitely not unique and there is no index.

Here, let's take a look at the stability of sorting under science. The simplest example is that the sorting result of, is 1, 2, 3, 4, and 5, if the first 1 is the first 1, the algorithm is stable. That is to say, equal numbers are not exchanged after sorting.

Remember several sort algorithms in the previous data structure:

The sorting complexity is n * n, Which is unstable,

The sorting complexity is n * n, Which is unstable,

Hill's sorting complexity is nlogn, Which is unstable,

The heap sorting complexity is nlogn, Which is unstable,

The Bubble Sorting complexity is n * n, which is stable,

The insertion sorting complexity is n * n, which is stable,

The Merge Sorting complexity is nlogn, which is stable.

The complexity of base sorting is related to the number of digits and is a stable sorting.

Now, let's go back to the topic. Test the machine and insert several pieces of test data. The table structure has two fields, id and name, with no index.

SELECT ROWNUM,ZZ_TEST.* FROM ZZ_TEST;
1  2  test2  2  test3  3  test4  4  test5  1  test

We can see that by default, the difference is sorted by rownum.
Sort by name,

SELECT ROWNUM,ZZ_TEST.* FROM ZZ_TEST ORDER BY ZZ_TEST."name"
1  2  test2  2  test5  1  test4  4  test3  3  test

We can see that the order is not sorted by rownum.
Here, I will insert a piece of knowledge to explain how to view the execution plan in oracle. I have knocked on the explain for half a day and found that it is useless...

It turns out that 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 |  16  (7)| 00:00:01 ||  1 | SORT ORDER BY   |     |   1 |   8 |  16  (7)| 00:00:01 ||  2 |  COUNT       |     |    |    |      |     ||  3 |  TABLE ACCESS FULL| ZZ_TEST |   1 |   8 |  15  (0)| 00:00:01 |-------------------------------------------------------------------------------
Okay. Is there any relationship between sorting and indexing?
First, add an index on the type. Here, I cleared and inserted 5 data records again.
SELECT ROWNUM,ZZ_TEST.* FROM ZZ_TEST ORDER BY ZZ_TEST."name"
1  3  test2  4  test5  2  test4  1  test3  5  test

Looks bad.
Okay. Delete the type index, add the index to the id, clear the table, and insert 5 data records.

SELECT ROWNUM,ZZ_TEST.* FROM ZZ_TEST ORDER BY ZZ_TEST."name"
1  3  test2  4  test5  2  test4  1  test3  5  test

Okay. It turns out that indexes are not powerful...

But not right... Something is wrong. That's right... I used rownum instead of rowID in TMD. I must have written too many pages recently.

The difference between rownum and rowid is simply distinguished here. rownum is a pseudo series of returned result sets, used to mark the order of returned results, and rowid is a physical value used to mark the storage location. This value is unique and fixed.

Rowid and rownum are both virtual columns, but they have different meanings. Rowid is a physical address used to locate the physical storage location of specific data in oracle, while rownum is the sorting of SQL output results. In general, rowid remains unchanged, and rownum changes, especially when order by is used.
Then let's try again the rowid. At this time, the table has no index.

SELECT rowid as rono,ROWNUM,ZZ_TEST.* FROM ZZ_TEST ORDER BY ZZ_TEST."name"
AAA7JjAB9AAAD+RAAA 1  3  testAAA7JjAB9AAAD+RAAB 2  4  testAAA7JjAB9AAAD+RAAG 5  2  testAAA7JjAB9AAAD+RAAD 4  1  testAAA7JjAB9AAAD+RAAC 3  5  test

It seems like rowno and rowid.

Clear the table, create an index on the name, and insert 5 data records.

AAA7JjAB9AAAD+RAAA 1  3  testAAA7JjAB9AAAD+RAAB 2  4  testAAA7JjAB9AAAD+RAAG 5  2  testAAA7JjAB9AAAD+RAAD 4  1  testAAA7JjAB9AAAD+RAAC 3  5  test

Therefore, oralce sorting is not stable, nor is rowid.
Here is a small trick, because the output sequence of rownum is not the result of sorting, how can we output the rownum of sorting order? You can use nested queries. This is the same as paging syntax.

select ROWNUM ,t.* from (SELECT rowid rono,ZZ_TEST.* FROM ZZ_TEST ORDER BY ZZ_TEST."name") t

Here, we will insert a little knowledge about how to view the table in oracle.

select  *  from  user_tables

All user tables can be queried.

select  table_name  from  user_tables;

Query results are output in sequence according to the in condition.

Business Needs: Use lucene to find the id that meets the search criteria, and then find the details of these IDs in the Details table

SELECT id,QUESTION,QUESTIONCOMMENT FROM "ASKDBA_QUESTION" where ID IN (63,62,65,61,64);

The IDS are sorted based on the search weights. The SQL statement is correct, but the result obtained by this SQL statement is incorrect.

61 test question 101 test question 10162 test question 102 test question 10263 test question 103 test question 10364 test question 104 test question 10465 test question 106 test question 106

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


There is a case on the Internet that uses the charindex of SQL server to sort data in the in order. But only for sqlserver

select id,title from info  where id in ('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 is called as follows:

CHARINDEX ( expression1 , expression2 [ , start_location ] ) 

Expression1 is the character to be searched in expression2. start_location is the position where the CHARINDEX function starts to find expression1 in expression2. The CHARINDEX function returns an integer that is the position of the string to be searched in the string to be searched. If CHARINDEX does not find the string to be searched, the function integer is "0"
Here are some tips. You can use charindex for Fuzzy Matching.

Select name, pass from dps_user wherecharindex ('zhang san', dps_user.name)> 0

But how does one achieve the same effect in oracle? You can use the decode function.

SELECT id,QUESTION,QUESTIONCOMMENT FROM "ASKDBA_QUESTION" where ID IN (63,62,65,61,64) ORDER BY "DECODE"(id, 63,1,62,2,65,3,61,64);
63 test question 103 test question 10362 test question 102 test question 10265 test question 106 test question 10661 test question 101 test question 10164 test question 104 test question 104

The result is qualified.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.