order by dbms_random.random问题

Yong Huang

原帖由 szusunny 于 2009-11-3 03:11 发表
其实order by dbms_random.random 并不是随机用一个列排序, 而是增加一个随机列, 然后按这随机列进行排序!
等价于select i.*,dbms_random.random from all_objects i order by dbms_random.random;

I agree with you. You can run this SQL multiple times to see the result:

SQL> select table_name, dbms_random.value from user_tables order by dbms_random.value;

TABLE_NAME        VALUE
---------- ------------
T1          .0677720002
T4         .29022524836
T2         .29651656741
JUNK2      .32677403399
TESTNULL   .49501084415
T3         .55977963534
TEST       .57477114003
TESTNLS    .63702901646
EMPTY_TS   .65707118667
TESTCHAR   .77008281445
TABLE1      .8539139886
T          .95402244366

12 rows selected.

Somebody asked Tom Kyte the same question at
http://asktom.oracle.com/pls/ask ... ON_ID:6075151195522

See "Still not clear...   October 22, 2002 - 12pm Central time zone". Unfortunately, Tom didn't really answer the question.

I looked up SQL Reference. It's not clear either:

"Use the ORDER BY clause to order rows returned by the statement."
"expr [i.e. the expression following 'order by'] orders rows based on their value for expr. The expression is based on columns in the select list or columns in the tables, views, or materialized views in the FROM clause."

It may be documented somewhere, but I didn't find it. We can think of "dbms_random.value" in the "order by" clause as a psedocolumn.

We definitely should not consider it as column position. Consider this:

SQL> create or replace function f return number as begin return 1; end;
  2  /

Function created.

SQL> select table_name from user_tables order by f;

TABLE_NAME
----------
TESTCHAR
TESTNULL
TESTNLS
EMPTY_TS
TEST
T
T2
T3
T4
TABLE1
JUNK2
T1

12 rows selected.

SQL> select table_name, f from user_tables order by f;

TABLE_NAME            F
---------- ------------
TESTCHAR              1
TESTNULL              1
TESTNLS               1
EMPTY_TS              1
TEST                  1
T                     1
T2                    1
T3                    1
T4                    1
TABLE1                1
JUNK2                 1
T1                    1

12 rows selected.

You see the function f which always returns 1 does not make the table_name sort. It's NOT the same as "select table_name from user_tables order by 1".

Yong Huang

fan_zhen_hua

yong huang讲的很精辟，我从来没想过这么多。。受益非浅~~
"expr [i.e. the expression following 'order by'] orders rows based on their value for expr. The expression is based on columns in the select list or columns in the tables, views, or materialized views in the FROM clause."

order by 表达式是按表达式值来排序，此时如果算第几列没什么意义，也理解不通。所以最原始的问题，应该是以不可预测的顺序来产生一个行集。。
谢谢~

sqysl

明白了，学习。