Re: Ideas for logicals (index).

[Michael_Schutte@xxxxxxxxxxxx]

have no idea on how to create lf over indexes.   Plus how could an index
advisor suggest anything without any data being in the file?


--

Michael Schutte
Admin Professional



Bob Evans Holiday Farmhouse Feast, Serves 6-8 l $74.99
A complete homestyle meal TO GO, ready to heat at home, serve & enjoy!
Perfect for Thanksgiving, Christmas or holiday entertaining.
For more information, visit www.FarmhouseFeast.com




                                                                       
             Alan Campin                                               
             <alan0307d@gmail.                                         
             com>                                                       To
             Sent by:                  Midrange Systems Technical      
             midrange-l-bounce         Discussion                      
             s@xxxxxxxxxxxx            <midrange-l@xxxxxxxxxxxx>       
                                                                        cc
                                                                       
             11/19/2009 04:45                                      Subject
             PM                        Re: Ideas for logicals (index). 
                                                                       
                                                                       
             Please respond to                                         
             Midrange Systems                                          
                 Technical                                             
                Discussion                                             
             <midrange-l@midra                                         
                 nge.com>                                              
                                                                       
                                                                       




I would suggest using the index adviser. Create the table and then use Ops
Nav to look at what index the system advises as you run the SQL statements.
If possible, create SQL indexes instead of logicals first. Then create
logicals and you will have SQL indexes. Faster.

On Thu, Nov 19, 2009 at 2:34 PM, <Michael_Schutte@xxxxxxxxxxxx> wrote:

> I'm looking for some suggestions.  We're going to be creating a large
> summary file and I'd like to get some ideas on what logical files I
should
> create.  Normally, we fly by the seat of our pants and create them when
> needed. But since this file is going to be large, I'd like to get them
> created beforehand.
>
> Anyway, the file unique key is by Company, Restaurant, Date, Item, Meal
> Period.    I believe SQL will mainly be used to retrieve the data from
this
> file.
>
> I believe one logical should be created by Company and Meal Period, then
> just throw in restaurant, date and item behind it.   I say Company and
meal
> period first because currently there are only 2 possible values for
company
> and 4 possible values for Meal Period.
>
> But then my concern comes in when join in other files.
>
> For example, I may want to get region 1 restaurants only.  So I'm going
to
> need a logical by company and restaurant, but should I throw in the other
> fields too?
>
> Then we might need to join in the Item Master file to select only
> beverages. So a logical by company and item would be needed.
>
> Finally, I think a locigal by  Company, date would be good to have.
>
> I guess I'm all over the place here, not really sure how to explain
myself.
> Does anyone have any suggestions?  I've read for SQL indexes that you
don't
> want to create indexes with the same fields (but in different order)
> because it doesn't really buy you anything (take longer for record to be
> inserted).  Would this be true for logicals too?  What's the harm if we
add
> the other fields anyway, just in case someone in the future wants to do
I/O
> reads on the file or for the group by clause?
>
> I think I've told before that you want to try to create you logicals
where
> the key fields with the least amount of unique values are first.
>
> (Please no DDL solutions, boss doesn't want to start using them yet
because
> not too many of us have experience in them).
>
> --
>
> Michael Schutte
> Admin Professional
>
>
>
> Bob Evans Holiday Farmhouse Feast, Serves 6-8 l $74.99
> A complete homestyle meal TO GO, ready to heat at home, serve & enjoy!
> Perfect for Thanksgiving, Christmas or holiday entertaining.
> For more information, visit www.FarmhouseFeast.com
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.