Ok to use a DDS logical in an SQL statement in this scenario?

[Vectorspace]

My understanding has always been that we should not use DDS logicals in SQL statements, because it means the SQL engine has less scope for optimising the access.

Is that still the case?

And what if the logical has Select/Omit rules?

I now need to write a query to pull data from a join of 2 tables. On one of those tables, I want to limit the join to certain types of record. E.g.

select * from TableA a
left join TableB b
on a.key = b.key
and b.type = 'S' // This is the record type

TableB already has a logical that uses a Select rule to filter for just the desired type. If I join the physical and have the type filter in the SQL like this, then I now have that type filtering logic in 2 places - once in the DB and once in my program. Or I could join the logical and not need to check type in the SQL like this:

select * from TableA a
left join TableB_L1 b // This is the logical that filters for record type s
on a.key = b.key

What should I do?

[B.Hauser]

When using a logical file in a SELECT Statement the query optimizer has to rewrite the SELECT statement based on the physical files. It has to look in the DDS descritptions, takes from the DDS descriptiong and retrieves the Column List, the JOIN clauses and the SELECT/OMIT clauses and rewrites the SQL Statement based on this informantion.
After it starts with the optimization and evaluates all existing access path (in DDS described files and SQL indexes). It is always the query optimizer who decides if an access path (Indexs / logical file) is used and which access path is used.
The decision made is also based on the data composition and not all index access is good and not all table scan is bad.

[Vectorspace]

Very good to know, thanks

[TedHolt]

I take a different approach to writing queries.

You don’t say what kind of data this is or what a type S means, so let’s say that it’s employee data and that type S means the employee is salaried.

In such an environment, I might build various views and/or logical files for the different types of employees -- salaried, hourly, part-time, temporary, whatever.

When it comes time to query the salaried employees, I wouldn’t think, “What’s the best way to write this query so that it will perform well?” Instead I would think “Where’s the data?” and I would query the salaried employees view.

It is the optimizer’s job to find an efficient way to retrieve the data. It is my job to write code that human beings can read, understand, and modify. I think this:

select . . . from Salaried_employees

is more understandable than this:

select . . . from Employees where type = ‘S’

Only if the query did not perform well would I try to improve performance. The index advisor usually tells me what I need to know.

[Vectorspace]

Totally understand and had the logical been an SQL View instead, I would have used the view and not given it a second thought. It's only because I had heard long ago that logicals should not be referenced in SQL queries that I felt I should ask for more clarity.