Re: default select order
Date: Wed, 7 Jan 2009 12:27:26 -0800 (PST)
Message-ID: <b3194a34-f129-4ef3-bfbc-795841e38a4d_at_a12g2000yqm.googlegroups.com>
On Jan 7, 1:51 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> On Jan 7, 2:29 pm, ddf <orat..._at_msn.com> wrote:
>
> snip
>
> > As far as I know such an order variance shouldn't occur, and I cannot
> > produce the OP's posted result with 10.2.0.3.0. This is not a
> > question of behaviour between two releases of Oracle, it's differing
> > behaviour for the same table and data in release 10gR2 (which patch
> > level is still unknown). Given that, the query plans should be the
> > same for the two results, leaving one to wonder if this is a bug in
> > 10.2.0.1.0 fixed in subsequent patchsets.
>
> You are guessing without a complete definition from the OP of the
> tables and indexes and statistics involved.
>
> Nothing wrong with taking a guess ...
>
> Oracle has the ability to retrieve column data from an index at
> times. Selecting one column with the same where clause as a different
> column "just ain't the same' without a good understanding of relevant
> indexes if any among other things.
Let's try this:
SQL> -- SQL> -- Basic table definition based upon problem statement SQL> -- SQL> create table table_z( 2 x varchar2(1), 3 y varchar2(45), 4 something varchar2(20)
5 );
Table created.
SQL> -- SQL> -- Indexes based upon information supplied by OP SQL> -- SQL> create index table_z_ind on
2 table_z(x,something);
Index created.
SQL>
SQL> create index table_z_ind2 on
2 table_z(something);
Index created.
SQL> -- SQL> -- Sample data set 1 SQL> -- SQL> insert all
2 into table_z
3 values('A','Avoin', 'SOMETHING')
4 into table_z
5 values('A','Avoin', 'NOTHING')
6 into table_z
7 values('X','Avoin', 'NOTHING')
8 into table_z
9 values('V','Valmis', 'SOMETHING')
10 into table_z
11 values('V','Valmis', 'NOTHING')
12 into table_z
13 values('X','Avoin', 'NOTHING')
14 into table_z
15 values('X','Avoin', 'NOTHING')
16 into table_z
17 values('X','Avoin', 'NOTHING')
18 into table_z
19 values('E','Ei kaytossa', 'NOTHING') 20 into table_z
21 values('E','Ei kaytossa', 'SOMETHING') 22 into table_z
23 values('X','Avoin', 'NOTHING')
24 into table_z
25 values('X','Avoin', 'NOTHING')
26 into table_z
27 values('X','Avoin', 'NOTHING')
28 into table_z
29 values('X','Avoin', 'NOTHING')
30 into table_z
31 values('X','Avoin', 'NOTHING')
32 select * From dual;
15 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');
PL/SQL procedure successfully completed.
SQL> -- SQL> -- Plans differ, based upon index used SQL> -- SQL> -- Result order differs, too, which reproduces the OPs SQL> -- original post SQL> -- SQL> SELECT X FROM TABLE_Z WHERE SOMETHING ='SOMETHING' ;
X
-
A
E
V
Execution Plan
Plan hash value: 4268080605
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 88 | 1 (0)| 00:00:01 | |* 1 | INDEX FULL SCAN | TABLE_Z_IND | 8 | 88 | 1 (0)|00:00:01 |
Predicate Information (identified by operation id):
1 - access("SOMETHING"='SOMETHING')
filter("SOMETHING"='SOMETHING')
SQL>
SQL> SELECT Y FROM TABLE_Z WHERE SOMETHING='SOMETHING' ;
Y
Avoin
Valmis
Ei kaytossa
Execution Plan
Plan hash value: 1079978889
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 128 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TABLE_Z | 8 | 128 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TABLE_Z_IND2 | 8 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access("SOMETHING"='SOMETHING')
SQL>
SQL> truncate table table_z;
Table truncated.
SQL> -- SQL> -- Insert a different data set SQL> -- SQL> insert all
2 into table_z
3 values('A','Avoin', 'SOMETHING')
4 into table_z
5 values('X','Avoin', 'NOTHING')
6 into table_z
7 values('X','Avoin', 'NOTHING')
8 into table_z
9 values('X','Avoin', 'NOTHING')
10 into table_z
11 values('X','Avoin', 'NOTHING')
12 into table_z
13 values('X','Avoin', 'NOTHING')
14 into table_z
15 values('X','Avoin', 'NOTHING')
16 into table_z
17 values('X','Avoin', 'NOTHING')
18 into table_z
19 values('X','Avoin', 'NOTHING')
20 into table_z
21 values('A','Avoin', 'NOTHING')
22 into table_z
23 values('E','Ei kaytossa', 'SOMETHING') 24 into table_z
25 values('X','Avoin', 'NOTHING')
26 into table_z
27 values('E','Ei kaytossa', 'NOTHING') 28 into table_z
29 values('V','Valmis', 'SOMETHING')
30 into table_z
31 values('X','Avoin', 'NOTHING')
32 into table_z
33 values('X','Avoin', 'NOTHING')
34 into table_z
35 values('V','Valmis', 'NOTHING')
36 select * From dual;
17 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');
PL/SQL procedure successfully completed.
SQL> --
SQL> -- With same indexes but slightly different data the plans still
differ
SQL> -- but the results are returned in the same order regardless of
which
SQL> -- column is selected SQL> -- SQL> SELECT X FROM TABLE_Z WHERE SOMETHING ='SOMETHING' ;
X
-
A
E
V
Execution Plan
Plan hash value: 4268080605
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 33 | 1 (0)| 00:00:01 | |* 1 | INDEX FULL SCAN | TABLE_Z_IND | 3 | 33 | 1 (0)|00:00:01 |
Predicate Information (identified by operation id):
1 - access("SOMETHING"='SOMETHING')
filter("SOMETHING"='SOMETHING')
SQL>
SQL> SELECT Y FROM TABLE_Z WHERE SOMETHING='SOMETHING' ;
Y
Avoin
Ei kaytossa
Valmis
Execution Plan
Plan hash value: 1079978889
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 48 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TABLE_Z | 3 | 48 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TABLE_Z_IND2 | 3 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access("SOMETHING"='SOMETHING')
SQL> So, John, your guess was a bit better than my first one. Why didn't you share your work with the rest of us?
David Fitzjarrell Received on Wed Jan 07 2009 - 14:27:26 CST