Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Implicit conversion from Java type conversion compare MySQL and Oracle     - Linux kernel source code analysis (Linux)

- Example of use WebSocket (Programming)

- By creating a personal technology blog Detailed GitHub (Linux)

- Ubuntu Control Panel to resolve network-manager icon display issue (Linux)

- To install and configure the Jetty server and JDK under Ubuntu 14.04.2 (Server)

- GROUP BY extension (Database)

- MySQL Data Types (Database)

- Linux System Getting Started Tutorial: how to find information on Linux-embedded module (Linux)

- Oracle Data Guard LOG_ARCHIVE_DEST_n optional attribute parameters (Database)

- Based AutoYaST automated installation of SUSE practice (Linux)

- Use C program in JavaScript (Programming)

- Keepalived + HAProxy high availability load balancing (Server)

- The hashcode method of Java (Programming)

- C ++ Supplements - malloc free and new delete the same and different (Programming)

- 10 Regulation of painless SQL Schema (Database)

- How to improve the performance of Ruby On Rails (Linux)

- Linux Network Security: nmap port scanning software (Linux)

- Use custom backup plans for Debian backupninja (Linux)

- Spring3 + SpringMVC + Hibernate4 full annotation environment configuration (Server)

- How Bluetooth turned off by default in Ubuntu 14.04 (Linux)

 
         
  Implicit conversion from Java type conversion compare MySQL and Oracle
     
  Add Date : 2018-11-21      
         
         
         
  Speaking of data type conversion, in the development of the case, in the database, too, before the simple comparison of MySQL, and Oracle data type conversions

But after that time finished, there is a reader casually asked one why, why? It seems he is still scanty, he said the rules, no rules no standards, the idea is beyond reproach, but I think it is again a look and see what can harvest, the next I can not guarantee the correctness of the content, I hope everyone out Discrimination also wish to make comments, after all, it is hoped the problem thoroughly understand it.

 Language first developed languages ​​have implicit conversion of data types, which is particularly evident in java, after all, a mission of carrying too much so huge, is a strongly typed language, data type conversion is a particularly important part of the . The Java data type conversion rules are the following.
// Conversion rules: range from small storage type to store range type.
// Specific rules: byte -> short (char) -> int -> long -> float -> double
Joseph himself tinkling a bit, write a simple little program to show proof, I will not explain this program java.
 public class Test {
 public static void main (String args []) {
 / * 1 * / System.out.println ( "aa");
/ * 2 * / System.out.println ( 'a');
/ * 3 * / byte a = 10;
/ * 4 * / System.out.println (a);
/ * 5 * / char b = 'b';
/ * 6 * / int c = b;
/ * 7 * / System.out.println (b);
/ * 8 * / System.out.println (c);
    }
 }
The output of this program
aa
 a
 10
 b
 98

The purpose is to write,
 First row, the second row of single quotes, double quotes need to do is to indicate that it is a variable value, the effect of both is the same at this time.
 Line 3 initializes a byte variable, then the output of this time or byte
However, Line 5 declares a type char variable, and then do a type of implicit conversion on line 6, the output of the character b in the seventh row, but the output is on line 8
 Through this simple example can be found in data types really do implicit conversion, and single quotes, double quotes role in this case is consistent, that is marked variables.
 Because the cost of viewing data type conversion in Java is still relatively difficult, we can come analogy in the database.
 First or repeat the previous test, prepare a batch of data. Create a table and insert some values.
create table test (id1 number, id2 varchar2 (10));
  begin
    for i in 1..100 loop
    insert into test values ​​(i, chr (39) || i || chr (39));
    end loop;
    commit;
    end;
    /
 create index ind1_test on n1.test (id1);
 create index ind2_test on n1.test (id2);
Then gather the statistics.
exec dbms_stats.gather_table_stats ( 'TEST', 'TEST', CASCADE => TRUE);
This time to view the execution plan
explain plan for select * from test where id1 = '2';
 SQL> select * from table (dbms_xplan.display);
 PLAN_TABLE_OUTPUT
 -------------------------------------------------- --------------------------------------------------
 Plan hash value: 2759464289
 -------------------------------------------------- ---------------------------------------
 | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
 -------------------------------------------------- ---------------------------------------
 | 0 | SELECT STATEMENT | | 1 | 20 | 1 (0) | 00:00:01 |
 | 1 | TABLE ACCESS BY INDEX ROWID | TEST | 1 | 20 | 1 (0) | 00:00:01 |
 | * 2 | INDEX RANGE SCAN | IND1_TEST | 1 | | 1 (0) | 00:00:01 |
 -------------------------------------------------- ---------------------------------------
 Predicate Information (identified by operation id):
 PLAN_TABLE_OUTPUT
 -------------------------------------------------- -----------
    2 - access ( "ID1" = 2)
Through this can indeed see section 2 predicate information - access ( "ID1" = 2) it has been automatically made the conversion, this time a triggered an index scan.
 But the process is still not see traces of data type conversion, we do have a problem seemingly example, to trigger it. Although id1 bit int type, but the use of character to trigger.
SQL> explain plan for select * from test where id1 = 'A';
 Explained.
 SQL> select * from table (dbms_xplan.display);
 PLAN_TABLE_OUTPUT
 -------------------------------------------------- --------------------------------------------------
 Plan hash value: 2759464289
 -------------------------------------------------- ---------------------------------------
 | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
 -------------------------------------------------- ---------------------------------------
 | 0 | SELECT STATEMENT | | 1 | 20 | 1 (0) | 00:00:01 |
 | 1 | TABLE ACCESS BY INDEX ROWID | TEST | 1 | 20 | 1 (0) | 00:00:01 |
 | * 2 | INDEX RANGE SCAN | IND1_TEST | 1 | | 1 (0) | 00:00:01 |
 -------------------------------------------------- ---------------------------------------
 Predicate Information (identified by operation id):
 PLAN_TABLE_OUTPUT
 ------------------------------------------------
    2 - access ( "ID1" = TO_NUMBER ( 'A'))
We can see the predicate information has been changed. 2 - access ( "ID1" = TO_NUMBER ( 'A')) from this place we can see that really triggered a to_number operation.
 The optimizer at this time, although the trigger, but when sql running, it will report an error, this time you can see that Oracle is still quite strict.
SQL> select * from test where id1 = 'A';
 select * from test where id1 = 'A'
                            *
 ERROR at line 1:
 ORA-01722: invalid number
If you use double quotes, execution plan will be throwing error.
SQL> explain plan for select * from test where id1 = "A";
explain plan for select * from test where id1 = "A"
                                            *
ERROR at line 1:
ORA-00904: "A": invalid identifier
Visible single and double quotes is very different meaning in Oracle representative.

 Let's look at MySQL's performance.
 Or create a simple table, insert some data.
> Create table test (id1 int, id2 varchar (10));
 > Insert into test values ​​(1, '1');
 > Insert into test values ​​(2, '2');
 > Insert into test values ​​(3, '3');
 > Commit;
 > Create index idx_id1 on test (id1);
 > Create index idx_id2 on test (id2);
This time execution plan can be found walking the index
> Explain select * from test where id1 = '1';
 + ------------- + ---- + ------- + ------ + --------------- + --------- + --------- + ------- + ------ + ------------- +
 | Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
 + ------------- + ---- + ------- + ------ + --------------- + --------- + --------- + ------- + ------ + ------------- +
 | 1 | SIMPLE | test | ref | idx_id1 | idx_id1 | 5 | const | 1 | Using where |
 + ------------- + ---- + ------- + ------ + --------------- + --------- + --------- + ------- + ------ + ------------- +
 1 row in set (0.00 sec)
And if the view is id1 of type varchar, also walked the index.
> Explain select * from test where id1 = 'a';
 + ------------- + ---- + ------- + ------ + --------------- + --------- + --------- + ------- + ------ + ------------- +
 | Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
 + ------------- + ---- + ------- + ------ + --------------- + --------- + --------- + ------- + ------ + ------------- +
 | 1 | SIMPLE | test | ref | idx_id1 | idx_id1 | 5 | const | 1 | Using where |
 + ------------- + ---- + ------- + ------ + --------------- + --------- + --------- + ------- + ------ + ------------- +
 1 row in set (0.00 sec)
If the difference is greater id1 = 'a', it is possible to perform normal, but there is no matching record.
> Select * from test where id1 = 'a';
 Empty set (0.00 sec)
And if the single quotes to double quotes, and can operate normally.
> Select * from test where id1 = "a";
 Empty set (0.00 sec)
And the case of double quotation marks, the execution plan is not a problem.
> Explain select * from test where id1 = "a";
 + ------------- + ---- + ------- + ------ + --------------- + --------- + --------- + ------- + ------ + ------------- +
 | Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
 + ------------- + ---- + ------- + ------ + --------------- + --------- + --------- + ------- + ------ + ------------- +
 | 1 | SIMPLE | test | ref | idx_id1 | idx_id1 | 5 | const | 1 | Using where |
 + ------------- + ---- + ------- + ------ + --------------- + --------- + --------- + ------- + ------ + ------------- +
 1 row in set (0.00 sec)
MySQL can be seen in the wider scope this time seems to be, not only in MySQL with single quotes, double quotes, but also often see this * symbol.
 Such flexibility can declare some changes in MySQL, for example, is not appropriate, for example, we create a table, a field int, int type directly in the following way to write, certainly throwing error.
> Create table test1 (int int);
 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int int)' at line 1
 crea 'at line 1
* You can add, it can be identified.
> Create table test1 ( `int` int);
 Query OK, 0 rows affected (0.00 sec)

Contrast this span of a little big, but with some little trick seems to be able to be seen in these types of conversions, the optimizer side of the trigger situation. Redouble their efforts to continue to explore.
     
         
         
         
  More:      
 
- Iptables application layer plug (Linux)
- List Leaves (Programming)
- ElasticSearch basic usage and cluster structures (Server)
- How to adjust the system time CentOS (Linux)
- How do I upgrade to Ubuntu 15.04 (Beta) (Linux)
- Ubuntu 14.04 / Linux Mint 17 How to install the MintMenu 5.5.2 menu (Linux)
- Ubuntu and Derivative Edition users install LMMS 0.4.15 (Linux)
- CentOS6 installation configuration rsh (Server)
- Text analysis tools - awk (Linux)
- Linux system on how to use rsync to synchronize data (Server)
- ORA-01000 Solution (Database)
- Guide: Trickle restrict application bandwidth usage (Linux)
- Linux operating system boot manager -GRUB (Linux)
- To install and deploy PHP environment under the CentOS (Server)
- Oracle table of nested loop connection (Database)
- MySQL server after an unexpected power outage can not start (Database)
- Ubuntu 14.04.02 LTS startup items erroneous writing / dev / sda1 (win 7 loader) Repair (Linux)
- Linux, set and view environment variables (Linux)
- A simple shell script for monitoring in Linux (Linux)
- Linux System Getting Started Learning: Debian download, installation and graphical interface (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.