Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    14

    Unanswered: Performance Question : Please help!

    Which of the following queries will be faster in oracle ? Any ideas/help ?

    1. select * from table_name
    2. select col1, col2, col3,.. from table_name

    Assume the following :
    1. There is no where clause in both the queries
    2. There are no indexes/keys in the table
    3. For the second query all the columns are selected from the table.
    4. The above queries will be executed from a java application connected to the database via Oracle JDBC Driver (classes12.zip)

    thanks,
    saravanan

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's what TKPROF found out:
    Code:
    select * from my_table
    
    
    call        count       cpu    elapsed     disk    query current        rows
    --------  -------  --------  --------- -------- -------- -------  ----------
    Parse           1      0.01       0.02        0        0       0           0
    Execute         1      0.00       0.00        0        0       0           0
    Fetch         127      0.10       0.10        0      145       3        1890
    
    --------  -------  --------  --------- -------- -------- -------  ----------
    total         129      0.11       0.12        0      145       3        1890
    
    Misses in library cache during parse: 1
    Optimizer hint: CHOOSE
    Parsing user id: 26  (SQL)
    
    Rows     Execution Plan
    -------  ---------------------------------------------------
          0  SELECT STATEMENT   OPTIMIZER HINT: CHOOSE
       1890    TABLE ACCESS (FULL) OF 'MY_TABLE'
    Code:
    select col1, col2, col3, col4, col5, col6, col7 from my_table
    
    call        count       cpu    elapsed     disk    query current        rows
    --------  -------  --------  --------- -------- -------- -------  ----------
    Parse           1      0.01       0.00        0        0       0           0
    Execute         1      0.00       0.00        0        0       0           0
    Fetch         127      0.09       0.32       20      145       3        1890
    
    --------  -------  --------  --------- -------- -------- -------  ----------
    total         129      0.10       0.32       20      145       3        1890
    
    Misses in library cache during parse: 1
    Optimizer hint: CHOOSE
    Parsing user id: 26  (SQL)
    
    Rows     Execution Plan
    -------  ---------------------------------------------------
          0  SELECT STATEMENT   OPTIMIZER HINT: CHOOSE
       1890    TABLE ACCESS (FULL) OF 'MY_TABLE'
    Table counts 1890 rows (which is nothing much); I don't see any significant differences. "Elapsed" time differs, but this might be because of different system conditions when those queries were executed (there were ~80 users working at the time). Execution plan is the same so - I'd say that it doesn't matter if you select <*> or <column_list> in such a query.

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Its only going to make a difference if you have a very large table. For example we had a DW query selecting hundreds of millions of rows and by reducing the number of columns returned we cut the query time from 12 hours to 7 hours as the network bandwidth was the bottleneck.

    As always try it out in your particular environment to see if it really does make a difference.

    Alan

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by asjsaravanan
    3. For the second query all the columns are selected from the table.
    In other words, the queries are logically identical, and will be optimized identically. The only teeny-weeny bit of overhead (perhaps) is that for "select *", the parser has to work out the list of columns to include; OTOH, with "select col1, col2, ..." the parser has to validate all those columns against the data dictionary anyway, so it is probably about even either way.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •