Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Retrieval of rows - Is it FIFO?

Re: Retrieval of rows - Is it FIFO?

From: root <root_at_orbmedia.com>
Date: 2000/07/05
Message-ID: <3965b730.110389703@news.supernews.com>#1/1

True, do not rely on row orders in any RDBMS! I am currently working on a project where an entire application miraculously ran for years depending on insert orders, but as soon as some stuff was indexed the application broke all over. It may appear to work but it is purely a coincidence *never rely on it*.

On Tue, 4 Jul 2000 23:36:33 +0200, "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:

>
>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 Wed Jul 05 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US