Write sql statement notes

2011-10-27  来源:本站原创  分类:Database  人气:2364 

1, poorly written using bind variables:
select * from basetab where msisdn = '13800138000 '
The correct wording:
select * from basetab where msisdn =: 1
Using preparedstatement in jdbc
2, do not frequently commit
Not the right way:
insert into basetab values ​​(....);
commit;
insert into basetab values ​​(....);
commit;
The correct way:
insert into basetab values ​​(....);
insert into basetab values ​​(....);
commit;
Prohibit the use AutoCommit jdbc
3, sub-table design to avoid a bad way:
According to a daily or monthly time sheet application development according to the table to determine which operations will be written as a dynamic SQL
The right way:
Partition table using the time
4, the careful design of the index if the query result set records for the table using an index with more than 5% efficiency if the number of query result set closer to the table the number of records, use the index is invalid
5, the proper use of composite index
create index inx_bas_msi_obj on subscribeinfo (msisdn, objectid);
The correct SQL wording:
select * from subscribeinfo where msisdn =: 1 and objectid =: 2
Incorrect SQL wording:
Select * from subscribeinfo where objectid =: 1 and msisdn =: 2

相关文章
  • oracle sql statement notes 2011-08-08

    Left outer join examples: select employee_id, last_name, salary, department_id, department_name from employees left join departments using (department_id) (Equivalent to two tables connected with department_id) Right outer join example: select employ

  • DB2 SQL statement notes 2011-02-23

    1, CASE WHEN statement The use of SQL CASE WHEN, Case has two formats: a simple search function and Case Case function. - Simple Case function CASE sex WHEN '1 'THEN' Male ' WHEN '2 'THEN' Female ' ELSE 'other' END - Case Search function CASE WHEN se

  • Write sql statement notes 2011-10-27

    1, poorly written using bind variables: select * from basetab where msisdn = '13800138000 ' The correct wording: select * from basetab where msisdn =: 1 Using preparedstatement in jdbc 2, do not frequently commit Not the right way: insert into baseta

  • Output parameters configuration hibernate sql statement 2010-08-09

    In J2ee applications, if the use of Hibernate framework, you can automatically create a database of entities and entity class mapping Java, Java program much easier to access and manipulate persistent objects, which significantly improve the efficien

  • The study notes oracle SQL statement editing 2010-10-28

    First, order a [ppend] text -> will be appended to the text after the current line Second, the command c [hange] / old / new -> old will replace the current new Third, the command cl [ear] buffer -> Clear the buffer so the line Fourth, the comman

  • The study notes oracle edit the SQL statement 2010-10-28

    First, order a [ppend] text -> will be appended to the text after the current line Second, the command c [hange] / old / new -> replace the current old new Third, the command cl [ear] buffer -> Clear the buffer so the line Fourth, the command inp

  • SQL statement Study Notes 2011-03-22

    1 What is SQL SQL: Structured query language Function: query, manipulate, define, control 2.SQL relational database table three basic modes: independent existence of tables, each corresponding to a basic table storage file, a table can be stored with

  • In JDK 1.5 in the form of a real object-oriented design documents SQL statement 2010-05-26

    In JDK 1.5 in the form of a real object-oriented SQL statement 4 (with source code, code generation tools) In JDK 1.5 in the form of a real object-oriented SQL statement 3 (with source code, code generation tools) Design Notes First, use the bridge p

  • Reposted elsewhere - by analyzing the SQL statement execution plan optimization of SQL (summary) 2010-06-30

    By analyzing the SQL statement execution plan optimization of SQL (summary) DBA did almost 7 years, and sentiment among many. In the DBA's daily work, to adjust individual performance to a less challenging when the SQL statement of work. The key lies

  • oracle sql statement (a) 2010-12-10

    Note: The database version is 10g, but also applies to most of 9i, 9i flashback no. 1. Who does not care to develop a database table to delete all the library was scared to death. Results found the following statement to restore the data before 1 hou

  • oracle sql statement in the Plan of Implementation of the method 2011-01-14

    How to generate explain plan? Answer: Run utlxplan.sql. Create plan table For a particular SQL statement, using the explain plan set statement_id = 'tst1' into plan_table for sql statement Run explain plan utlxplp.sql or utlxpls.sql View (Select * fr

  • oracle sql statement execution plan in the analysis method 2011-01-14

    How to generate explain plan? Answer: Run utlxplan.sql. The establishment plan table Specific SQL statement, using the explain plan set statement_id = 'tst1' into plan_table for sql statement Run explain plan look utlxplp.sql or utlxpls.sql (Select *

  • corresponding mongodb statement sql statement 2011-01-28

    corresponding mongodb statement sql statement HK Study Notes I believe we are all very familiar with the sql, sql statement, you Nengkanmingbai What is ... a compare, so naturally I know what it means mongodb API http://api.mongodb.org/java/2.5-pre-/

  • Oracle sql optimization notes (reproduced) 2011-02-25

    http://zmaze.org/?p=193 Basic Sql write notes Minimize the use of the IN operator, essentially all of the IN operator can use EXISTS instead. Do NOT IN operator, you can use NOT EXISTS or outer join + instead. IN subquery in the implementation of Ora

  • The second study MyBatis. SQL statement mapping file (2) 2011-05-05

    Second, SQL statement mapping file (2) 2.2 select A select element is very simple. For example: Xml Code <! - Check the students, according to the id -> <select parameterType="String" resultMap="studentResultMap"> <! [CD

  • Oracle batch create sql statement 2011-07-09

    Today, in the process of creating multiple function, we found that Oracle executed more than create or replace statement, but only to create a function. Looked into the function, the error: Encountered the symbol create, because the implementation of

  • sqlite database sql statement 2011-08-09

    One study notes 2010 SQLite One. How to get the latest version of SQLite Official site: http://www.sqlite.org/ From http://www.sqlite.org/ Download page for the site The following page to download http://www.sqlite.org/download.html http://blog.csdn.

  • Skills: dynamically created SQL statement 2009-06-12

    MATT on his blog, has posted a dynamically created SQL statements Bo-wen. In the text, he referred to himself using the IN operator when a database query problems: According to the set of external incoming IN operator in the number of decisions place

  • ID generation strategies and Save () whether to issue the sql statement of: 2008-08-23

    ID generation strategies and Save () whether to issue the sql statement of: The primary key generation side of road is the uuid, so called after the completion of save, but the entity object into the session management, and will not issue insert stat

  • SQL statement used the classical 2010-02-04

    1 - Note: the word of several high-level query operations 2A: UNION operator 3UNION operator results through a combination of the other two tables (for example, TABLE1 and TABLE2) and eliminate any duplicate rows in the table derives a result table.