Performance problem: the difference between select s. * vs select *

Question

Recently, I had some problem fulfilling my request. The following is described here: poor Hibernate chooses performance over starting directly - how to debug? After a long time of struggle, I finally found that the request with the selected prefix, for example:

SELECT sth.* FROM Something as sth... 

300 times slower than the request started as follows:

  SELECT * FROM Something as sth.. 

Can someone help me and answer why so? Some external documents on this subject will be really helpful.

Table used for testing:

Table

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

SALES_UNIT_RELATION contains the PARENT-CHILD relationship between units of a sales unit. It consists of SALES_UNIT_PARENT_ID, SALES_UNIT_CHILD_ID and VALID_TO_DTTM / VALID_FROM_DTTM. There is no connection with any tables. PK here: .. PARENT_ID, .. CHILD_ID and VALID_FROM_DTTM

The actual queries that I 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 

The same query, both use the left join, and only the difference with the choice.

Answer 1

they are, of course, two different questions. CAN plan will change, but choice will be different. i.e. in sth. * he can choose a full / fast full index scan on the left merged table. whereas in the first case it may be a full table scan.

to help you, can we see the plans please? it is advisable to 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 plan of explanation, do you see theres CARDINALITY = 1 at every step? You collected statistics 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 this 200314 IO and it took a few seconds. Also see ROWS = 1 at each step (i.e. full scan) .. allows you to collect statistics:

 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 try again: 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 only 2537, and the plan shows the correct rows ROWS and HASH (better for our needs). my test patterns are probably less than your real ones, so the timings are closer