is used, but less resources are used.
Purpose:1. Let's talk about Oracle optimizer and related knowledge.2. Answer Why sometimes a field in a table is clearly indexed. When you observe some SQL Execution plans, you will find that the index does not exist.3. If you have doubts about first_rows and all_rows, you can also read this article.
Start:
Before executing an SQL statement, Oracle needs to analyze the
Science
Unloader such a tool
Oracle does not provide a tool to export data to a file. However, we can use the Sql*plus select and format data to output to a file:
Set echo off NewPage 0 spaces 0 pagesize 0 feeds off head off Trimspool on
Spool Oradata.txt
Select Col1 | | ',' || col2 | | ',' || Col3
From TAB1
where col2 = ' XYZ ';
Spool off
Alternatively, you can use Utl_file pl/sql package processing:
REM
What should I do if SQL Server executes dynamic SQL statements? The following describes the two correct ways for SQL Server to execute dynamic SQL. We hope that you can have a better understanding of SQL Server to execute dynamic SQL
Shared SQL zone, private SQL zone, and cursor
I. Share the SQL ZoneThe shared SQL zone is another way of calling the handle of the cursor object in the Library cace and the data of the Child heap.Ii. Concept of private SQL ZonePrivate S
1. Execute DDL and DDL statements (except select)
A) open a cursor.
B) analyze the statements to be executed
C) bind any input variables that may be required
D) execute the statement
E) Close the cursor.The execution of the SELECT statement is as follows:
Set serveroutput on;
Declare
V_cursorid number;
V_selectrecords varchar2 (500 );
V_numrows integer;
V_mynum integer;
V_mytext varchar (50 );
Begin
V_cursorid: = dbms_ SQL .open_cursor;
V_selectrecord
Tags: LTE tin tac hit Lex supports RAC query statement testingIn our Java development application process, will inevitably encounter the system performance problems, especially in the enterprise application development process, will deal with the database. When we encounter database performance, the most effective is to directly track the execution of each SQL statement, the optimization of SQL statements,
SQL statement to query the SQL Server name and IP address, SQL Server
Get Server Name:
SELECT SERVERPROPERTY('MachineName')select @@SERVERNAMEselect HOST_NAME()
To obtain the IP address, run the ipconfig command in xp_cmdshell:
-- Enable xp_cmdshell exec sp_configure 'show advanced options', 1 reconfigure with override exec sp_configure 'xp _ cmdshell', 1 reconf
SQL statement to query the memory usage of SQL Server, SQL Server
SELECT type, -- Clerk type sum (bytes) as vm_Reserved_kb, -- reserved memory sum (bytes) as vm_Committed_kb, -- Submitted memory sum (awe_allocated_kb) as awe_Allocated_kb, -- the memory used after AWE is enabled sum (bytes) as sm_Reserved_kb, -- shared reserved memory sum (bytes) as sm_Committed_k
Oracle Advanced SQL (4) -- SQL column and column conversion topic 2, oracle -- SQL
[Previous topic] column-and-column conversion 1-Introduction to functions of aggregate and unaggregate
Implementation case 1001: lufei1001 shanzhi1001 namei1001 qiaoba: 1001 lufei, shanzhi, namei, qiaoba typical row-column conversion: there are many ways to achieve this.
Here, the
Label:A small SQL Server DBA wants to talk about the capabilities of SQL ServerBaidu has not yet searched for relevant individuals to write more valuable articles, at least in the Chinese network of the world there is noBut there is an article on Microsoft's website: "Comparing SQL Server with IBM DB2"The article compares from the following aspects1. TCO and ROI2
MSSQL provides us with two commands for dynamically executing SQL statements, namely exec and sp_executesql, and generally, sp_executesql has the advantage of providing an input-output interface, and exec does not. One of the biggest benefits is the ability to reuse execution plans with sp_executesql, which provides execution performance and can write more secure code. EXEC will be more flexible in some cases. Unless you have compelling reasons to use
Label:Introduction The transaction log in SQL Server is undoubtedly one of the most important parts of SQL Server. Because SQL Server uses transaction logs to ensure persistence (durability) and transaction rollback (Rollback). This also partially ensures the acid properties of the transaction. When SQL Server crashes,
. Otherwise, long transactions will have a great impact on system performance.
Distinct and Having clauses are time-consuming operations and should be used as little as possible
Union All is used to replace Union when repeat record merging is not required.
Explicit cursors are used instead of implicit cursors, especially when the data volume is large.
Whether to use functions
Use Direct table Association instead of Exist. Use Exist or Not Exists to represent In. Sub-queries In are inefficie
1.sqlplus Execute SQL Error: ora-01756:quoted string not properly terminated
Category: Technology
An error occurred while executing the SQL file in Sqlplus: ora-01756:quoted string not properly terminated but SQL is not a problem, looking for many reasons, may be due to use Toad Expor
SQL Server 2000 T-SQL operation
T-SQL (trantsact-SQL) is a structured query language developed by Microsoft on the basis of Sybase.ProgramAnd the tools for communicating and accessing stored procedures with SQL Server. Included ansi89 and ansi92 standards. So T-
1 Installation Configurationhave been using Toad to manage the database, today, a small study of SQL Developer tools, after the installation of ORACLE, the default installation of SQL Developer, the installation directory is $oracle_home/sqldeveloper, The first time you start will be prompted to enter the Java directory, note that the requirement here is JAVASE,J
Remote connection SQL Server 2008, server-side and client configuration
Key settings:
The first step (SQL2005, SQL2008):
Start--> program-->microsoft SQL Server 2008 (or the)--> Configuration Tool-->sql Server Configuration Manager-->sql Server network configuration--> MSSQLServer (this name is the exact instance na
PremiseWhen this article discusses only SQL Server queries,For non-composite statistics, where the statistics for each field contain only the data distribution of theHow to estimate the number of rows based on statistical information when combining queries with multiple fields.The algorithm principle of estimating the number of data rows using statistics from different fields, and the differences between SQL
|| '_' || cur_snap.snap_id || '.htm', 'w', 32767); FOR cur_rep IN (SELECT output FROM TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(l_dbid, l_instance_number, l_last_snap, cur_snap.snap_id))) LOOP UTL_FILE.put_line(l_file, cur_rep.output); END LOOP; UTL_FILE.fclose(l_file); END IF; l_last_snap := cur_snap.snap_id; END LOOP; EXCEPTION WHEN OTHERS THEN IF UTL_FILE.is_open(l_file) THEN UTL_FILE.fclose(l_file); END IF;
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.