Printable View
we have recently migrated data from 9i to 10g..
platform MS server 2003 on both sides..
oracle 6i forms and reports both sides..
Earlier, data in forms and reports was displayed in sorted order..But After shifting to new server, data is not displayed in sorted order.
How could it be corrected?
Do you mean the output is not sorted at all? Then use the ORDER BY clause in queries.
If output is kind of sorted but the order is wrong, check the NLS_ parameters and if possible compare them with those on the old box.
Check the code.
Until Ora9i when you had a GROUP BY clause ORDER BY was implicit meaning Oracle would sort the output by the GROUP BY condition even if no ORDER BY was explicitily specified.
Starting on Ora10g if you want to ensure the output sorted you have to include both GROUP BY and ORDER BY clauses.
thanks it helped
this requires changing the coding of all forma and reports of my application dat i cant afford..Is there any way or parameter setting that allows implicit order by alongwith group by??
I need to do it urgently..
This never has been true. Not a big deal to show that:Quote:
Originally Posted by PAVB
http://groups.google.com/group/comp....e=source&hl=en
well momentarily, i got the solution by setting optimizer_features_enable='9.2.0'....
but i m willing to use the optimizer features of 10g later on...
is there any other way??
No, there isn't. Implicit ORDER BY with GROUP BY is a myth.Quote:
Originally Posted by mahajanakhil198
Quote from 9i docs:
http://download.oracle.com/docs/cd/B...3a.htm#2066158
Obviously, omitting the ORDER BY was a developer mistake. If you want sorted output you have to sort it explicitly. It apply to queries with GROUP BY or without it.Quote:
The GROUP BY clause groups rows but does not guarantee the order of the result set. To order the groupings, use the ORDER BY clause.
Changing the optimizer_features_enable is the only thing that might help. I hope it gives you enough time to adjust the queries.
Perhaps others here have some idea ...
Good luck.
myth????
here is the code..
SQL> select * from test;
A B
---------- ----------
3 200
2 300
8 100
5 400
1 300
4 500
8 200
5 300
4 600
2 300
3 500
2 100
7 300
13 rows selected.
SQL> select a,sum(b) from test
2 group by a;
A SUM(B)
---------- ----------
1 300
2 700
3 700
4 1100
5 700
7 300
8 300
7 rows selected.
Well, sometimes it works. But it doesn't work always. Launch the code bellow and post here if the output was sorted according the group by clause in ascending order. Mine wasn't in 9i nor in 10g.
Code:create table the_table (
pk1 int,
pk2 int,
nk1 int not null,
nk2 int not null,
primary key(pk1,pk2)
)
/
create index the_table_idx on the_table(pk1,nk1,pk2,nk2)
/
insert into the_table values (1, 1, 2, 2 );
insert into the_table values (2, 1, 1, 1 );
analyze table the_table compute statistics;
set autotrace on explain
select nk1, nk2, pk1, pk2, count(*) a_count
from the_table
group by nk1, nk2, pk1, pk2
;
Thanks God we have people like you in the forum that knows better than Oracle knows about Oracle products.Quote:
Originally Posted by ales
Why don't you check Metalink Note #345048.1?... try it, it wouldn't bite you :rolleyes:
Paul, despite of fact that i didn't know the note it says no word about implicit sorting when using group by. It only says it happens, but without order by no gurantee. This is the same the oracle docs says.
Quotes from the said Metalink Note:
Quote:
If no order by clause is specified then the order of the rows retrieved depends on the method used to retrieve the rows from the database. In other words, it depends on the execution plan chosen.
Strange thing. Plain english text and two people understand it in a reverse way. Could you please find the words that mention implicit sorting in group by and post it here? In my understanding, if something just happens it's not very "implicit". At least, I would never rely on it.Quote:
... the group by clause has been enhanced to allow a hash algorithm rather than a sorting algorithm to group the data. Neither of these algorithms guaranteed the order the data would be returned in, the sort algorithm just 'happened' to order it the way wanted by chance.
Try the code I posted earlier and answer yourself if the query returns ouput in the order you expect.
The impertinet irony and rolled eyes are excused.
Arguing with a well known issue documented in Metalink?
Can't believe it. You can't read -or didn't even bother to read - either, huh?
Let me quote from the Metalink note, may be if pre-digested you will be able to see it a little clearly.
"The GROUP BY clause still performs sort operations but in version 10G, new internal Sort algorithms have been introduced which do not guarantee the order of data retrieval and may change the order for returned rows"
"Sort operation used in 9.2.0 optimizer is Sort by group while 10g optimizer is using HASH GROUP BY operation. Each sort algorithm orders data in a different way"
Please stop confusing people. It's Okay to say "I don't know" or "I stand corrected"
Have a nice and productive life.
As documented on Metalink the only cure is to actually add ORDER BY clause to all affected queries.Quote:
Originally Posted by mahajanakhil198
But, "If you have a lot of queries to change, you can use one of the following init/spfile parameters in order to have the same Optimizer behaviour as older versions. But I have to insist that there is no guarantee optimizer will still choose the same sort operation to group the data. Even with these parameters, it's not sure the rows will be retrieved in the same order:
"_gby_hash_aggregation_enabled" = false
OR
optimizer_features_enabled=9.2.0
OR
optimizer_features_enabled=8.1.7"
I'm not suggesting to change your init/spfile initialization parameters, this is an option Oracle brings to the table but I would consider it just a last resort option.
Hope this helps.
No, I don't. That's misunderstanding. What I argue with is this:Quote:
Originally Posted by PAVB
I do insist this is not true. There never has been implicit order by with group by. Of course group by uses internal sorting mechanism and final output may or may not be affected by it, but group by without order by has never guaranted the order of output. The oracle docs says it too and the mentioned Metalink note says it as well:Quote:
Originally Posted by PAVB
Quote:
Applies to:
Oracle Server - Enterprise Edition - Version: 6.0.0.0 to 10.2.0.1
...
The only way to enforce the order of data in a query is to specify an order by clause.
Yes, I understand it well. And what? Do they say there's implicit order by with group by? No. They say group by internally sorts. It sorts differently in 9i and 10g. Do they say group by makes order by for you? No.Quote:
Originally Posted by PAVB
I say you confuse people with the mystical implicit order by.Quote:
Please stop confusing people. It's Okay to say "I don't know" or "I stand corrected"
Please be so kind and launch in 9i the testing code I posted. SQL*Plus does not bite :) believe me or not.
Then, please, post the result of the final query here and comment how it conform with the "implicit order by with group by" idea. Show us how it "sorted the output by the GROUP BY condition even if no ORDER BY was explicitily specified.".
Until you post the result here I find further discussion pointless.
Okay... you asked for it...
:DCode:SQL>
SQL> select version from v$instance;
VERSION
-----------------
9.2.0.4.0
SQL>
SQL> create table the_table (
2 pk1 int,
3 pk2 int,
4 nk1 int not null,
5 nk2 int not null,
6 primary key(pk1,pk2)
7 )
8 ;
Table created.
SQL> create index the_table_idx on the_table(pk1,nk1,pk2,nk2);
Index created.
SQL> insert into the_table values (1, 1, 2, 2 );
1 row created.
SQL> insert into the_table values (2, 1, 1, 1 );
1 row created.
SQL> analyze table the_table compute statistics;
Table analyzed.
SQL>
SQL> explain plan for select nk1, nk2, pk1, pk2, count(*) a_count
from the_table 2
3 group by nk1, nk2, pk1, pk2;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 16 | 1 |
| 1 | SORT GROUP BY NOSORT| | 2 | 16 | 1 |
| 2 | INDEX FULL SCAN | THE_TABLE_IDX | 2 | 16 | 1 |
-----------------------------------------------------------------------
Note: cpu costing is off
10 rows selected.
SQL>
Just wish you do not start arguing about "... BY NOSORT" 'cause that would show you don't understand index pre-sorting either.
Here is a little piece of advice... Rule of Holes #1 states "when you find yourself in a hole the single most important thing you must do is to stop digging" so, stop digging.
Please don't take it personally but I'm done with you, this is the last time I'm answering you.
ROFL :D
You tweaked the script to make it not to show the results! I don't believe my eyes. Where's the final select? You replaced it by explain plan to hide the truth! Who is in the hole then? Stop digging and stop spreading fog!
OK, I launch the whole thing for you, if you're running scared ...
We can see now if the "implicit order by" came into play and sorted the result in the expected order :rolleyes:.Code:SQL>select version from v$instance;
VERSION
-----------------
9.2.0.6.0
SQL>create table the_table (
2 pk1 int,
3 pk2 int,
4 nk1 int not null,
5 nk2 int not null,
6 primary key(pk1,pk2)
7 )
8 /
Table created.
SQL>create index the_table_idx on the_table(pk1,nk1,pk2,nk2)
2 /
Index created.
SQL>insert into the_table values (1, 1, 2, 2 );
1 row created.
SQL>
SQL>insert into the_table values (2, 1, 1, 1 );
1 row created.
SQL>
SQL>analyze table the_table compute statistics;
Table analyzed.
SQL>
SQL>set autotrace on explain
SQL>
SQL>select nk1, nk2, pk1, pk2, count(*) a_count
2 from the_table
3 group by nk1, nk2, pk1, pk2
4 ;
NK1 NK2 PK1 PK2 A_COUNT
---------- ---------- ---------- ---------- ----------
2 2 1 1 1
1 1 2 1 1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=2 Bytes=16)
1 0 SORT (GROUP BY NOSORT) (Cost=1 Card=2 Bytes=16)
2 1 INDEX (FULL SCAN) OF 'THE_TABLE_IDX' (NON-UNIQUE) (Cost=
1 Card=2 Bytes=16)
That's OK, I don't mind it and I don't take it personally. The only thing I regret is the lost time I spent preaching to a deaf guy. I hope somebody else find the thread useful ...Quote:
Originally Posted by PAVB
thanks both of u..
@PAVB---i mentioned earlier that setting optimizer_features_enable alleviated my problem..
I also read abt this parameter "_gby_hash_aggregation_enabled" in metalink but it was not shown by "show parameter _gby_hash_aggregation_enabled" command..
So, for the time being, I m using optimizer_features_enable=9.2.0
Glad it worked out Lucky.
but still i m not able to see the setting of parameter _gby_hash_aggregation_enabled as show parameter does not show anything when show parameter _gby_hash_aggregation_enabled is executed...
can sb tell me how could it be done??
The hidden parameters aren't visible in the v$parameter view and this is why the "show parameter" doesn't work. Try this query, works for me. You have to be logged as sys.
Code:select
a.ksppinm name,
b.ksppstvl value,
b.ksppstdf deflt,
decode
(a.ksppity, 1,
'boolean', 2,
'string', 3,
'number', 4,
'file', a.ksppity) type,
a.ksppdesc description
from
sys.x$ksppi a,
sys.x$ksppcv b
where
a.indx = b.indx
and
a.ksppinm = '¶meter_name'
/
well i got it now..thanks
@ ales and PAVB, i got sth to add to ur discussion..
though its only another discussion forum n not a credible source..
http://www.oracle-base.com/forums/vi...st=0&sk=t&sd=a