oracle 排序 结果不对,Oracle中，何种查询在不使用order by的情况下返回排序的结果...

这是昨天一个朋友问我的问题。这里谈到的排序，指的对任何一列字段排序后返回的结果都算。当时一时还真没答出来，今天回来琢磨了下，写了下面的文字。欢迎大家讨论。

一 单表查询的情况

1、 查询索引组织表，将返回排序的数据；

2、 对于堆组织表，则有许多种情况：

A、 普通查询，不进行排序操作；

B、 查询中包含group by的查询，返回排序数据

SQL> select object_name, count(*)

2 from

t1_no_index

3 where

rownum <= 50

4 group

by object_name;

已选择50行。

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT

(GROUP BY)

2 1 COUNT (STOPKEY)

3 2 TABLE ACCESS (FULL) OF 'T1_NO_INDEX'

我们可以看到，执行计划中，包含有排序操作。

C、 查询中包含distinct的查询，返回排序数据

SQL> select distinct object_name

2 from

t1_no_index

3 where

rownum <= 50;

已选择50行。

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT

(UNIQUE)

2 1 COUNT (STOPKEY)

3 2 TABLE ACCESS (FULL) OF 'T1_NO_INDEX'

我们可以看到，执行计划中，也包含有排序操作。

D、 如果执行计划使用全索引扫描，也会返回排序数据

SQL> select object_name

2 from t1

3 where

rownum <= 50;

已选择50行。

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=50 Bytes=450)

1 0 COUNT (STOPKEY)

2 1 INDEX (FAST FULL SCAN) OF

'IDX_T1_OBJECT_NAME' (NON-UNIQ

UE) (Cost=4 Card=31367 Bytes=282303)

二 多表连接查询的情况

我们仅仅以两表连接为例。

1、 如果执行计划执行HASH

JOIN操作，返回结果不排序

SQL> select t.object_name

2 from t,

t1

3 where

t.object_name = t1.object_name

4 and rownum <= 50;

已选择50行。

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=36 Card=50 Bytes=900

)

1 0 COUNT (STOPKEY)

2 1 HASH JOIN (Cost=36 Card=31367 Bytes=564606)

3 2 INDEX (FAST FULL SCAN) OF 'IDX_T_OBJECT_NAME' (NON-UNI

QUE) (Cost=4 Card=31367 Bytes=282303)

4 2 INDEX (FAST FULL SCAN) OF 'IDX_T1_OBJECT_NAME' (NON-UN

IQUE) (Cost=4 Card=31367 Bytes=282303)

在上面的例子中，两张表都采用快速索引扫描，但是还是返回不排序的结果。

2、 如果两表在执行NESTED

LOOPS连接前是排序好的结果，则返回排序数据

SQL> select t.object_name, t.object_id

2 from t,

t1

3 where

t.object_name = t1.object_name

4 and t.object_name between '100000050' and '100000099';

已选择50行。

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=22)

1 0 NESTED LOOPS (Cost=4 Card=1

Bytes=22)

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=1 Byte

s=13)

3 2 INDEX (RANGE SCAN) OF 'IDX_T_OBJECT_NAME' (NON-UNIQUE)

(Cost=2 Card=1)

4 1 INDEX (RANGE SCAN) OF 'IDX_T1_OBJECT_NAME' (NON-UNIQUE)

(Cost=1 Card=1 Bytes=9)

3、 如果两表连接采用MERGE

JOIN，则返回排序数据

SQL> select

2 t.object_name, t.object_id

3 from

t_no_index t, t1_no_index t1

4 where

t.object_name = t1.object_name

5 and t1.object_name between '100000050' and '100000099';

已选择50行。

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 MERGE

JOIN

2 1 SORT (JOIN)

3 2 TABLE ACCESS (FULL) OF 'T1_NO_INDEX'

4 1 SORT (JOIN)

5 4 TABLE ACCESS (FULL) OF 'T_NO_INDEX'

4、 如果两表连接使用了UNION，则返回排序数据

SQL> select object_name, object_id

2 from

t_no_index

3 where

object_name between '100000050' and '100000099'

4 union

5 select object_name,

object_id

6 from

t1_no_index

7 where

object_name between '100000150' and '100000199';

已选择100行。

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT

(UNIQUE)

2 1 UNION-ALL

3 2 TABLE ACCESS (FULL) OF 'T_NO_INDEX'

4 2 TABLE ACCESS (FULL) OF 'T1_NO_INDEX'

5、 如果两表连接使用了UNION

ALL，则不返回排序数据。

SQL> select object_name, object_id

2 from

t_no_index

3 where

object_name between '100000050' and '100000099'

4 union

all

5 select object_name,

object_id

6 from

t_no_index

7 where

object_name between '100000150' and '100000199';

已选择100行。

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 UNION-ALL

2 1 TABLE ACCESS (FULL) OF 'T_NO_INDEX'

3 1 TABLE ACCESS (FULL) OF 'T_NO_INDEX'

暂时只想到这么多了。欢迎大家补充。