Retrieval of rows - Is it FIFO?

From: Ganesh Vaideeswaran <divyagan_at_pacbell.net>
Date: 2000/07/04
Message-ID: <39624098.5F51EA8F@pacbell.net>#1/1

Hi all,

I am wondering if Oracle returns rows back during retrieval of rows on a

FIFO basis.
What I mean by this is -

Does Oracle return the first row I inserted as the first row I retrieve(assuming that
there are no WHERE clauses)?

---

We have a workflow system where we want to retrieve rows based on a TimeStamp.
At present, we do this is using a SELECT statment such as -

SELECT * FROM wfTable WHERE TS=(Select MIN(TS) FROM WF);

TS ==> Name of TimeStamp column.

The other alternative is to use a SELECT statement such as -

SELECT * FROM wfTable ORDER by TS;

Now, which is better? Should I have an index on the TS column? Without the ORDER BY, will I get the rows on a FIFO basis.

---

If I am guaranteed to get the rows on a FIFO basis, will the retrieval order
change if I have a WHERE clause such as -

SELECT * FROM wfTable WHERE col1='yyyy';

---

Suppose I retrieve rows from a table based on 3 columns, should I have a composite index on all 3 columns, or should I have one index for each column, or should I just have just one index that will weed out

the
most rows.

For example,

SELECT * FROM wfTable where col1=10 and col2='rrr' and col3=35;

I can eliminate 75% of the rows based on col1, 50% based on col2 and 25%

based on col3. So, what should my index be like?

---

And one last question ...

I have a col4(an integer) in the wfTable that can take a value of 1 thro' 10.

If I have to SELECT from the table based on certain values for col4, which is a better
way to write the statement...

SELECT * from wfTable where col1=10 and col2='rrr' and col3=35 and (col4

!= 1
AND col4 != 5 AND col4 != 6 AND col4 != 8 AND col4 != 10);

OR

SELECT * from wfTable where col1=10 and col2='rrr' and col3=35 and (col4

• 2 OR col4 = 3 OR col4 = 5 OR col4 = 7 OR col4 = 9);

OR

SELECT * from wfTable where col1=10 and col2='rrr' and col3=35 and col4 NOT IN
(1, 5 , 6, 8, 10);

OR

SELECT * from wfTable where col1=10 and col2='rrr' and col3=35 and col4 IN
(2, 3 , 4, 7, 9);

And should I include col4 as part of a composite index based on col1, col2 and col3.

---

Thanks in advance.

Ganesh - divyagan_at_pacbell.net Received on Tue Jul 04 2000 - 00:00:00 CDT