Re: Really weird question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 16 Aug 2001 13:12:07 -0700
Message-ID: <9lh9en08th@drn.newsguy.com>

In article <9lh66g$ovt$1_at_freenet9.carleton.ca>, aj739_at_FreeNet.Carleton.CA says...
>
>
>I have a table with approximately 6000 rows in an 8.1.7 database on an HP
>UX server.
>
>I run a query SELECT COL1 FROM TABLE1;
>
>Then I run a new query SELECT COL1, COL2 FROM TABLE1;
>The rows are in exactly the same order as when I ran the first query.
>
>Next I run the query SELECT COL1, COL2, COL3 FROM TABLE1;
>The rows are no longer in the same order.
>
>Does anyone have an explanation of why this happens?
>
>Marg

Well, unless you have an ORDER By, we are free to return the records in any order we want.

Enable autotrace (see
http://osi.oracle.com/~tkyte/article1/autotrace.html ) and see what the plan is....

I'll take an educated guess. one of col1, col2 is NOT NULL (at least) and there is an index on col1, col2 that does not include col3

For the first two queries, we index full scan (faster then full scanning the table). For the last we, we full scan the table.

Example:

ops$tkyte_at_ORA817.US.ORACLE.COM> create table t ( x int, y int, z int, primary key(x,y) );

Table created.

ops$tkyte_at_ORA817.US.ORACLE.COM> insert into t values ( 2,2,2 ); 1 row created.
ops$tkyte_at_ORA817.US.ORACLE.COM> insert into t values ( 1,1,1 ); 1 row created.

ops$tkyte_at_ORA817.US.ORACLE.COM> exec dbms_stats.set_table_stats( user, 'T', numrows => 10000000, numblks => 1000000, avgrlen => 1000);

PL/SQL procedure successfully completed.

That makes is look expensive to full scan T, its really big.

ops$tkyte_at_ORA817.US.ORACLE.COM> set autotrace on explain

ops$tkyte_at_ORA817.US.ORACLE.COM> select x from t;

         1
         2

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=10000000 Bytes=130000000) 1 0 INDEX (FAST FULL SCAN) OF 'SYS_C0016747' (UNIQUE) (Cost=1 Card=10000000 Bytes=130000000)

ops$tkyte_at_ORA817.US.ORACLE.COM> select x,y from t;

X Y
---------- ----------

         1          1
         2          2

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=10000000 Bytes=260000000) 1 0 INDEX (FAST FULL SCAN) OF 'SYS_C0016747' (UNIQUE) (Cost=1 Card=10000000 Bytes=260000000)

ops$tkyte_at_ORA817.US.ORACLE.COM> select x,y,z from t;

X Y Z
---------- ---------- ----------

         2          2          2
         1          1          1

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28788 Card=10000000 Bytes=390000000)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=28788 Card=10000000 Bytes=390000000)

Same results as you (different row order) since the index scan has the data organized different then the table does.

If you need data in a predicable order you MUST order by -- there is no skipping that step.

ops$tkyte_at_ORA817.US.ORACLE.COM>
ops$tkyte_at_ORA817.US.ORACLE.COM> set autotrace off

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Thu Aug 16 2001 - 15:12:07 CDT