Shulou(Shulou.com)05/31 Report--

This article mainly explains the "Oracle scheduling problem example analysis", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's train of thought slowly in depth, together to study and learn "Oracle sorting problem example analysis"!

In order to describe the problem, we must first reproduce the problem. For better illustration, in the following example, the sort column includes only two different values.

SQL > CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A

The table has been created.

SQL > SELECT TEMPORARY, COUNT (*) FROM T GROUP BY ROLLUP (TEMPORARY)

T COUNT (*)

--

N 28046

Y 29

28075

The test table has been constructed above, and here are a few simple paging queries to locate the problem:

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT ID FROM T ORDER BY TEMPORARY

8) A

9 WHERE ROWNUM

< 11 10 ) 11 WHERE RN >

= 1

RN ID

--

1 1

2 2

3 3

4 4

5 5

6 6

7 7

8 8

9 9

10 10

Ten rows have been selected.

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT ID FROM T ORDER BY TEMPORARY

8) A

9 WHERE ROWNUM

< 111 10 ) 11 WHERE RN >

= 101

RN ID

--

101 101

102 102

103 103

104 104

105 105

106 106

107 107

108 108

109 109

110 110

Ten rows have been selected.

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT ID FROM T ORDER BY TEMPORARY

8) A

9 WHERE ROWNUM

< 1111 10 ) 11 WHERE RN >

= 1101

RN ID

--

1101 1101

1102 1102

1103 1103

1104 1104

1105 1105

1106 1106

1107 1107

1108 1108

1109 1109

1110 1110

Ten rows have been selected.

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT ID FROM T ORDER BY TEMPORARY

8) A

9 WHERE ROWNUM

< 11111 10 ) 11 WHERE RN >

= 11101

RN ID

--

11101 6093

11102 6094

11103 6095

11104 6096

11105 6097

11106 6098

11107 6099

11108 6100

11109 6101

11110 6102

Ten rows have been selected.

As a result, the above four queries have found the problem. In the previous queries, the value of ROWNUM is the same as that of ID, and only the last query does not satisfy this result.

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT ID FROM T ORDER BY TEMPORARY

8) A

9 WHERE ROWNUM

< 6091 10 ) 11 WHERE RN >

= 6080

RN ID

--

6080 6080

6081 6081

6082 6082

6083 6083

6084 6084

6085 6085

6086 6086

6087 6087

6088 6088

6089 6089

6090 6090

11 lines have been selected.

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT ID FROM T ORDER BY TEMPORARY

8) A

9 WHERE ROWNUM

< 6101 10 ) 11 WHERE RN >

= 6090

RN ID

--

6090 6090

6091 6091

6092 6092

6093 6093

6094 6094

6095 6095

6096 6096

6097 6097

6098 6098

6099 6099

6100 6100

11 lines have been selected.

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT ID FROM T ORDER BY TEMPORARY

8) A

9 WHERE ROWNUM

< 6111 10 ) 11 WHERE RN >

= 6100

RN ID

--

6100 6092

6101 6093

6102 6094

6103 6095

6104 6096

6105 6097

6106 6098

6107 6099

6108 6100

6109 6101

6110 6102

11 lines have been selected.

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT ID FROM T ORDER BY TEMPORARY

8) A

9 WHERE ROWNUM

< 6121 10 ) 11 WHERE RN >

= 6110

RN ID

--

6110 6092

6111 6093

6112 6094

6113 6095

6114 6096

6115 6097

6116 6098

6117 6099

6118 6100

6119 6101

6120 6102

11 lines have been selected.

From these four queries, it is not difficult to see that the problem lies in ID=6102. When the paging query is less than 6102, ROWNUM and ID are always equal in the query results. But once the paging exceeds the value of 6102, the result of the last page is fixed-from 6092 to 6102.

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT ID FROM T ORDER BY TEMPORARY

8) A

9 WHERE ROWNUM

< 28041 10 ) 11 WHERE RN >

= 28030

RN ID

--

28030 6092

28031 6093

28032 6094

28033 6095

28034 6096

28035 6097

28036 6098

28037 6099

28038 6100

28039 6101

28040 6102

11 lines have been selected.

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT ID FROM T ORDER BY TEMPORARY

8) A

9 WHERE ROWNUM

< 28047 10 ) 11 WHERE RN >

= 28036

RN ID

--

28036 6093

28037 6094

28038 6095

28039 6096

28040 6097

28041 6098

28042 6099

28043 6100

28044 6101

28045 6102

28046 6103

11 lines have been selected.

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT ID FROM T ORDER BY TEMPORARY

8) A

9 WHERE ROWNUM

< 28051 10 ) 11 WHERE RN >

= 28040

RN ID

--

28040 6097

28041 6098

28042 6099

28043 6100

28044 6101

28045 6102

28046 6103

28047 6104

28048 8728

28049 9075

28050 8744

11 lines have been selected.

According to the statistics for the TEMPORARY column just now, there are 28046 records with N. Through the query, we can find that as long as the page is not turned to the last page, the query result is always 6092 to 6102.

If you control the page turning to all records with N, you will find that the last record with N is 6103. If you continue to scroll down, the record with Y begins to appear.

Record 6103 is the last record with N, and record 6104 should be the first record with Y:

SQL > SELECT ID FROM T WHERE TEMPORARY ='Y' AND ROWNUM = 1

ID

-

6104

As expected, 6104 is the first record of Y, where Oracle visits found the first Y, and according to the order of visits, the last result of N is 6103.

In the paging before the 6104 record, the results were normal, because Oracle did not encounter a record of Y when sorting until the emergence of record 6104. Since 6104 is regarded as the first Y, 6103 is regarded as the last N.

Then it can be inferred that when the query page turns over 6103, Oracle takes record 6103 and some records before 6103 as the maximum values in N, and when the requirement exceeds 6103, Oracle will continue to look down for records with TEMPORARY N. These records are sorted before the "largest" records, such as record 6103. Because it is ORDER BY STOPKEY, the table scan stops when the query results in more records than required. At this point, the result will be returned to the user. Because of the paging mechanism, the last few records of the query are returned each time. Due to STOPKEY, the result of Oracle sorting exceeds the total number of records returned, so the largest 6103 of N is the excess and will not be returned, while the largest record is 6092 to 6102.

This is why when paging exceeds a certain range, the same data is returned for this purpose.

However, the above content is completely inferred from the ranking results of Oracle, and there is no theoretical basis, and this inference is only a general, Oracle specific algorithm is estimated to be much more complicated.

To verify the above description:

SQL > SELECT STATUS, COUNT (*) FROM T GROUP BY ROLLUP (STATUS)

STATUS COUNT (*)

--

VALID 28075

28075

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT ID FROM T ORDER BY STATUS

8) A

9 WHERE ROWNUM

< 101 10 ) 11 WHERE RN >

= 91

RN ID

--

91 91

92 92

93 93

94 94

95 95

96 96

97 97

98 98

99 99

100 100

Ten rows have been selected.

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT ID FROM T ORDER BY STATUS

8) A

9 WHERE ROWNUM

< 1001 10 ) 11 WHERE RN >

= 991

RN ID

--

991 991

992 992

993 993

994 994

995 995

996 996

997 997

998 998

999 999

1000 1000

Ten rows have been selected.

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT ID FROM T ORDER BY STATUS

8) A

9 WHERE ROWNUM

< 10001 10 ) 11 WHERE RN >

= 9991

RN ID

--

9991 9991

9992 9992

9993 9993

9994 9994

9995 9995

9996 9996

9997 9997

9998 9998

9999 9999

10000 10000

Ten rows have been selected.

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT ID FROM T ORDER BY STATUS

8) A

9 WHERE ROWNUM

< 28001 10 ) 11 WHERE RN >

= 27991

RN ID

--

27991 27991

27992 27992

27993 27993

27994 27994

27995 27995

27996 27996

27997 27997

27998 27998

27999 27999

28000 28000

Ten rows have been selected.

For the sorting of fields that contain only one value, there is no part of the page that has the same result.

Let's update the STATUS column to create a different value to see if the query works as expected:

SQL > UPDATE T SET STATUS = 'VBLID' WHERE ID IN (1000, 1500, 5000)

3 rows updated.

SQL > COMMIT

The submission is complete.

The following query is made:

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT ID FROM T ORDER BY STATUS

8) A

9 WHERE ROWNUM

< 111 10 ) 11 WHERE RN >

= 100

RN ID

--

100 100

101 101

102 102

103 103

104 104

105 105

106 106

107 107

108 108

109 109

110 110

11 lines have been selected.

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT ID FROM T ORDER BY STATUS

8) A

9 WHERE ROWNUM

< 1111 10 ) 11 WHERE RN >

= 1100

RN ID

--

1100 988

1101 989

1102 990

1103 991

1104 992

1105 993

1106 994

1107 995

1108 996

1109 997

1110 998

11 lines have been selected.

The result of the query is exactly as expected. However, the above conjecture is based on two different values, and the algorithm is much more complex when there are multiple different key values in the column.

Thank you for your reading, the above is the content of "example Analysis of Oracle scheduling problem". After the study of this article, I believe you have a deeper understanding of the example analysis of Oracle scheduling problem, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.