Monday, June 12, 2017

Which are the important performance parameters for MyISAM and InnoDB?

For MyISAM

key_cache_size
thread_cache_size
tmp_table_size
max_heap_table_size
read_buffer_size
query_cache

For InnoDB

innodb_buffer_pool_size
innodb_buffer_pool_instances
innodb_log_file_size
innodb_log_buffer_size
tmp_table_size
max_heap_table_size
table_open_cache

What's the difference between MyISAM and InnoDB

MYISAM:

MYISAM supports Table-level Locking
MyISAM designed for need of speed
MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.
MYISAM supports fulltext search
You can use MyISAM, if the table is more static with lots of select and less update and delete.


INNODB:

InnoDB supports Row-level Locking
InnoDB designed for maximum performance when processing high volume of data
InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
InnoDB stores its tables and indexes in a tablespace
InnoDB supports transaction. You can commit and rollback with InnoDB

Wednesday, June 7, 2017

Partitioned Tables And Indexes in oracle

Range Partitioning Tables
Hash Partitioning Tables
Composite Partitioning Tables


range partition
***************


 create  table emp_pat (empno number(4) not null,ename varchar2(10),job varchar2(9),
    mgr number(4),hiredate date,sal number(7,2) ,comm number(7,2),deptno number(2))
 partition by range(deptno) (partition p1 values less than(10),
    partition p2 values less than (20),
    partition p3 values less than (30),
    partition p4 values less than (maxvalue))




insert into emp_pat select * from emp;





SQL> explain plan for select * from emp_pat where deptno=40;

Explained.

SQL> select * from table (dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 253299801

--------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |     1 |    87 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|         |     1 |    87 |     3   (0)| 00:00:01 |     4 |     4 |
|*  2 |   TABLE ACCESS FULL    | EMP_PAT |     1 |    87 |     3   (0)| 00:00:01 |     4 |     4 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter("DEPTNO"=40)

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.

SQL> explain plan for select * from emp_pat where sal<=1050;

Explained.



SQL> select * from table (dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3695347441

-----------------------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     2 |   174 |     6   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|         |     2 |   174 |     6   (0)| 00:00:01 |     1 |     4 |
|*  2 |   TABLE ACCESS FULL | EMP_PAT |     2 |   174 |     6   (0)| 00:00:01 |     1 |     4 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter("SAL"<=1050)

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.

SQL>




range partition without maxvalue
********************************


SQL> drop table emp_pat;

Table dropped.

SQL>  create  table emp_pat (empno number(4) not null,ename varchar2(10),job varchar2(9),
  2      mgr number(4),hiredate date,sal number(7,2) ,comm number(7,2),deptno number(2))
  3   partition by range(deptno) (partition p1 values less than(10),
  4      partition p2 values less than (20),
  5      partition p3 values less than (30))
  6  /

Table created.

SQL> insert into emp_pat select * from emp;
insert into emp_pat select * from emp
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

  or
  SQL>   create  table emp_pat (empno number(4) not null,ename varchar2(10),job varchar2(9),
  2    mgr number(4),hiredate date,sal number(7,2) ,comm number(7,2),deptno number(2))
  3       partition by range(sal) (partition p1 values less than(1000),
  4         partition p2 values less than (2000),
  5          partition p3 values less than (4000))
  6  /

Table created.


SQL> insert into emp_pat select * from emp;
insert into emp_pat select * from emp
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition --( one row is more than 5000 sal is there )


SQL> insert into emp_pat select * from emp where sal <=4000;

13 rows created.


SQL> select * from emp_pat partition (p1);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

SQL> select * from emp_pat partition (p2);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

6 rows selected.

SQL> select * from emp_pat partition (p3);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20


 
 
SQL> SELECT  TABLE_NAME,    PARTITION_NAME,    HIGH_VALUE
  2  FROM    USER_TAB_PARTITIONS
  3  WHERE   TABLE_NAME='EMP_PAT'
  4  ORDER BY  PARTITION_NAME;

       TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
 ------------------------------ ------------------------------ -------------------------
           EMP_PAT                        P1                             1000
           EMP_PAT                        P2                             2000
           EMP_PAT                        P3                             4000

 
 
 
 
 
  range partition with Interval
********************************
 
  SQL>   create  table emp_pat (empno number(4) not null,ename varchar2(10),job varchar2(9),
  2    mgr number(4),hiredate date,sal number(7,2) ,comm number(7,2),deptno number(2))
  3  PARTITION BY RANGE(hiredate)
  4  INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))
  5  (
  6     PARTITION p1 VALUES LESS THAN (TO_DATE('01-12-1980', 'DD-MM-YYYY')),
  7     PARTITION p2 VALUES LESS THAN (TO_DATE('1-12-1981', 'DD-MM-YYYY')) ,
  8       PARTITION p3 VALUES LESS THAN (TO_DATE('1-12-1982', 'DD-MM-YYYY'))
  9  )
 10  /

Table created.

 
 SQL> insert into emp_pat select * from emp where hiredate<='03-DEC-82';

12 rows created.

SQL> SELECT num_rows, TABLE_NAME,    PARTITION_NAME,    HIGH_VALUE
  2  FROM    USER_TAB_PARTITIONS
  3  WHERE   TABLE_NAME='EMP_PAT'
  4  ORDER BY  PARTITION_NAME
  5  /

  NUM_ROWS TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
---------- ------------------------------ ------------------------------ --------------------------------------------------------------------------------
           EMP_PAT                        P1                             TO_DATE(' 1980-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
           EMP_PAT                        P2                             TO_DATE(' 1981-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
           EMP_PAT                        P3                             TO_DATE(' 1982-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'



SQL> delete from emp_pat;

12 rows deleted.

SQL> commit;

Commit complete.

SQL> insert into emp_pat select * from emp;

14 rows created.

SQL> SELECT num_rows, TABLE_NAME,    PARTITION_NAME,    HIGH_VALUE
  2  FROM    USER_TAB_PARTITIONS
  3  WHERE   TABLE_NAME='EMP_PAT'
  4  /

  NUM_ROWS TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
---------- ------------------------------ ------------------------------ --------------------------------------------------------------------------------
           EMP_PAT                        P1                             TO_DATE(' 1980-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
           EMP_PAT                        P2                             TO_DATE(' 1981-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
           EMP_PAT                        P3                             TO_DATE(' 1982-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
           EMP_PAT                        SYS_P21                        TO_DATE(' 1983-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'

 
 
 
  List Partition
  ****************
 
 SQL>  create  table emp_pat (empno number(4) not null,ename varchar2(10),job varchar2(9),
  2    mgr number(4),hiredate date,sal number(7,2) ,comm number(7,2),deptno number(2))
  3  PARTITION BY LIST(deptno)
  4  (Partition p1 values (10),
  5  partition p2 values (20,30),
  6  partition p3 values (default))
  7  /

Table created.

SQL>  create  table emp_pat (empno number(4) not null,ename varchar2(10),job varchar2(9),
  2    mgr number(4),hiredate date,sal number(7,2) ,comm number(7,2),deptno number(2))
  3  PARTITION BY LIST(deptno)
  4  (Partition p1 values (10),
  5  partition p2 values (20,30),
  6  partition p3 values (default))
  7  /

Table created.

SQL> insert into emp_pat select * from emp;

14 rows created.


SQL> SELECT num_rows, TABLE_NAME,    PARTITION_NAME,    HIGH_VALUE
  2  FROM    USER_TAB_PARTITIONS
  3  WHERE   TABLE_NAME='EMP_PAT'
  4  ORDER BY  PARTITION_NAME;

         TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
  ------------------------------ ------------------------------ --------------------------------------------------------------------------------
           EMP_PAT                        P1                             10
           EMP_PAT                        P2                             20, 30
           EMP_PAT                        P3                             default

   SQL> select * from emp_pat partition (p3);

no rows selected

SQL> select * from emp_pat partition (p2);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

11 rows selected.

SQL> select * from emp_pat partition (p1);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

SQL> explain plan for select empno,ename,sal,deptno from emp_pat where deptno=20;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3226506680

-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |    46 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE|         |     1 |    46 |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL   | EMP_PAT |     1 |    46 |     3   (0)| 00:00:01 |     2 |     2 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter("DEPTNO"=20)

14 rows selected.

SQL> explain plan for select empno,ename,sal,deptno from emp_pat where sal<=3000;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2273502597

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |    46 |     4   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST ALL|         |     1 |    46 |     4   (0)| 00:00:01 |     1 |     3 |
|*  2 |   TABLE ACCESS FULL| EMP_PAT |     1 |    46 |     4   (0)| 00:00:01 |     1 |     3 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter("SAL"<=3000)

14 rows selected.



  List Partition without (default )
  *********************

SQL> drop table emp_pat;

Table dropped.

SQL>   create  table emp_pat (empno number(4) not null,ename varchar2(10),job varchar2(9),
  2    mgr number(4),hiredate date,sal number(7,2) ,comm number(7,2),deptno number(2))
  3  PARTITION BY LIST(deptno)
  4  (Partition p1 values (10),
  5  partition p2 values (20))
  6  /

Table created.

SQL>
SQL> SELECT  TABLE_NAME,    PARTITION_NAME,    HIGH_VALUE
  2  FROM    USER_TAB_PARTITIONS
  3  WHERE   TABLE_NAME='EMP_PAT'
  4  ORDER BY  PARTITION_NAME
  5  /

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
EMP_PAT                        P1                             10
EMP_PAT                        P2                             20

SQL>

 SQL> insert into emp_pat select * from emp;
insert into emp_pat select * from emp
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition


 
             HASH Partitioning
             *******************
 
 SQL>   create  table emp_pat (empno number(4) not null,ename varchar2(10),job varchar2(9),
  2    mgr number(4),hiredate date,sal number(7,2) ,comm number(7,2),deptno number(2))
  3  PARTITION BY HASH(deptno)
  4  Partitions 4;

Table created.

SQL> insert into emp_pat select * from emp;

14 rows created.

SQL> SELECT  TABLE_NAME,    PARTITION_NAME,    HIGH_VALUE
  2  FROM    USER_TAB_PARTITIONS
  3  WHERE   TABLE_NAME='EMP_PAT'
  4  ORDER BY  PARTITION_NAME
  5  /

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
EMP_PAT                        SYS_P22
EMP_PAT                        SYS_P23
EMP_PAT                        SYS_P24
EMP_PAT                        SYS_P25

SQL> select * from emp_pat partition (SYS_P22);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

6 rows selected.

SQL> select * from emp_pat partition (SYS_P23);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

SQL> select * from emp_pat partition (SYS_P24);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

SQL> select * from emp_pat partition (SYS_P25);

no rows selected

SQL> explain plan for select * from emp_pat where sal>100;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1594423551

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    14 |  1218 |     6   (0)| 00:00:01 |       |       |
|   1 |  PARTITION HASH ALL|         |    14 |  1218 |     6   (0)| 00:00:01 |     1 |     4 |
|*  2 |   TABLE ACCESS FULL| EMP_PAT |    14 |  1218 |     6   (0)| 00:00:01 |     1 |     4 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter("SAL">100)

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.

SQL>
SQL>
SQL> explain plan for select sal,empno,ename from emp_pat where deptno=10;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3260685292

-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     3 |   138 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION HASH SINGLE|         |     3 |   138 |     3   (0)| 00:00:01 |     2 |     2 |
|*  2 |   TABLE ACCESS FULL   | EMP_PAT |     3 |   138 |     3   (0)| 00:00:01 |     2 |     2 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter("DEPTNO"=10)

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.







 
 
 
 

  

ORA-14402: updating partition key column would cause a partition change

SQL> CREATE TABLE honnikery
  2  (
  3    id NUMBER       NOT NULL,
  4    name       VARCHAR2(12) NOT NULL,
  5    name_id   VARCHAR2(30) NOT NULL
  6  )
  7  PARTITION BY LIST (CITY_ID)
  8  (
  9    PARTITION P282 VALUES ('1'),
 10    PARTITION P283 VALUES ('10'),
 11    PARTITION P284 VALUES ('20'));
Table created.
SQL>
SQL> INSERT INTO honnikery VALUES (1,'prabhakr','1');
1 row created.
SQL> INSERT INTO honnikery VALUES (2,'honnikery','1');
1 row created.
SQL> INSERT INTO honnikery VALUES (3,'pawan','1');
1 row created.
SQL> INSERT INTO honnikery VALUES (4,'daivik','1');
1 row created.
SQL> COMMIT;
Commit complete.




SQL> UPDATE honnikery SET name_id = '1' WHERE id = 1;
UPDATE honnikery SET name_id = '1' WHERE id = 1
       *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change




SQL> ALTER TABLE honnikery ENABLE ROW MOVEMENT;
Table altered.
SQL>UPDATE honnikery SET name_id = '1' WHERE id = 1;
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> ALTER TABLE honnikery DISABLE ROW MOVEMENT;
Table altered.

Monday, May 22, 2017

MySql Database Design Best Practices

1. Optimize Your Queries For the Query Cache
2. EXPLAIN Your SELECT Queries
3. LIMIT 1 When Getting a Unique Row
4. Index the Search Fields
5. Do Not ORDER BY RAND()
6. Avoid SELECT *
7. Almost Always Have an id Field
8. Use ENUM over VARCHAR
9.Get Suggestions with PROCEDURE ANALYSE()
10.Use NOT NULL If You Can
11.Prepared Statements
12.Fixed-length (Static) Tables are Faster
13.Split the Big DELETE or INSERT Queries
14.Smaller Columns Are Faster
15.Choose the Right Storage Engine
a)MyISAM Storage Engine
b)InnoDB Storage Engine1. Optimize Your Queries For the Query Cache
2. EXPLAIN Your SELECT Queries
3. LIMIT 1 When Getting a Unique Row
4. Index the Search Fields
5. Do Not ORDER BY RAND()
6. Avoid SELECT *
7. Almost Always Have an id Field
8. Use ENUM over VARCHAR
9.Get Suggestions with PROCEDURE ANALYSE()
10.Use NOT NULL If You Can
11.Prepared Statements
12.Fixed-length (Static) Tables are Faster
13.Split the Big DELETE or INSERT Queries
14.Smaller Columns Are Faster
15.Choose the Right Storage Engine
a)MyISAM Storage Engine
b)InnoDB Storage Engine
16. Use an Object Relational Mapper
17. Use well defined and consistent names for tables and columns
18. Use singular for table names . Table represents a collection of entities, there is no need for plural names.
19. Don’t use unnecessary prefixes or suffixes for table names .
20. Keep passwords as encrypted for security. Decrypt them in application when required.
21. Provide authentication for database access. Don’t give admin role to each user.
 22.Use an ORM (object relational mapping) framework (i.e. hibernate, iBatis ...) if application code is big enough. Performance issues of ORM frameworks can be handled by detailed configuration parameters.

16. Use an Object Relational Mapper
17. Use well defined and consistent names for tables and columns
18. Use singular for table names . Table represents a collection of entities, there is no need for plural names.
19. Don’t use unnecessary prefixes or suffixes for table names .
20. Keep passwords as encrypted for security. Decrypt them in application when required.
21. Provide authentication for database access. Don’t give admin role to each user.
 22.Use an ORM (object relational mapping) framework  if application code is big enough. Performance issues of ORM frameworks can be handled by detailed configuration parameters.


Friday, April 14, 2017

How to Define an Auto Increment Primary Key in Oracle



 Create the table

 create table honnikery (
      id number primary key,
      name varchar2(100)
    );

Create sequence
 create sequence honni_id_seq;

create a trigger that uses the sequence to populate the primary key
create trigger trg_honni_id
      before insert on honnikery
      for each row
    begin
      select honni_id_seq.nextval
        into :new.id
        from dual;
    end;



insert into honnikery( name ) values ('honnikery Prabhakar');
insert into honnikery( name ) values ('Biswa');

Wednesday, April 5, 2017

How does one get the time difference between two date columns SQL

How does one get the time difference between two date columns
 in hours , minutes and seconds



SELECT floor((D1-D2)*24)
      || ' HOURS ' ||
          mod(floor((D1-D2)*24*60),60)
        || ' MINUTES ' ||
        mod(floor((D1-D2)*24*60*60),60)
         || ' SECS ' time_difference
    FROM honnikery;



TIME_DIFF
--------------------------------------------------------------------------------
1 HOURS 0 MINUTES 0 SECS
1 HOURS 10 MINUTES 0 SECS
10 HOURS 1 MINUTES 10 SECS