|
|
我的意图是获得客户的分页结果集。我正在使用Tom的这种算法：1 w5 d* E0 J2 K4 |! d! V
select * from (
- _$ P) M- E8 Z5 Y1 R select /*+ FIRST_ROWS(20) */ FIRST_NAME, ROW_NUMBER() over (order by FIRST_NAME) RN
7 Q5 }( N* N5 y1 n- q from CUSTOMER C, }( Z T) _, w% ]3 R
)2 y! q( I9 C7 U% u; W0 B; D9 {1 {
where RN between 1 and 20
7 l( g D; j, f( ^7 A" Dorder by RN;
& B( e6 x% I" b: U& B0 v我还在“客户”列中定义了一个索引。“ FIRST_NAME”：
; e0 ~' ~: q0 G z" gCREATE INDEX CUSTOMER_FIRST_NAME_TEST ON CUSTOMER (FIRST_NAME ASC);* a+ J7 {5 I' f. J2 r8 C0 m3 Q
该查询返回了预期的结果集，但是从说明计划中，我注意到未使用该索引：+ [* f: f' d' p! ^
--------------------------------------------------------------------------------------
- }$ }" P8 _8 @# z# \7 F| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |/ P% `! k9 @3 V; T+ c$ ~! a
--------------------------------------------------------------------------------------7 u. }5 Z) m( s# V- [0 [2 E
| 0 | SELECT STATEMENT | | 15467 | 679K| 157 (3)| 00:00:02 |
- X, K4 i3 j1 a3 f( j' W% h( c| 1 | SORT ORDER BY | | 15467 | 679K| 157 (3)| 00:00:02 |
* y6 |/ F5 W5 v* ? W; P|* 2 | VIEW | | 15467 | 679K| 155 (2)| 00:00:02 |* H/ z* a3 l5 V) l2 y1 Y
|* 3 | WINDOW SORT PUSHED RANK| | 15467 | 151K| 155 (2)| 00:00:02 |% L9 P+ @, ]4 b- `9 y" B; v- q( m
| 4 | TABLE ACCESS FULL | CUSTOMER | 15467 | 151K| 154 (1)| 00:00:02 |
2 M) R m) \" C* r--------------------------------------------------------------------------------------0 _' g: o+ h' I) ~) r
Predicate Information (identified by operation id):
9 l4 H5 I; w! ~% N, P. d$ b---------------------------------------------------
0 X3 O& E! D& P 2 - filter("RN">=1 AND "RN"我正在使用Oracle 11g。由于我只查询前20行（按索引列排序），因此我希望使用索引。; W- ?( R E2 x# b
为什么Oracle优化器会忽略索引？我认为分页算法有问题，但是我不知道是什么。& u, m) w0 o: B6 f
谢谢。
4 a, |& S7 Y' n% J9 F+ \6 d ; a" n: V7 q; s) r0 K; f
解决方案:! J( e1 d4 z& Z" \* y' N/ D
0 u% v; s5 v6 j& \# Y4 ~& z- I9 d* \7 D, m
+ S' a$ S4 I( f, b! W; K
您的FIRST_NAME列很有可能为空。& [: F/ m9 h" C7 ?# p3 N
SQL> create table customer (first_name varchar2(20), last_name varchar2(20));2 ?* J9 I r. X
Table created.( L- ]4 l/ Z* p# c2 O p. @
SQL> insert into customer select dbms_random.string('U', 20), dbms_random.string('U', 20) from dual connect by level create index c on customer(first_name);
. S, F& O5 y% p! G& T% Q9 `Index created.
7 u2 G7 a/ H( N1 p# W8 u. I$ I! s4 TSQL> explain plan for select * from (& e* m' p, D, X9 J) M8 P
2 select /*+ FIRST_ROWS(20) */ FIRST_NAME, ROW_NUMBER() over (order by FIRST_NAME) RN' i0 s1 g1 d& S1 i8 P
3 from CUSTOMER C
% r1 Q( r+ e1 V" I2 p. o$ b& u 4 )' Z% H/ U- x' a/ ^. S4 j+ Q" w
5 where RN between 1 and 20
2 L0 @% G* }: G; l 6 order by RN;/ w/ K5 y7 m$ B
Explained.& O; F0 _% q& ~7 h
SQL> @explain ""
. E) G9 i! w4 Q1 L4 X( [Plan hash value: 14740945834 I- v1 B7 q2 D" f
----------------------------------------------------------------------------------------------
) B; K* G/ ^8 D| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
3 l% K" g, \: x& v8 y+ k+ J----------------------------------------------------------------------------------------------* Q' H3 U8 {5 z# n0 x" j
| 0 | SELECT STATEMENT | | 117K| 2856K| | 1592 (1)| 00:00:20 |
: [+ p( z- w! f# i* o| 1 | SORT ORDER BY | | 117K| 2856K| 4152K| 1592 (1)| 00:00:20 |; n2 u# d# Q0 V6 x4 S0 q
|* 2 | VIEW | | 117K| 2856K| | 744 (2)| 00:00:09 |
7 N* L! s3 c! S; F2 H|* 3 | WINDOW SORT PUSHED RANK| | 117K| 1371K| 2304K| 744 (2)| 00:00:09 |
9 h3 E$ P- X% J$ T5 h9 z| 4 | TABLE ACCESS FULL | CUSTOMER | 117K| 1371K| | 205 (1)| 00:00:03 |" g d, W6 N# c1 l' \
----------------------------------------------------------------------------------------------5 K0 n; l" u" C2 \ D: F3 B
Predicate Information (identified by operation id):/ a7 J/ x) _/ |% g: V E
---------------------------------------------------$ u8 ]. J2 j G& s. d
2 - filter("RN">=1 AND "RN" alter table customer modify first_name not null;
1 S3 J& I0 J* C! b" E' `Table altered./ T0 d0 ]5 ]8 t \
SQL> explain plan for select * from (& j! J0 I8 l2 a; ~! j1 A
2 select /*+ FIRST_ROWS(20) */ FIRST_NAME, ROW_NUMBER() over (order by FIRST_NAME) RN* R! y; v5 f8 m3 L7 s
3 from CUSTOMER C
% u) N' g( n/ a7 I/ A2 } 4 )# W* t0 [# h8 t' e& J
5 where RN between 1 and 206 F: Z2 T9 Y1 n2 q
6 order by RN;
/ f. |/ ] I/ D4 G# H/ i0 a8 N$ tExplained.
) ?/ a" D* c- \& i' S. D) N, _SQL> @explain ""3 j8 M' U D+ X$ @( k, `& L
Plan hash value: 17250281382 o" u+ p$ ~5 M
----------------------------------------------------------------------------------------& {1 V1 ^' e. @1 D
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
( \% }! h8 {, ~' `( f) r----------------------------------------------------------------------------------------
8 s" X7 F" J# E% F7 n" I3 u% n| 0 | SELECT STATEMENT | | 117K| 2856K| | 850 (1)| 00:00:11 |
$ v4 Y( l& ^" Y# {| 1 | SORT ORDER BY | | 117K| 2856K| 4152K| 850 (1)| 00:00:11 |& f# w7 v# X% ]* A3 n7 ^
|* 2 | VIEW | | 117K| 2856K| | 2 (0)| 00:00:01 |& m7 j8 c0 e( ]# V# h7 \, x" U6 G
|* 3 | WINDOW NOSORT STOPKEY| | 117K| 1371K| | 2 (0)| 00:00:01 |) J6 ?# V V, }2 o
| 4 | INDEX FULL SCAN | C | 117K| 1371K| | 2 (0)| 00:00:01 |& Q. @) e+ I, x' B
----------------------------------------------------------------------------------------
' B0 l' [ T4 g: |" y0 u0 yPredicate Information (identified by operation id):' u: X* g2 d( \( n7 \( H
---------------------------------------------------
f" Y- @+ |& l s6 e 2 - filter("RN">=1 AND "RN"! g' i# i* e' f% N% h# w6 Q
在其中添加一个NOT NULL来解决它。/ A$ N- Q }1 b; O
SQL> explain plan for select * from (
$ G* T/ t/ i5 z9 H. Q& v( k$ L 2 select /*+ FIRST_ROWS(20) */ FIRST_NAME, ROW_NUMBER() over (order by FIRST_NAME) RN
8 e7 w9 O2 [: ]+ a& p R 3 from CUSTOMER C
* E# y0 _, D( Y: R9 g 4 where first_name is not null
/ a; w. F4 C1 q. W* s" M+ o" @ 5 )
, G9 R3 z2 V% W 6 where RN between 1 and 20, r: \( H8 f6 V( T. h- M
7 order by RN;! |! e8 e* q4 M1 }7 r: g6 T. z
Explained.; w0 n0 E( }, `& p: R
SQL> @explain ""
1 X0 {& q. ?9 }8 D( E, fPlan hash value: 1725028138* I; P! i. O7 o4 t3 u4 a- ?
----------------------------------------------------------------------------------------
/ P5 o7 e8 h9 Z% j1 K ^6 x% V| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
% W" J9 \* \8 z5 `$ \2 l----------------------------------------------------------------------------------------
' y8 E+ Y% c; }5 w3 M: O; p3 p; V! K& || 0 | SELECT STATEMENT | | 117K| 2856K| | 850 (1)| 00:00:11 |
$ [6 j9 f. \" M9 p3 P7 {$ d8 f| 1 | SORT ORDER BY | | 117K| 2856K| 4152K| 850 (1)| 00:00:11 |
: B- b( i9 t/ e1 Q: ^1 Q|* 2 | VIEW | | 117K| 2856K| | 2 (0)| 00:00:01 |
9 ?/ W/ M9 Q4 D$ K. G|* 3 | WINDOW NOSORT STOPKEY| | 117K| 1371K| | 2 (0)| 00:00:01 |
( C$ u" v0 y6 k|* 4 | INDEX FULL SCAN | C | 117K| 1371K| | 2 (0)| 00:00:01 |
5 d+ V# n h. ^5 e p----------------------------------------------------------------------------------------( P% j3 b7 d6 m- ?
Predicate Information (identified by operation id):' V4 T% X# ?4 @4 K+ e) {
---------------------------------------------------
3 ]% T( x& _* S9 e 2 - filter("RN">=1 AND "RN" |
|
技术问答
290 人阅读
|
0 人回复
|
2023-09-14
