Scalar queries in select clause

From: Gokul <gokulkumar.gopal_at_gmail.com>
Date: Sun, 12 Apr 2009 10:29:18 -0700 (PDT)
Message-ID: <b3870d85-e696-4687-a872-0af4e72db9ba_at_o11g2000yql.googlegroups.com>

I have a bunch of queries that have scalar subqueries in the select clause. From the plan, I see the one with the scalar subqueries has lower cost. Am I missing something ? Is there real benefit in writing queries using scalar subqueries in the select clause ?

SQL> desc t1_test

 Name                                      Null?    Type
 ----------------------------------------- --------


----------------------------

 CODE_VALUE                                         VARCHAR2(100)
 PARAMETER_DESCRIPTION                   VARCHAR2(500)

SQL> desc code_value
 Name                                      Null?    Type
 ----------------------------------------- --------


----------------------------

 CODE_GROUP                                    NOT NULL VARCHAR2(20)
 CODE_SUB_GROUP                            NOT NULL VARCHAR2(100)
 CODE_VALUE                                     NOT NULL VARCHAR2
(4000)
 PARAMETER_DESCRIPTION                                VARCHAR2(250)
 USED                                                  NOT NULL
VARCHAR2(1)
 SEQ_NUM                                           NOT NULL NUMBER(2)
 DATE_CREATED                                                   DATE
 CREATOR_USERID                                               VARCHAR2
(20)
 DATE_MODIFIED                                                  DATE
 MODIFIER_USERID                                              VARCHAR2

(20)

SQL> select count(*) from t1_test
  2 /

  COUNT(*)

---

     87780

SQL> select count(*) from code_value
  2 /

  COUNT(*)

---

      2272

SQL> select index_name,column_name,column_position from all_ind_columns where table_name='CODE_VALUE'   2 /

INDEX_NAME

COLUMN_NAME                              COLUMN_POSITION


----------------------------------------

---------------------------------------- ---------------

PK_CODE_VALUE
CODE_GROUP                                            1
PK_CODE_VALUE
CODE_SUB_GROUP                                    2
PK_CODE_VALUE
CODE_VALUE                                             3

SQL> select t1.code_value,(select c1.parameter_description from code_value c1 where t1.code_value=c1.code_value) description   2 from t1_test t1;

Execution Plan

---

Plan hash value: 2452051333

---

| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |


--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |            | 87780 |   342K|   110   (2)|
00:00:02 |
|*  1 |  TABLE ACCESS FULL| CODE_VALUE |     2 |    74 |    13   (0)|

00:00:01 |
| 2 | TABLE ACCESS FULL| T1_TEST | 87780 | 342K| 110 (2)| 00:00:02 |

---

Predicate Information (identified by operation id):

---

   1 - filter("C1"."CODE_VALUE"=:B1)

SQL> select t1.code_value,c1.parameter_description   2 from t1_test t1,code_value c1
  3 where t1.code_value = c1.code_value;

Execution Plan

---

Plan hash value: 1654335201

---

| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |


---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |            |   181K|  7285K|   126   (4)|
00:00:02 |
|*  1 |  HASH JOIN         |            |   181K|  7285K|   126   (4)|

00:00:02 |
| 2 | TABLE ACCESS FULL| CODE_VALUE | 2272 | 84064 | 14 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1_TEST | 87780 | 342K| 110 (2)| 00:00:02 |

---

Predicate Information (identified by operation id):

---

   1 - access("T1"."CODE_VALUE"="C1"."CODE_VALUE")

SQL> Rgds,
Gokul Received on Sun Apr 12 2009 - 12:29:18 CDT