Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / SQL PLUS Session Environment

 

Analyze table compute statistics

SQL> SQL> create table myTable1 as select * from all_objects where rownum < 50; Table created. SQL> create table myTable2 as select * from all_objects where rownum <= 50; Table created. SQL> SQL> alter table myTable1 add constraint myTable1_pk primary key(object_id); Table altered. SQL> alter table myTable2 add constraint myTable2_pk primary key(object_id); Table altered. SQL> SQL> analyze table myTable1 compute statistics   2  for table for all indexes for all indexed columns; Table analyzed. SQL> SQL> analyze table myTable2 compute statistics   2  for table for all indexes for all indexed columns; Table analyzed. SQL> SQL> create or replace function get_data( p_object_id in number ) return varchar2   2  is   3      l_object_name myTable2.object_name%type;   4  begin   5      select object_name into l_object_name   6        from myTable2   7       where object_id = p_object_id;   8      return l_object_name;   9  exception  10      when no_data_found then  11          return NULL;  12  end;  13  / Function created. SQL> SQL> select a.object_id, a.object_name oname1, b.object_name oname2   2    from myTable1 a, myTable2 b   3   where a.object_id = b.object_id(+);  OBJECT_ID ONAME1                         ONAME2 ---------- ------------------------------ ------------------------------         20 ICOL$                          ICOL$         44 I_USER1                        I_USER1         28 CON$                           CON$         15 UNDO$                          UNDO$         29 C_COBJ#                        C_COBJ#          3 I_OBJ#                         I_OBJ#         25 PROXY_ROLE_DATA$               PROXY_ROLE_DATA$         39 I_IND1                         I_IND1         51 I_CDEF2                        I_CDEF2         26 I_PROXY_ROLE_DATA$_1           I_PROXY_ROLE_DATA$_1         17 FILE$                          FILE$  OBJECT_ID ONAME1                         ONAME2 ---------- ------------------------------ ------------------------------         13 UET$                           UET$          9 I_FILE#_BLOCK#                 I_FILE#_BLOCK#         41 I_FILE1                        I_FILE1         48 I_CON1                         I_CON1         38 I_OBJ3                         I_OBJ3          7 I_TS#                          I_TS#         53 I_CDEF4                        I_CDEF4         19 IND$                           IND$         14 SEG$                           SEG$          6 C_TS#                          C_TS#         42 I_FILE2                        I_FILE2  OBJECT_ID ONAME1                         ONAME2 ---------- ------------------------------ ------------------------------         21 COL$                           COL$         43 I_TS1                          I_TS1         35 I_UNDO2                        I_UNDO2          5 CLU$                           CLU$         23 PROXY_DATA$                    PROXY_DATA$         24 I_PROXY_DATA$                  I_PROXY_DATA$         36 I_OBJ1                         I_OBJ1         46 I_COL2                         I_COL2         37 I_OBJ2                         I_OBJ2         54 I_CCOL1                        I_CCOL1         16 TS$                            TS$  OBJECT_ID ONAME1                         ONAME2 ---------- ------------------------------ ------------------------------          8 C_FILE#_BLOCK#                 C_FILE#_BLOCK#         10 C_USER#                        C_USER#         34 I_UNDO1                        I_UNDO1         56 BOOTSTRAP$                     BOOTSTRAP$         12 FET$                           FET$         33 I_TAB1                         I_TAB1         32 CCOL$                          CCOL$         22 USER$                          USER$         49 I_CON2                         I_CON2         30 I_COBJ#                        I_COBJ#         18 OBJ$                           OBJ$  OBJECT_ID ONAME1                         ONAME2 ---------- ------------------------------ ------------------------------         47 I_COL3                         I_COL3          2 C_OBJ#                         C_OBJ#          4 TAB$                           TAB$         31 CDEF$                          CDEF$         50 I_CDEF1                        I_CDEF1 49 rows selected. SQL> SQL> select object_id, object_name oname1, get_data(object_id) oname2   2    from myTable1;  OBJECT_ID ONAME1 ---------- ------------------------------ ONAME2 --------------------------------------------------------------------------------         20 ICOL$ ICOL$         44 I_USER1 I_USER1         28 CON$ CON$  OBJECT_ID ONAME1 ---------- ------------------------------ ONAME2 --------------------------------------------------------------------------------         15 UNDO$ UNDO$         29 C_COBJ# C_COBJ#          3 I_OBJ# I_OBJ#  OBJECT_ID ONAME1 ---------- ------------------------------ ONAME2 --------------------------------------------------------------------------------         25 PROXY_ROLE_DATA$ PROXY_ROLE_DATA$         39 I_IND1 I_IND1         51 I_CDEF2 I_CDEF2  OBJECT_ID ONAME1 ---------- ------------------------------ ONAME2 --------------------------------------------------------------------------------         26 I_PROXY_ROLE_DATA$_1 I_PROXY_ROLE_DATA$_1         17 FILE$ FILE$         13 UET$ UET$  OBJECT_ID ONAME1 ---------- ------------------------------ ONAME2 --------------------------------------------------------------------------------          9 I_FILE#_BLOCK# I_FILE#_BLOCK#         41 I_FILE1 I_FILE1         48 I_CON1 I_CON1  OBJECT_ID ONAME1 ---------- ------------------------------ ONAME2 --------------------------------------------------------------------------------         38 I_OBJ3 I_OBJ3          7 I_TS# I_TS#         53 I_CDEF4 I_CDEF4  OBJECT_ID ONAME1 ---------- ------------------------------ ONAME2 --------------------------------------------------------------------------------         19 IND$ IND$         14 SEG$ SEG$          6 C_TS# C_TS#  OBJECT_ID ONAME1 ---------- ------------------------------ ONAME2 --------------------------------------------------------------------------------         42 I_FILE2 I_FILE2         21 COL$ COL$         43 I_TS1 I_TS1  OBJECT_ID ONAME1 ---------- ------------------------------ ONAME2 --------------------------------------------------------------------------------         35 I_UNDO2 I_UNDO2          5 CLU$ CLU$         23 PROXY_DATA$ PROXY_DATA$  OBJECT_ID ONAME1 ---------- ------------------------------ ONAME2 --------------------------------------------------------------------------------         24 I_PROXY_DATA$ I_PROXY_DATA$         36 I_OBJ1 I_OBJ1         46 I_COL2 I_COL2  OBJECT_ID ONAME1 ---------- ------------------------------ ONAME2 --------------------------------------------------------------------------------         37 I_OBJ2 I_OBJ2         54 I_CCOL1 I_CCOL1         16 TS$ TS$  OBJECT_ID ONAME1 ---------- ------------------------------ ONAME2 --------------------------------------------------------------------------------          8 C_FILE#_BLOCK# C_FILE#_BLOCK#         10 C_USER# C_USER#         34 I_UNDO1 I_UNDO1  OBJECT_ID ONAME1 ---------- ------------------------------ ONAME2 --------------------------------------------------------------------------------         56 BOOTSTRAP$ BOOTSTRAP$         12 FET$ FET$         33 I_TAB1 I_TAB1  OBJECT_ID ONAME1 ---------- ------------------------------ ONAME2 --------------------------------------------------------------------------------         32 CCOL$ CCOL$         22 USER$ USER$         49 I_CON2 I_CON2  OBJECT_ID ONAME1 ---------- ------------------------------ ONAME2 --------------------------------------------------------------------------------         30 I_COBJ# I_COBJ#         18 OBJ$ OBJ$         47 I_COL3 I_COL3  OBJECT_ID ONAME1 ---------- ------------------------------ ONAME2 --------------------------------------------------------------------------------          2 C_OBJ# C_OBJ#          4 TAB$ TAB$         31 CDEF$ CDEF$  OBJECT_ID ONAME1 ---------- ------------------------------ ONAME2 --------------------------------------------------------------------------------         50 I_CDEF1 I_CDEF1 49 rows selected. SQL> SQL> begin   2   3      for x in ( select a.object_id,   4                        a.object_name oname1,   5                        b.object_name oname2   6                   from myTable1 a, myTable2 b   7                  where a.object_id = b.object_id(+) )   8      loop   9          null;  10      end loop;  11  12      for x in ( select object_id,  13                        object_name oname1,  14                        get_data(object_id) oname2  15                   from myTable1 )  16      loop  17          null;  18      end loop;  19  20  end;  21  / PL/SQL procedure successfully completed. SQL> SQL> drop table myTable1; Table dropped. SQL> drop table myTable2; Table dropped. SQL>