Difference between select * from Table & select individual columns

Analytics
Enthusiast

Difference between select * from Table & select individual columns

Hello all,
I am having trouble understanding difference select * from TABLE X or select a , b, c ( columns) from table X ( same table ) above.
I was wondering if someone actually knows what is the difference at the Teradata DB Architecture level.

Thanks,
Aditi
2 REPLIES
Enthusiast

Re: Difference between select * from Table & select individual columns

Well, it helps Teradata when you enslist colum names in the query. By using '*' you add one more stage and asking Teradata to resolve that * first, and then bring the data.

By using specific colum name, Teradata tend to bring data faster, and ensures you are getting data for colums you really need. It is always recommended to use colum names instead of 'SEL * FROM TABLE_NAME;'.

Re: Difference between select * from Table & select individual columns

Hi

There won't be any difference in the execution plan for both the queries .i.e. Sel * From Tbl and Sel F1, F2 ( all the fields ) From Tbl. The main difference between the above two query processing will be in the Parsing Stage or when the Parser handles the query ( which would be prior to the generation of the query plan).

In the Case of Sel * From Tbl query, The Parser will perform following operations
1. Query Validation which involve:
a. Check the DD tables for the access
b. Check the DD for the obtaining the Database details, table details ( to obtain the field name)
c. Crosscheck the details mentioned in the query with the obtained details from the DD.
In this case it need to check only the Table name and the DB.
2. Completed the query - which will be given to the Optimizer to determine the query plan.

Similarly for the query Sel F1, F2 From Tbl (Assuming that there is only 2 fields in the Tbl) The Parser have to perform the following operations.
1. Query Validation which involve:
a. Check the DD tables for the access
b. Check the DD for the obtaining the Database details, table details ( to obtain the field name)
c. Crosscheck the details mentioned in the query with the obtained details from the DD.
In this case it need to check only the Table name,DB and Fields which is given in the query.
2. Completed the query - which will be given to the Optimizer to determine the query plan.

Since the difference between the query execution persists before the generation of Query plan, functionally there is no major difference.

I hope i had answered your question.

Thank You
Visakh Chandran