Interesting Behaviour of the Index, Order changed in Table...

[Oracledba8]

Hi Guys,

I have a table which contains a Column as Entrry date and this table have values as Order by that date, When I create an index on a Column, This Disturbs the Order in Totally different way, Does any one know WHY is This.. Even it is not desired or required. Actually I shold say it Changes the Order of the Entries in the Table.

Here is What I have..
SQL> select media_id, seq_no, entered_on_date from medloc
2 where media_id = 100;

MEDIA_ID SEQ_NO ENTERED_O
---------- ---------- ---------
100 26-SEP-95
100 27-SEP-95
100 27-SEP-95
100 25-FEB-98
100 21-APR-99

5 rows selected.

SQL> create index idx on medloc(media_id);

Index created.

SQL> select media_id, seq_no, entered_on_date from medloc
2 where media_id = 100;

MEDIA_ID SEQ_NO ENTERED_O
---------- ---------- ---------
100 25-FEB-98
100 21-APR-99
100 26-SEP-95
100 27-SEP-95
100 27-SEP-95

5 rows selected.

SQL> drop index idx;

Index dropped.

SQL> select media_id, seq_no, entered_on_date from medloc
2 where media_id = 100;

MEDIA_ID SEQ_NO ENTERED_O
---------- ---------- ---------
100 26-SEP-95
100 27-SEP-95
100 27-SEP-95
100 25-FEB-98
100 21-APR-99

5 rows selected.

If You see the Entered_on_date Columns has to be in the date Order, This way I am getting the Seq_No, But I need the Index to speed up the Update...

Can any one please Comment, Why does it change the Order of the data in the table.

Thanks in Advance.

[vbaskar]

Hi

I am not 2 sure !! but i could immd. think of this...without an index the data is shown as IT IS ENTERED OR AS IT ENTERED TO THE DATABASE...Am i right !! fine !! in that case if u create an index..obv. the DATA order Changes in the index table..letz forget whether it uses b Tree or other index..obv the order changes..when u write a select statement and using the index the data is re-arranged as per the index..

i donn know whether does this make much sense..but this is something which worth considering..

Thankx

[jmodic]

Your query without index returns rows sorted by entered_on_date column, even though you haven't specify any ORDER BY. This is obviously what you wish to have. Well, don't rely on this behaviour in the future, with or without the index. There is no way to be cartain in which order you'll get the resultset back unless you don't user explicit ORDER BY.

The fact that you are getting results in the desired order now is more or less pure luck. Ufter some deletes, inserts and updates you might get results in totally different order, no matter if you have index or not. So if you want to get results sorted always use ORDER BY and you'll get what you expect with or without index.

select media_id, seq_no, entered_on_date from medloc
where media_id = 100
ORDER BY entered_on_date;

BTW, creating the index could not possibly change the order of rows stored in the table blocks. It merely change the execution path of the query which may result in the different order in which records are returned.

[Edited by jmodic on 04-21-2001 at 05:11 AM]

[Oracledba8]

Thanks Jurij and Vijay,

my question is still there. I know I could use Order by Clause, But I have to get the seq_no for every media_id block depending on the date, For media_id= 100, The seq_no will be from 1 to 5 as there are 5 entries of media_id 100. If the index chnages the Data inside, Iwill get the wrong seq_no and that's what I don't want to do.

Is there a way round? I have the Procedure that will update it, but the Problem comes after having the Index and I definitely need the Index as there are about 800000 records to update with diferent of about 257000 Media_Id's.

Any suggestion, please.

Thanks

[kmesser]

What's wrong with jmodic's solution? You want the data ordered by date, and his query does that! The index doesn't change the table data at all. As stated earlier in this thread, you can't count on ANY sequence in the returned data unless you explicitly control it through an order by clause.

[Oracledba8]

Kmesser,

I suppose You have not read what I said. The example is in front of you.

As soon as I Create Index, The data is shuffled around with out any Order. Before Creating the Index, The data was in the Order of Entered_on_Date and Once I create the Index, The data was ordered randomely and even to prove if I drop the Index, The data again went back to the Original Form.

[jmodic]

With all due respect, it's not kmesser who is not reading the therad, it's you! Everybody is telling you that you can't count on any order in which data is returned unless you use ORDER BY. Period. With or without the index. If you get the data in a specific order today, it is not neccessary you'll get them in the same order tomorow if you do not use ORDER BY.

Besides, it is not clear what you mean by your SEQ_NO. It is in your select statement, but I can't see it anywhere in your results. If you want to get results ordered based on date and this seq_no, then use them both in your ORDER BY.

If you still don't belive anyone of us, then stick with your query without the ORDER BY and prevent the index to be used in select by aplying a dummy opreation on column MEDIA_ID:

select media_id, seq_no, entered_on_date from medloc where media_id+0 = 100;

But don't be surprised by the order of the results someday...

[kmesser]

OracleDBA8:

If my post offended you, I apologize. My understanding of the problem you stated is:

1. The data will always be entered in the same order as the entered_on_date field;

2. You have noticed that so far, when no index exists, a query returns the data in the correct order;

3. You hope to rely on the query always returning the result set in the desired order, without explicitly stating this in an "order by" clause;

4. Even if you do create an index, you do not want to use an "order by" clause;

5. You have noticed that when you do create an index, if you subsequently query and do not specify an order, the result set is not in the same order as the un-indexed query, and this led you to believe the sequence of rows in the table was altered by the creation of an index. However, that theory was disproven when you dropped the index and subsequent queries returned the data in the original sequence - unless you also believe that dropping the index also reorders the rows of data . . .


The correct way to approach this is to force Oracle to return your data in the desired sequence. Any other approach will almost certainly result in some other return sequence eventually. If you delete any rows out of this table, then insert more rows, the sequence will change.

If you post your motives for attempting to avoid the order by clause, there may be ways to alleviate those concerns also.

[padmam]

Hi ORACLEDBA, 22nd April 2001 12:39 hrs chennai

I have tried with your tables like simulation.

I dont find the order of fetching changing.
here is my Analysis.

==========
I) create table medloc(media_id number(10),seq_no number(10), entered_on_date date);

II)12:32:52 SQL> select * from medloc;

MEDIA_ID SEQ_NO ENTERED_O
---------- ---------- ---------
100 26-SEP-95
100 27-SEP-95
100 27-SEP-95
100 25-FEB-98
100 21-APR-99

III)12:33:02 SQL> create index idx on medloc(media_id);

IV)12:33:28 SQL> select * from medloc;

MEDIA_ID SEQ_NO ENTERED_O
---------- ---------- ---------
100 26-SEP-95
100 27-SEP-95
100 27-SEP-95
100 25-FEB-98
100 21-APR-99
V)12:38:22 SQL> drop index idx;

12:41:40 SQL> select media_id, seq_no, entered_on_date from medloc
12:44:59 2 where media_id = 100;

MEDIA_ID SEQ_NO ENTERED_O
---------- ---------- ---------
100 26-SEP-95
100 27-SEP-95
100 27-SEP-95
100 25-FEB-98
100 21-APR-99
========================
I have tried with media_id as varchar2 Data Type also in another table ||r to medloc as medloc1.Still i dont find any order changed.

As JMODIC as asked can you please define what is SEQ_NO ? its data type and more information on the table structure etc will be appreciated.

Cheers

Padmam



[Edited by padmam on 04-22-2001 at 04:30 AM]

[padmam]

--------------

[Edited by padmam on 04-22-2001 at 11:19 AM]