performance issue: difference between select s.* vs select *

Question

Recently I had some problem in performance of my query. The thing is described here: poor Hibernate select performance comparing to running directly - how debug? After long time of struggling, I've finally discovered that the query with select prefix like:

    SELECT sth.* FROM Something as sth...

Is 300x times slower then query started this way:

    SELECT * FROM Something as sth..

Could somebody help me, and answer why is that so? Some external documents on this would be really useful.

The table used for testing was:

SALES_UNIT table contains some basic info abot sales unit node such as name and etc. The only association is to table SALES_UNIT_TYPE, as ManyToOne. The primary key is ID and field VALID_FROM_DTTM which is date.

SALES_UNIT_RELATION contains relation PARENT-CHILD between sales unit nodes. Consists of SALES_UNIT_PARENT_ID, SALES_UNIT_CHILD_ID and VALID_TO_DTTM/VALID_FROM_DTTM. No association with any tables. The PK here is ..PARENT_ID, ..CHILD_ID and VALID_FROM_DTTM

The actual queries I've used were:

    SELECT s.* 
    FROM   sales_unit s LEFT JOIN sales_unit_relation r 
               on (s.sales_unit_id = r.sales_unit_child_id) 
    WHERE  r.sales_unit_child_id IS NULL

    SELECT  * 
    FROM    sales_unit s LEFT JOIN sales_unit_relation r 
               on (s.sales_unit_id = r.sales_unit_child_id) 
    WHERE   r.sales_unit_child_id  IS NULL

Same query, both uses left join and only difference is with select.

@kamil, you should consider editing your question to include the full SQL. I think the fact that two tables are involved (via the LEFT JOIN) is likely a critical component of this question. — kaliatech, Nov 15 '12 at 14:06

DazzaL · Accepted Answer · 2012-11-15T16:57:53.993

they are two different queries of course. the plan CAN change with the selects being different. i.e. in the sth.* it may be choosing a full/fast full index scan on the left joined table. whereas on the first it will possibly be a full table scan.

in order to help you further, can we see the plans please? preferably do this in SQL*PLUS

set timing on
set autotrace on traceonly

select s.* from sales_unit s left join sales_unit_relation r on (s.sales_unit_id = r.sales_unit_child_id) where r.sales_unit_child_id is null;

select * from sales_unit s left join sales_unit_relation r on (s.sales_unit_id = r.sales_unit_child_id) where r.sales_unit_child_id is null;

EDIT

given your explain plan, you see theres CARDINALITY=1 on every step? you've gathered stats when the tables were empty! see this:

SQL> select s.* from sales_unit s left join sales_unit_relation r on (s.sales_unit_id = r.child_sales_unit_id) where r.child_sales_unit_id is null;

no rows selected

Elapsed: 00:00:03.19

Execution Plan
----------------------------------------------------------
Plan hash value: 1064670292

------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |    48 |    27  (86)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |               |     1 |    48 |    27  (86)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| SALES_UNIT    |     1 |    35 |     2   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | SALES_REL_IX1 |     1 |    13 |    25  (92)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("S"."SALES_UNIT_ID"="R"."CHILD_SALES_UNIT_ID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     200314  consistent gets
       2220  physical reads
          0  redo size
        297  bytes sent via SQL*Net to client
        339  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

so see it used 200314 IO and took a few seconds. Also see ROWS = 1 on every step (i.e the full scans)..lets gather stats:

SQL> begin dbms_stats.gather_table_stats(user, 'SALES_UNIT', degree=>8, cascade=>true); end;
  2  /

PL/SQL procedure successfully completed.

SQL> begin dbms_stats.gather_table_stats(user, 'SALES_UNIT_RELATION', degree=>8, cascade=>true); end;
  2  /

PL/SQL procedure successfully completed.

and now rerun: SQL> select s.* from sales_unit s left join sales_unit_relation r on (s.sales_unit_id = r.child_sales_unit_id) where r.child_sales_unit_id is null;

no rows selected

Elapsed: 00:00:00.84

Execution Plan
----------------------------------------------------------
Plan hash value: 2005864719

-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |   912 | 18240 |       |  1659   (3)| 00:00:20 |
|*  1 |  HASH JOIN ANTI       |               |   912 | 18240 |  2656K|  1659   (3)| 00:00:20 |
|   2 |   TABLE ACCESS FULL   | SALES_UNIT    |   100K|  1472K|       |    88   (3)| 00:00:02 |
|   3 |   INDEX FAST FULL SCAN| SALES_REL_IX1 |   991K|  4841K|       |   618   (3)| 00:00:08 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("S"."SALES_UNIT_ID"="R"."CHILD_SALES_UNIT_ID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2537  consistent gets
          0  physical reads
          0  redo size
        297  bytes sent via SQL*Net to client
        339  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>

now we used 2537 gets only and the plan shows the right ROWS and a HASH join (better for our needs). my test tables are probably smaller than your real ones which is why the timings are closer

I had some real problems with sql plus. Granting plustrace and plan_tables didn't worked so I couldn't ran this statements. I tried to do "Explain" from Navicat, which gave me this results: http://i49.tinypic.com/6f2pab.png hope it helps with an answer. First is with `select s.*` second is `select *` — kamil, Nov 15 '12 at 15:45
cheers. you see the problem in those plans right? CARDINALITY=1 on every row. that probably means that you gathered stats when the table was empty. whats the real size of the tables. im assuming much much larger. regather stats on them both and rerun the plans. — DazzaL, Nov 15 '12 at 15:55
THat's really odd. Tables are not empty, guess it's Navicat fault, i'll try to ran somewhere else — kamil, Nov 15 '12 at 16:07
no you have to gather stats..ie something like `begin dbms_stats.gather_table_stats(user, 'SALES_UNIT', degree=>8, cascade=>true); end;` (this is a basic gather..no histograms) same for SALES_UNIT_RELATION. its not a navigat fault, as its oracle that's picking the plan. a bad one due the wrong stats you've set. — DazzaL, Nov 15 '12 at 16:45
Thanks for the explanation. here's valid (I think so) plan for sql statements: http://i50.tinypic.com/169ou8p.png — kamil, Nov 15 '12 at 17:34
ok, how do the statements perform now? as both look like the "good" one did before. — DazzaL, Nov 15 '12 at 17:47
nothing changed. still "the good one" is fast like 100ms, while "the slow one" is 20-30seconds — kamil, Nov 15 '12 at 18:02

performance issue: difference between select s.* vs select *

1 Answers1

Linked