Friday, January 30, 2015

3 new things about sdsql


New Name !


The first is a new name this EA it's named sqlcl for sql command line.  However, the binary to start it up is simply sql.  Nothing is easier when you need to run some sql than typing 'sql' and hitting enter.


#./sql klrice/klrice@//localhost/orcl

SQLcl: Release 4.1.0 Beta on Fri Jan 30 12:53:05 2015

Copyright (c) 1982, 2015, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 


There's never too much Information


The next is a new information command has a lot more in it now.  Hit me up on twitter if you can think of more things it should show some of the things here are directly from folks doing exactly that. Sometimes functional specs can be done in 140 chars.



KLRICE@ORCL>
KLRICE@ORCL>info hr.employees
TABLE: EMPLOYEES 
  LAST ANALYZED:2015-01-17 12:06:26.0 
  ROWS         :107 
  SAMPLE SIZE  :107 
  INMEMORY     :DISABLED 
  COMMENTS     :employees table. Contains 107 rows. References with departments,
                       jobs, job_history tables. Contains a self reference. 

Columns 
NAME             DATA TYPE           NULL  DEFAULT    COMMENTS
*EMPLOYEE_ID     NUMBER(6,0)         No               Primary key of employees table.
 FIRST_NAME      VARCHAR2(20 BYTE)   Yes              First name of the employee. A not null column.
 LAST_NAME       VARCHAR2(25 BYTE)   No               Last name of the employee. A not null column.
 EMAIL           VARCHAR2(25 BYTE)   No               Email id of the employee
 PHONE_NUMBER    VARCHAR2(20 BYTE)   Yes              Phone number of the employee; includes country code and area code
 HIRE_DATE       DATE                No               Date when the employee started on this job. A not null column.
 JOB_ID          VARCHAR2(10 BYTE)   No               Current job of the employee; foreign key to job_id column of the
                                                      jobs table. A not null column.
 SALARY          NUMBER(8,2)         Yes              Monthly salary of the employee. Must be greater
                                                      than zero (enforced by constraint emp_salary_min)
 COMMISSION_PCT  NUMBER(2,2)         Yes              Commission percentage of the employee; Only employees in sales
                                                      department elgible for commission percentage
 MANAGER_ID      NUMBER(6,0)         Yes              Manager id of the employee; has same domain as manager_id in
                                                      departments table. Foreign key to employee_id column of employees table.
                                                      (useful for reflexive joins and CONNECT BY query)
 DEPARTMENT_ID   NUMBER(4,0)         Yes              Department id where employee works; foreign key to department_id
                                                      column of the departments table

Indexes
INDEX_NAME            UNIQUENESS  STATUS  FUNCIDX_STATUS  COLUMNS                COLUMN_EXPRESSION  
HR.EMP_JOB_IX         NONUNIQUE   VALID                   JOB_ID                                    
HR.EMP_NAME_IX        NONUNIQUE   VALID                   LAST_NAME, FIRST_NAME                     
HR.EMP_EMAIL_UK       UNIQUE      VALID                   EMAIL                                     
HR.EMP_EMP_ID_PK      UNIQUE      VALID                   EMPLOYEE_ID                               
HR.EMP_MANAGER_IX     NONUNIQUE   VALID                   MANAGER_ID                                
HR.EMP_DEPARTMENT_IX  NONUNIQUE   VALID                   DEPARTMENT_ID                             


References
TABLE_NAME   CONSTRAINT_NAME  DELETE_RULE  STATUS   DEFERRABLE      VALIDATED  GENERATED  
DEPARTMENTS  DEPT_MGR_FK      NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME  
EMPLOYEES    EMP_MANAGER_FK   NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME  
JOB_HISTORY  JHIST_EMP_FK     NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME  

KLRICE@ORCL>




Info doesn't end there, you can do info+ and get different data and I'm in the process of changing the indexes to show some stats also like the main table.

KLRICE@ORCL>info+ hr.employees
TABLE: EMPLOYEES 
  LAST ANALYZED:2015-01-17 12:06:26.0 
  ROWS         :107 
  SAMPLE SIZE  :107 
  INMEMORY     :DISABLED 
  COMMENTS     :employees table. Contains 107 rows. References with departments,
                       jobs, job_history tables. Contains a self reference. 

Columns 
NAME             DATA TYPE           NULL  DEFAULT    LOW_VALUE             HIGH_VALUE            NUM_DISTINCT   HISTOGRAM  
*EMPLOYEE_ID     NUMBER(6,0)         No               100                   206                   107            NONE       
 FIRST_NAME      VARCHAR2(20 BYTE)   Yes              Adam                  Winston               91             NONE       
 LAST_NAME       VARCHAR2(25 BYTE)   No               Abel                  Zlotkey               102            NONE       
 EMAIL           VARCHAR2(25 BYTE)   No               ABANDA                WTAYLOR               107            NONE       
 PHONE_NUMBER    VARCHAR2(20 BYTE)   Yes              011.44.1343.329268    650.509.4876          107            NONE       
 HIRE_DATE       DATE                No               1987.06.17.00.00.00   2000.04.21.00.00.00   98             NONE       
 JOB_ID          VARCHAR2(10 BYTE)   No               AC_ACCOUNT            ST_MAN                19             NONE       
 SALARY          NUMBER(8,2)         Yes              2100                  24000                 57             NONE       
 COMMISSION_PCT  NUMBER(2,2)         Yes              .1                    .4                    7              NONE       
 MANAGER_ID      NUMBER(6,0)         Yes              100                   205                   18             NONE       
 DEPARTMENT_ID   NUMBER(4,0)         Yes              10                    110                   11             NONE       

Indexes
INDEX_NAME            UNIQUENESS  STATUS  FUNCIDX_STATUS  COLUMNS                COLUMN_EXPRESSION  
HR.EMP_JOB_IX         NONUNIQUE   VALID                   JOB_ID                                    
HR.EMP_NAME_IX        NONUNIQUE   VALID                   LAST_NAME, FIRST_NAME                     
HR.EMP_EMAIL_UK       UNIQUE      VALID                   EMAIL                                     
HR.EMP_EMP_ID_PK      UNIQUE      VALID                   EMPLOYEE_ID                               
HR.EMP_MANAGER_IX     NONUNIQUE   VALID                   MANAGER_ID                                
HR.EMP_DEPARTMENT_IX  NONUNIQUE   VALID                   DEPARTMENT_ID                             


References
TABLE_NAME   CONSTRAINT_NAME  DELETE_RULE  STATUS   DEFERRABLE      VALIDATED  GENERATED  
DEPARTMENTS  DEPT_MGR_FK      NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME  
EMPLOYEES    EMP_MANAGER_FK   NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME  
JOB_HISTORY  JHIST_EMP_FK     NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME  

KLRICE@ORCL>


Those who don't know history are doomed to repeat.


The last thing to be sure to take a look at is the history. History by itself is great however we wanted to show how often the history is uses so there's now "history usage" which will show how many times that history item has been run.  The other thing new here is that instead of timing statements only when user request it, we now time and record that time for everything.  You can access is very simply with "history time" that will show the timings of how long the things in the history took.  The format of the time is HH:MI:SS.fff and we shorten the leading numbers when zero to be able to read and format it easier.






KLRICE@ORCL>history time
....
 20           info s
 21  (00.050) select table_name from user_tables
 22           info+ s
 23           info hr.dept
 24           into scott.emp
 25           info scott.emp
 26           info+ scott.emp
 27           info all_tables
 28           info all_tab_comments
 29  (04.531) select * from all_tables t,all_tab_comments tc where t.owner = tc.owner and t.table_name = tc.table_name
 30  (00.420) select * from all_tables t,all_tab_comments tc  where t.owner = tc.owner (+) and t.table_name = tc.table_name(+) 



While this is 3 really quick things, there's a lot more in there with this new EA. We will blog the other new things more in the coming days.