Select *

Often, it is necessary to select columns from either views or tables when cursors are written inside the PLSQL logic. One option would be to do a SELECT * instead of selecting necessary columns in the SELECT list. This may introduce a performance bottleneck which is discussed through the examples below.

Select * from Views

Views may contain embedded method calls as columns in the SELECT list. Often, all columns defined in the view is not required for the method implementation, rather it is a subset of columns. If a SELECT * is done in a cursor then, there will be a context switch between SQL, PLSQL engines for each column with a method call. Additionally oracle need to parse all the columns used in the cursor. It is always better to select only the required columns in the SELECT list.

Below is an extract from an ordinary view definition in ORDER. Note the columns with comments Method call n. If a select * is performed, oracle has to do extra context switches for each of the method calls . As in the example below, most likely, it is not necessary to fetch client values from the view to perform server logic.

CREATE OR REPLACE VIEW CUSTOMER_ORDER_PICK_LIST AS
SELECT
    pick_list_no                                                  pick_list_no,
    order_no                                                      order_no,
    create_date                                                   create_date,
    Pick_List_Printed_API.Decode(printed_flag)                    printed_flag, -- Method call 1
    printed_flag                                                  printed_flag_db,
    Picking_Confirmed_API.Decode(picking_confirmed)               picking_confirmed, -- Method call 2
    picking_confirmed                                             picking_confirmed_db,
    source                                                        source,
    Consolidated_Pick_List_API.Decode(consolidated_flag)          consolidated_flag, -- Method call 3
    consolidated_flag                                             consolidated_flag_db,
    consolidated_orders                                           consolidated_orders,
    contract                                                      contract,
    sel_order                                                     sel_order,
    sel_customer                                                  sel_customer,
    sel_route                                                     sel_route,
    sel_ship_period                                               sel_ship_period,
    sel_forward_agent                                             sel_forward_agent,
    sel_location_group                                            sel_location_group,
    Pick_List_Consolidation_API.Decode(consolidation)             consolidation, -- Method call 4
    consolidation                                                 consolidation_db,
    Pick_Inventory_Type_API.Decode(pick_inventory_type)           pick_inventory_type, -- Method call 5
    pick_inventory_type                                           pick_inventory_type_db,
    sel_due_date                                                  sel_due_date,
    Consol_Pick_Incl_Cust_Ord_API.Decode(sel_include_cust_orders) sel_include_cust_orders, -- Method call 6
    sel_include_cust_orders                                       sel_include_cust_orders_db,
    rowkey                                                        objkey,
    to_char(rowversion,'YYYYMMDDHH24MISS')                        objversion,
    rowid                                                         objid
FROM customer_order_pick_list_tab
WHERE (contract IS NULL OR EXISTS (SELECT 1 FROM user_allowed_site_pub WHERE customer_order_pick_list_tab.contract = site))
WITH READ ONLY;

Select * from Tables

There are scenarios where all columns in the table are selected unnecessary for a particular row even though only a few columns are accessed. Because of this, there could be additional table/full_table scans instead of index_range/index_unique /Index full scans to retrieve these unwanted values. Therefore, the clever way is to retrieve only the required columns instead of SELECT *.

Example

PROCEDURE New_Note_Text_All (
   note_text_ IN OUT VARCHAR2,
   note_id_ IN NUMBER,
   output_type_ IN VARCHAR2 )
IS
   CURSOR source IS
      SELECT *
      FROM document_text_tab
      WHERE note_id     = note_id_
      AND   output_type = output_type_;
BEGIN
   OPEN source;
   FETCH source INTO test_rec_;
   IF source%FOUND THEN
      DO_SOMETING;
   ELSE
      DO_SOMETING;
   END IF;
END New_Note_Text_All;

Solution

The purpose of this cursor is to check the availability of the record. So, it's not needed to do a SELECT * from the table. Instead, use SELECT 1.

PROCEDURE New_Note_Text_One (
   note_text_ IN OUT VARCHAR2,
   note_id_ IN NUMBER,
   output_type_ IN VARCHAR2 )
IS
   CURSOR source IS
      SELECT 1
      FROM document_text_tab
      WHERE note_id     = note_id_
      AND   output_type = output_type_;
BEGIN
   OPEN source;
   FETCH source INTO test_rec_;
   IF source%FOUND THEN
      DO_SOMETING;
   ELSE
      DO_SOMETING;
   END IF;
END New_Note_Text_One;