×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Index is to Table as Key is to Logical File?
4

Index is to Table as Key is to Logical File?

Index is to Table as Key is to Logical File?

(OP)
Hello,
I'm not sure exactly where to post this in tek-tips...

Coming from an AS400 reporting environment against a JD Edwards ERP system - The ERP system had physical files(PF) and logical files(LF).  A single PF could have multiple LF's, each with different Key fields.  K01 identifies the first field that is sorted, K02 is the second subsort, K03 the third...and so on. I could choose the appropriate LF based on a reports selection (where clause).  Usually it would improve a reports performance by matching up the appropriate LF that sorted by my selection criteria.

Now I'm in a SQL Server/Oracle environment. The tables have indexes, but appear different than Key fields.
Does the equivalent of LF's exist in the SQL Server/Oracle environment?
Could anyone help me to understand the difference?
Thank You,
Chris

RE: Index is to Table as Key is to Logical File?

I don't work with Oracle, but I work with SQL Server.  SQL Server has indexes which can be put on tables, and the database engine will automatically try to use the correct index (although you can provide index hints as well).  So it would be analogous to doing a select statement on the physical file, and the correct index would be used without have to use a different Table/View/Logical File name in the From clause.

SQL Server also has Indexed Views which are Views that can be selected on directly by the name of the view itself and they contain their own indexes.  I would relate these closely to Logical Files.  Although from my experience in the SQL Server world, regular indexes on tables are used far more often than Indexed Views.     

RE: Index is to Table as Key is to Logical File?

(OP)
Thank you RiverGuy.  

That information helps me to better understand how SQL works.  

Do you know if regular views take advantage of the indexes on the base table/s?  In other words, if I use a regular view in a report, and the report has selection criteria...will the index of the base table/s be utilized and possibly improve the speed of record retrieval?

Thanks,
Chris

RE: Index is to Table as Key is to Logical File?

You can check out the execution plan to see whether it is using the indexes. With views, you may also want to index the view to improve performace.

"NOTHING is more important in a database than integrity." ESquared
 

RE: Index is to Table as Key is to Logical File?

Technically I suppose Indexes and Keys are different creatures.

In the world of absolutely kosher SQL, a key is something that is used to describe relations in an RDBMS. Thus we have Primary, Foreign and Candidate keys ... all describing some logical aspect of the table(s). Ideally SQL supports a way to manipulate data at a logical level and not a physical one ... a practice more honored in the breach than the observance in most SQL implementations ... (to badly paraphrase Hamlet.)

In contrast, Indexes are a mechanism to describe orderings of data. Pure ANSI SQL has no notion of indexes, leaving that as an implementation detail at the physical level of the database.

Meanwhile ... back in the real world ...

Most DBMSs will index fields that are key fields on the assumption that, if it is a key then you probably want to be able to search efficiently for those key values. The discussions from RiverGuy and SQLSister focus on indexes because they are much more issues for the working programmer. The inner mysteries of establishing keys and relationships goes on (or should go on) at the database design level.

RE: Index is to Table as Key is to Logical File?

An index is provided to accelerate access of data for queries which use the data from the column(s) which comprise the index. Often indexes are across several columns in order to provide a greater degree of uniqueness (b-tree, b-treive). Or they can be bitmap indexes which are useful when there are considerable more repetition of values, like State Codes.

A key is more of a logical concept.  Examples include surrogate (or synthetic) keys, which are generated to support database activities and unknown to the end user. Another key is the natural key - this is the key best known to the end user, for instance, order or invoice number. Natural keys might or might not be unique. In addition, there are foreign keys. These are column(s) which provide lookup to other tables, usually reference tables. For instance, customer bill-to and customer ship-to are two different foreign keys in an order table, although both the customer bill-to and customer ship-to might point to the same reference table, Customer Address.

Hope this helps.  

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

RE: Index is to Table as Key is to Logical File?

(OP)
Thank you all for your valuable input.  Indexes are becoming clearer now and I feel more confident that I can understand their impact to report performance.

Thanks Again,
Chris

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close