Column Order in a Table – Does it Matter? 1

May 22, 2010 (Updated May 23, 2010)

(Forward to the Next Post in the Series)

This week, between periodic outages, a couple of message threads appeared on the OTN forums that caught my attention.  The first is a thread in which the original poster asked how to add a column between two existing columns in a table – a task that is fairly easy to do in a database such as Microsoft Access, but is not possible without recreating the table in an Oracle database.  When I first started working with Oracle databases, I too wanted to see the columns listed in a specific, logical order in the tables that I created.  The general response in the thread was that the column order did not matter because it is possible to select the table’s columns in whatever order is preferred.

The second thread included a question asking whether column order could potentially affect the performance of a query.  Responders in the thread provided several different answers that boiled down to:

• Not a chance.
• Yes with the rule based optimizer, but not with the cost-based optimizer
• Maybe

In 2008 I read the book Troubleshooting Oracle Performance, and recently started re-reading the book.  In the book I recall reading a couple of paragraphs that suggested that the order of the columns in a table may, in fact, affect the performance of queries.  How?

• The calculated CPU_COST for a line in an execution plan increases – accessing the first column will have a slightly lower CPU_COST than would accessing the second column in a table.  This change in the calculated CPU_COST could cause a different execution plan to be generated.
• With variable length columns in a table, Oracle is not able to quickly determine the starting byte location of the tenth column without first determining the byte length of the first nine columns.  While this calculation may not seem significant, it can increase execution time, especially for servers with slower CPUs and higher latency memory.
• Edit: See the first comment about row chaining, written by Chris Antognini below.

Page 529 of the book Troubleshooting Oracle Performance includes a helpful chart that shows that, for a particular query, accessing the 250th column in a table required roughly five times as long as accessing the first column.  There are of course a lot of variables at play including:

• The speed of the server.
• The table design – what type of columns were in the table, what were the column lengths, and how many columns were in the table.
• The background activity in the server due to the other sessions competing for CPU, memory, and disk access.
• The number of times the test was repeated.
• Were all of the blocks in memory, or were physical reads required.

I commented in the second thread, stating that a couple people have reported that their testing found that the position of a column in a table could affect performance, and that is why the optimizer generates a different CPU_COST component for each column in a table.  I did not have an independent test case to verify the results that others obtained, so I posted links to the results that were provided by other people who have tested a column position’s effect on performance.  Was it a bad choice for me to provide an answer without first verifying that it could happen?

I think that we need to put together a test case to see if the column order really matters.  It is a little challenging to put together a test case where the outcome is not affected by spurious network interference, client-side delays, execution statistics calculations, Oracle Database release specific optimizations, and other similar issues.  For example, if you execute a SQL statement twice from a client on an otherwise idle server and see that the number of consistent gets increased on the second execution, that could throw off the accuracy of any intended benchmark test (this behavior was encountered on the 64 bit version of Oracle Database 11.2.0.1).

So, how to test?  We will create a test script that uses PL/SQL to repeatedly execute the same SQL statement, potentially with different WHERE clause predicates, to limit the effects of adaptive cursor sharing and the various other potential problem areas.  The server will run the 64 bit version of Oracle Database 11.2.0.1 on the Linux and Windows platforms with essentially the same hardware to help determine the effects of excessive context switching, and potential optimizations on each operating system platform – there may still be too many variables to state that one operating system platform is more efficient than the other for this specific test script.   The database instance will be configured with the SGA_TARGET set to 8000M, and the FILESYSTEMIO_OPTIONS parameter set to SETALL to enable direct, asychronous I/O (the default on the Windows platform).  As best as possible, this test will seek to factor out delays caused by physical reads, so event 10949 will be set to limit the chances of Oracle switching to physical direct path reads during the portion of the test that performs full table scans – ideally, the entire table and indexes will reside in the buffer cache.

First, we will create a test table with the same set of column definitions in the first couple of columns of the table as are present in the last columns of the table, with several VARCHAR2 columns in between.  Several indexes will be created which may be used for additional tests to determine what happens when the table columns referenced by an index are not stored in numerical/alphabetic order – not all of the indexes will be used for this test.

CREATE TABLE T11 (
  N0 NUMBER,
  N1 NUMBER,
  ND2 NUMBER,
  V3 VARCHAR2(30),
  VD4 VARCHAR2(30),
  D5 DATE,
  DD6 DATE,
  V7 VARCHAR2(50),
  V8 VARCHAR2(50),
  V9 VARCHAR2(50),
  V10 VARCHAR2(50),
  V11 VARCHAR2(50),
  V12 VARCHAR2(50),
  V13 VARCHAR2(50),
  V14 VARCHAR2(50),
  V15 VARCHAR2(50),
  V16 VARCHAR2(50),
  V17 VARCHAR2(50),
  V18 VARCHAR2(50),
  V19 VARCHAR2(50),
  V20 VARCHAR2(50),
  V21 VARCHAR2(50),
  V22 VARCHAR2(50),
  V23 VARCHAR2(50),
  V24 VARCHAR2(50),
  V25 VARCHAR2(50),
  V26 VARCHAR2(50),
  V27 VARCHAR2(50),
  V28 VARCHAR2(50),
  V29 VARCHAR2(50),
  V30 VARCHAR2(50),
  V31 VARCHAR2(50),
  V32 VARCHAR2(50),
  V33 VARCHAR2(50),
  V34 VARCHAR2(50),
  V35 VARCHAR2(50),
  V36 VARCHAR2(50),
  V37 VARCHAR2(50),
  V38 VARCHAR2(50),
  V39 VARCHAR2(50),
  V40 VARCHAR2(50),
  N41 NUMBER,
  ND42 NUMBER,
  V43 VARCHAR2(30),
  VD44 VARCHAR2(30),
  D45 DATE,
  DD46 DATE);

INSERT INTO
  T11
SELECT
  ROWNUM,
  ROWNUM,
  1000000-ROWNUM,
  CHR(MOD(ROWNUM-1,26)+65)||CHR(MOD(ROWNUM,26)+65)||CHR(MOD(ROWNUM+1,26)+65)||
    CHR(MOD(ROWNUM+2,26)+65)||CHR(MOD(ROWNUM+3,26)+65)||CHR(MOD(ROWNUM+4,26)+65),
  REVERSE(CHR(MOD(ROWNUM-1,26)+65)||CHR(MOD(ROWNUM,26)+65)||CHR(MOD(ROWNUM+1,26)+65)||
    CHR(MOD(ROWNUM+2,26)+65)||CHR(MOD(ROWNUM+3,26)+65)||CHR(MOD(ROWNUM+4,26)+65)),
  TRUNC(SYSDATE) + ROWNUM/1000,
  TRUNC(SYSDATE) + (1000000-ROWNUM)/1000,
  TO_CHAR(ROWNUM),
  TO_CHAR(1000000-ROWNUM),
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  TO_CHAR(ROWNUM),
  TO_CHAR(1000000-ROWNUM),
  ROWNUM,
  1000000-ROWNUM,
  CHR(MOD(ROWNUM-1,26)+65)||CHR(MOD(ROWNUM,26)+65)||CHR(MOD(ROWNUM+1,26)+65)||
    CHR(MOD(ROWNUM+2,26)+65)||CHR(MOD(ROWNUM+3,26)+65)||CHR(MOD(ROWNUM+4,26)+65),
  REVERSE(CHR(MOD(ROWNUM-1,26)+65)||CHR(MOD(ROWNUM,26)+65)||CHR(MOD(ROWNUM+1,26)+65)||
    CHR(MOD(ROWNUM+2,26)+65)||CHR(MOD(ROWNUM+3,26)+65)||CHR(MOD(ROWNUM+4,26)+65)),
  TRUNC(SYSDATE) + ROWNUM/1000,
  TRUNC(SYSDATE) + (1000000-ROWNUM)/1000
FROM
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=1000) V1,
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=1000) V2;

COMMIT;

CREATE INDEX T11_N1 ON T11(N1);
CREATE INDEX T11_N41 ON T11(N41);
CREATE INDEX T11_ND2 ON T11(ND2);
CREATE INDEX T11_ND42 ON T11(ND42);
CREATE INDEX T11_V3 ON T11(V3);
CREATE INDEX T11_VD4 ON T11(VD4);
CREATE INDEX T11_V43 ON T11(V43);
CREATE INDEX T11_VD44 ON T11(VD44);
CREATE INDEX T11_D5 ON T11(D5);
CREATE INDEX T11_DD6 ON T11(DD6);
CREATE INDEX T11_D45 ON T11(D45);
CREATE INDEX T11_DD46 ON T11(DD46);

ALTER TABLE T11 CACHE;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T11',CASCADE=>TRUE)

In the above, notice that a NULL value was assigned to most of the columns located between the two repeating set of column definitions.  1,000,000 rows were inserted into the table.

The testing script that we will use follows.  Essentially, the script is composed of serveral anonymous PL/SQL blocks that compare the execution performance of repeatedly selecting from the first set of columns with the execution performance of repeatedly selecting from the last set of columns.  The first set of tests attempts to test performance using a large number of index range scan accesses, while the second set of tests attempts to test performance using a smaller number of full table scan accesses.

The test script will be repeated 3 times: 1) to flood the buffer cache with the blocks used by the tests (these results will be discarded), 2) with 10046 trace at level 8 enabled, 3) without a 10046 trace enabled.

SET TIMING ON
SET SERVEROUTPUT ON SIZE 1000000

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'COLUMN_POSITION_TEST';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

SPOOL COLUMN_POSITION_TEST.TXT

DECLARE
  tsStart TIMESTAMP := SYSTIMESTAMP;

  CURSOR cuCursor(intVALUE IN NUMBER) IS
    SELECT /*+ FIND_ME */
      N1,
      ND2
    FROM
      T11
    WHERE
      N1 BETWEEN intVALUE AND INTVALUE+10000;

  TYPE TYPE_cuCursor IS TABLE OF cuCursor%ROWTYPE
    INDEX BY BINARY_INTEGER;

  T_cuCursor TYPE_cuCursor;

BEGIN
  FOR I IN 1..100000 LOOP
    OPEN cuCursor(I);

    LOOP
      FETCH cuCursor BULK COLLECT INTO T_cuCursor LIMIT 1000;
      EXIT WHEN T_cuCursor.COUNT = 0;

      NULL;
    END LOOP;

    CLOSE cuCursor;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('N1 ND2: '||TO_CHAR(SYSTIMESTAMP-tsStart));
END;
/

DECLARE
  tsStart TIMESTAMP := SYSTIMESTAMP;

  CURSOR cuCursor(intVALUE IN NUMBER) IS
    SELECT /*+ FIND_ME */
      N41,
      ND42
    FROM
      T11
    WHERE
      N41 BETWEEN intVALUE AND INTVALUE+10000;

  TYPE TYPE_cuCursor IS TABLE OF cuCursor%ROWTYPE
    INDEX BY BINARY_INTEGER;

  T_cuCursor TYPE_cuCursor;

BEGIN
  FOR I IN 1..100000 LOOP
    OPEN cuCursor(I);

    LOOP
      FETCH cuCursor BULK COLLECT INTO T_cuCursor LIMIT 1000;
      EXIT WHEN T_cuCursor.COUNT = 0;

      NULL;
    END LOOP;

    CLOSE cuCursor;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('N41 ND42: '||TO_CHAR(SYSTIMESTAMP-tsStart));
END;
/

DECLARE
  tsStart TIMESTAMP := SYSTIMESTAMP;

  CURSOR cuCursor(intVALUE IN NUMBER) IS
    SELECT /*+ FIND_ME */
      V3,
      VD4
    FROM
      T11
    WHERE
      N1 BETWEEN intVALUE AND INTVALUE+10000;

  TYPE TYPE_cuCursor IS TABLE OF cuCursor%ROWTYPE
    INDEX BY BINARY_INTEGER;

  T_cuCursor TYPE_cuCursor;

BEGIN
  FOR I IN 1..100000 LOOP
    OPEN cuCursor(I);

    LOOP
      FETCH cuCursor BULK COLLECT INTO T_cuCursor LIMIT 1000;
      EXIT WHEN T_cuCursor.COUNT = 0;

      NULL;
    END LOOP;

    CLOSE cuCursor;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('V3 VD4: '||TO_CHAR(SYSTIMESTAMP-tsStart));
END;
/

DECLARE
  tsStart TIMESTAMP := SYSTIMESTAMP;

  CURSOR cuCursor(intVALUE IN NUMBER) IS
    SELECT /*+ FIND_ME */
      V43,
      VD44
    FROM
      T11
    WHERE
      N41 BETWEEN intVALUE AND INTVALUE+10000;

  TYPE TYPE_cuCursor IS TABLE OF cuCursor%ROWTYPE
    INDEX BY BINARY_INTEGER;

  T_cuCursor TYPE_cuCursor;

BEGIN
  FOR I IN 1..100000 LOOP
    OPEN cuCursor(I);

    LOOP
      FETCH cuCursor BULK COLLECT INTO T_cuCursor LIMIT 1000;
      EXIT WHEN T_cuCursor.COUNT = 0;

      NULL;
    END LOOP;

    CLOSE cuCursor;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('V43 VD44: '||TO_CHAR(SYSTIMESTAMP-tsStart));
END;
/

DECLARE
  tsStart TIMESTAMP := SYSTIMESTAMP;

  CURSOR cuCursor(intVALUE IN NUMBER) IS
    SELECT /*+ FIND_ME */
      D5,
      DD6
    FROM
      T11
    WHERE
      N1 BETWEEN intVALUE AND INTVALUE+10000;

  TYPE TYPE_cuCursor IS TABLE OF cuCursor%ROWTYPE
    INDEX BY BINARY_INTEGER;

  T_cuCursor TYPE_cuCursor;

BEGIN
  FOR I IN 1..100000 LOOP
    OPEN cuCursor(I);

    LOOP
      FETCH cuCursor BULK COLLECT INTO T_cuCursor LIMIT 1000;
      EXIT WHEN T_cuCursor.COUNT = 0;

      NULL;
    END LOOP;

    CLOSE cuCursor;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('D5 DD6: '||TO_CHAR(SYSTIMESTAMP-tsStart));
END;
/

DECLARE
  tsStart TIMESTAMP := SYSTIMESTAMP;

  CURSOR cuCursor(intVALUE IN NUMBER) IS
    SELECT /*+ FIND_ME */
      D45,
      DD46
    FROM
      T11
    WHERE
      N41 BETWEEN intVALUE AND INTVALUE+10000;

  TYPE TYPE_cuCursor IS TABLE OF cuCursor%ROWTYPE
    INDEX BY BINARY_INTEGER;

  T_cuCursor TYPE_cuCursor;

BEGIN
  FOR I IN 1..100000 LOOP
    OPEN cuCursor(I);

    LOOP
      FETCH cuCursor BULK COLLECT INTO T_cuCursor LIMIT 1000;
      EXIT WHEN T_cuCursor.COUNT = 0;

      NULL;
    END LOOP;

    CLOSE cuCursor;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('D45 DD46: '||TO_CHAR(SYSTIMESTAMP-tsStart));
END;
/

ALTER SESSION SET EVENTS '10949 TRACE NAME CONTEXT FOREVER, LEVEL 1';

DECLARE
  tsStart TIMESTAMP := SYSTIMESTAMP;

  CURSOR cuCursor IS
    SELECT /*+ FIND_ME */
      N1,
      ND2
    FROM
      T11;

  TYPE TYPE_cuCursor IS TABLE OF cuCursor%ROWTYPE
    INDEX BY BINARY_INTEGER;

  T_cuCursor TYPE_cuCursor;

BEGIN
  FOR I IN 1..100 LOOP
    OPEN cuCursor;

    LOOP
      FETCH cuCursor BULK COLLECT INTO T_cuCursor LIMIT 1000;
      EXIT WHEN T_cuCursor.COUNT = 0;

      NULL;
    END LOOP;

    CLOSE cuCursor;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('N1 ND2 2: '||TO_CHAR(SYSTIMESTAMP-tsStart));
END;
/

DECLARE
  tsStart TIMESTAMP := SYSTIMESTAMP;

  CURSOR cuCursor IS
    SELECT /*+ FIND_ME */
      N41,
      ND42
    FROM
      T11;

  TYPE TYPE_cuCursor IS TABLE OF cuCursor%ROWTYPE
    INDEX BY BINARY_INTEGER;

  T_cuCursor TYPE_cuCursor;

BEGIN
  FOR I IN 1..100 LOOP
    OPEN cuCursor;

    LOOP
      FETCH cuCursor BULK COLLECT INTO T_cuCursor LIMIT 1000;
      EXIT WHEN T_cuCursor.COUNT = 0;

      NULL;
    END LOOP;

    CLOSE cuCursor;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('N41 ND42 2: '||TO_CHAR(SYSTIMESTAMP-tsStart));
END;
/

DECLARE
  tsStart TIMESTAMP := SYSTIMESTAMP;

  CURSOR cuCursor IS
    SELECT /*+ FIND_ME */
      V3,
      VD4
    FROM
      T11;

  TYPE TYPE_cuCursor IS TABLE OF cuCursor%ROWTYPE
    INDEX BY BINARY_INTEGER;

  T_cuCursor TYPE_cuCursor;

BEGIN
  FOR I IN 1..100 LOOP
    OPEN cuCursor;

    LOOP
      FETCH cuCursor BULK COLLECT INTO T_cuCursor LIMIT 1000;
      EXIT WHEN T_cuCursor.COUNT = 0;

      NULL;
    END LOOP;

    CLOSE cuCursor;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('V3 VD4 2: '||TO_CHAR(SYSTIMESTAMP-tsStart));
END;
/

DECLARE
  tsStart TIMESTAMP := SYSTIMESTAMP;

  CURSOR cuCursor IS
    SELECT /*+ FIND_ME */
      V43,
      VD44
    FROM
      T11;

  TYPE TYPE_cuCursor IS TABLE OF cuCursor%ROWTYPE
    INDEX BY BINARY_INTEGER;

  T_cuCursor TYPE_cuCursor;

BEGIN
  FOR I IN 1..100 LOOP
    OPEN cuCursor;

    LOOP
      FETCH cuCursor BULK COLLECT INTO T_cuCursor LIMIT 1000;
      EXIT WHEN T_cuCursor.COUNT = 0;

      NULL;
    END LOOP;

    CLOSE cuCursor;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('V43 VD44 2: '||TO_CHAR(SYSTIMESTAMP-tsStart));
END;
/

DECLARE
  tsStart TIMESTAMP := SYSTIMESTAMP;

  CURSOR cuCursor IS
    SELECT /*+ FIND_ME */
      D5,
      DD6
    FROM
      T11;

  TYPE TYPE_cuCursor IS TABLE OF cuCursor%ROWTYPE
    INDEX BY BINARY_INTEGER;

  T_cuCursor TYPE_cuCursor;

BEGIN
  FOR I IN 1..100 LOOP
    OPEN cuCursor;

    LOOP
      FETCH cuCursor BULK COLLECT INTO T_cuCursor LIMIT 1000;
      EXIT WHEN T_cuCursor.COUNT = 0;

      NULL;
    END LOOP;

    CLOSE cuCursor;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('D5 DD6 2: '||TO_CHAR(SYSTIMESTAMP-tsStart));
END;
/

DECLARE
  tsStart TIMESTAMP := SYSTIMESTAMP;

  CURSOR cuCursor IS
    SELECT /*+ FIND_ME */
      D45,
      DD46
    FROM
      T11;

  TYPE TYPE_cuCursor IS TABLE OF cuCursor%ROWTYPE
    INDEX BY BINARY_INTEGER;

  T_cuCursor TYPE_cuCursor;

BEGIN
  FOR I IN 1..100 LOOP
    OPEN cuCursor;

    LOOP
      FETCH cuCursor BULK COLLECT INTO T_cuCursor LIMIT 1000;
      EXIT WHEN T_cuCursor.COUNT = 0;

      NULL;
    END LOOP;

    CLOSE cuCursor;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('D45 DD46 2: '||TO_CHAR(SYSTIMESTAMP-tsStart));
END;
/

ALTER SESSION SET EVENTS '10949 TRACE NAME CONTEXT OFF';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

SPOOL OFF

What were the test results, does column position in a table matter?  Is performance improved by locating frequently accessed columns in the first couple of column positions?  Does the operating system platform matter (there are a lot of factors contributing to any time differences)?

The script output should look something like this, which is the output from the Linux execution of the script with a 10046 trace enabled (blank spaces between lines were adjusted to group related time values):

N1 ND2: +000000000 00:12:14.750567000
PL/SQL procedure successfully completed.
Elapsed: 00:12:14.77

N41 ND42: +000000000 00:14:13.846775000
PL/SQL procedure successfully completed.
Elapsed: 00:14:13.87

V3 VD4: +000000000 00:12:28.702281000
PL/SQL procedure successfully completed.
Elapsed: 00:12:28.72

V43 VD44: +000000000 00:14:28.663870000
PL/SQL procedure successfully completed.
Elapsed: 00:14:28.68

D5 DD6: +000000000 00:11:58.169894000
PL/SQL procedure successfully completed.
Elapsed: 00:11:58.19

D45 DD46: +000000000 00:13:52.969594000
PL/SQL procedure successfully completed.
Elapsed: 00:13:52.98

Session altered.

N1 ND2 2: +000000000 00:00:41.202708000
PL/SQL procedure successfully completed.
Elapsed: 00:00:41.23

N41 ND42 2: +000000000 00:00:47.222258000
PL/SQL procedure successfully completed.
Elapsed: 00:00:47.22

V3 VD4 2: +000000000 00:00:42.662874000
PL/SQL procedure successfully completed.
Elapsed: 00:00:42.68

V43 VD44 2: +000000000 00:00:50.135338000
PL/SQL procedure successfully completed.
Elapsed: 00:00:50.13

D5 DD6 2: +000000000 00:00:38.307032000
PL/SQL procedure successfully completed.
Elapsed: 00:00:38.31

D45 DD46 2: +000000000 00:00:47.696470000
PL/SQL procedure successfully completed.
Elapsed: 00:00:47.70

In the above, the N1 and ND2 test run shows the performance when the second and third columns, both of which are numbers, were accessed using the index on the N1 column.  The N41 and ND42 test run shows the performance when the 42nd and 43rd columns, both of which are numbers, were accessed using the index on the N41 column.  Thus, the N indicates a numeric column, the V indicates a varchar2 column, and the D indicates a date column, all accessed with either the index on  the N1 column or N41 column.

Below is a summary of the test results when accessing the various columns.

The following code may be used to retrieve the execution plans just to confirm that the expected execution plan was selected, but we have something much better – a 10046 trace file for the execution.

SET TIMING OFF
SET PAGESIZE 1000
SET LINESIZE 160
SPOOL COL_POSITION_PLANS.TXT

SELECT /*+ LEADING(S) */
  T.PLAN_TABLE_OUTPUT
FROM
  (SELECT
    SQL_ID,
    CHILD_NUMBER
  FROM
    V$SQL
  WHERE
    SQL_TEXT LIKE 'SELECT /*+ FIND_ME */%') S,
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(S.SQL_ID,S.CHILD_NUMBER,'TYPICAL')) T;

SPOOL OFF

The TKPROF output for the Linux and Windows executions are attached below.  When downloading, strip off the .doc extension and open the files with a text viewer (Notepad, vi, jed, etc.).

ColumnOrder1TKPROF_LinuxX64.txt
ColumnOrder1TKPROF_WindowsX64.txt

So, how much of an impact does the column order have in your environment?  Oracle databases support up to 1000 columns per table.  How might the performance of selecting data from the second column compare to selecting data from the 902nd column?  How might the outcome change if physical block reads were required?  How might the outcome change if there was significant competition for the CPU cycles in the server?

Feel free to modify and extend the test script.