Re: Retrieval of rows - Is it FIFO?

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 2000/07/04
Message-ID: <962746479.15781.1.pluto.d4ee154e@news.demon.nl>#1/1

Answers embedded...

"Ganesh Vaideeswaran" <divyagan_at_pacbell.net> wrote in message news:39624098.5F51EA8F_at_pacbell.net...
>
> 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)?

Not necessarily

> ================================
>
> 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?

Yes
> Without the ORDER BY, will I get the rows on a FIFO basis.

No, the 'fifo' order applies to initial load only, after that Oracle will fill 'holes' in your data blocks.

> ================================================
>
> 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';
>

The retrieval order will obviously change if your column col1 is indexed. It will not change if the column is not indexed.

Generally speaking you should *not* rely on *any* order of your data, as that is also against relational principles.

---

>
> 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?
>

You should have a primary key on any table, from your post it is impossible to determine what the primary key of your table is. With the little background you provide it is impossible to provide a correct answer as there are too many factors. If your table has a composite primary key of col1, col2, col3, all three columns should be indexed, the most significant column leading. Indexing col2 and col3 separately won't help you much and the index on col3 will probably not be used at all.

---

>
> 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.
>

Please use the explain plan facility to get the the execution plans for your statements. You don't provide any detail about the contents of your table, and you can't expect us to setup a case for you and reproduce your statements.
Using the Cost Based Optimizer your execution path is dependent on the content of your data for 80 to 90 percent.

---

>
> Thanks in advance.
>
> Ganesh - divyagan_at_pacbell.net
>
>
>

Regards,

Sybrand Bakker, Oracle DBA Received on Tue Jul 04 2000 - 00:00:00 CDT