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

[IQSChris]

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

 

[RiverGuy]

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.

 

[IQSChris]

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

 

[SQLSister]

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

 

[Golom]

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.

 

[johnherman]

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

 

[IQSChris]

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