how to tune sql query in oracle

Query all the elements of a distance from a certain point (that is, the surrounding query) SQL: Select T. objectid, T. comp_type, T. datatype, T. comp_name, T. health_lic, T. shape. sdo_point.x as x, t. shape. sdo_point.y as Y, T. reg_addr, T. bus_addr from t_publicplaces t where 1 = 1 and substr (T. comp_type,) = '01' and sdo_within_distance (T. shape, sdo_geom

"> 2. Merge into statement: For the top statement, you can use the merge into statement --Merge into implementation method merge into EMP using (SELECT * from emp_new1 ) E on (e.empno = emp.empno) when matched then update set emp.empdesc = E.empdesc;The execution results are the same as the results in 1, assuming inference. It is recommended to use the merge into method. Because merge into only visits once emp_new1. Iii. Deleting delete op

Oracle Spatial spatial data SQL query related instances -- Select dlbm, dlmc, trim (zldwdm) as zldwdm_1 from gzdt where nvl (zldwdm, 'T') = 'T' or zldwdm = '';Update gzdt set zldwdm = '000000' where nvl (zldwdm, 'T') = 'T' or zldwdm = '';Commit;Delete from gzdt where zldwdm like '20160301'Commit;// Obtain the space areaUpdate gzdt set mj = MDSYS. sdo_geom.sdo_are

Oracle Query result set, random sort Copy Code code as follows: SELECT * FROM table1 ORDER by Dbms_random.value (); MySQL randomly query out a record: Copy Code code as follows: --The following query statement is efficient, do not use SELECT * FROM table1 to rand () LIMIT to

Original address: http://www.cnblogs.com/ginponson/p/5746435.htmlAssuming that this is page pageno, there are pagesize records per page, and now the student table is queried using MySQL, Oracle, and SQL Server paging.Paging Query for 1.MysqlSELECT * from Student -1* pagesize,pagesize;Understanding: (Limit n,m) + starts from the nth line to fetch M Records, n f

One of the Oracle-SQL skills (query continuous records) Requirement Description: www.2cto.com needs to query that a customer has been continuously performing business for those days of a year. The SQL implementation is as follows: Create a table: create table test_num (tyear

Query the method for binding variable values in Oracle SQL statements, oraclesql Alter session set nls_date_format = 'yyyy-mm-dd, hh24: mi: ss'; set linesize 400col SQL _Id format a20col name format a20col datatype_string format a14col value_string format a20select SQL _id,

Oracle paging query SQL syntax classification: Oracle column 4850 people read comments (1) collect reports -- 1: no order by statement. (Most efficient)-- (After testing, this method has the lowest cost. Only one layer is nested, and the speed is the fastest! Even if the queried data volume is large, it is almost unaf

First, let's take a look at SQL server. Previously, we used the foreground to select a start time and an end time (in the smallest unit of day), and then used it as a condition for query, if we use "between starttime and endtime" as the condition, we will automatically convert "" to "00:00:00 ", therefore, the following query condition is "between '2014-06-16 'an

Content HR Mode Oracle sample Schema Install HR Mode Demo level Query Hierarchical Query Reference HR mode Oracle sample Schema Oracle has several examplesSchema, Including,HR (Human Resources),OE (Order Entry),OC (Online Catalog),PM (Product Media),IX (Information Ex

Explain the Oracle SQL grouping query instances and explain the oraclesql instances I. Introduction There is a user table. The query table structure statement is as follows: select dbms_metadata.get_ddl('TABLE','USERS') from dual; The table structure is as follows: CREATE TABLE "PMSS"."USERS" ( "ID" NUMBER(10,

Tags: on as SQL type Oracle table text class ROMQuery all columns and their properties for a table:Select From User_tab_columns t,user_col_comments Cwhere t.table_name = c.table_name and T.column_name = c.column_name and t.table_name = women;To find the primary key for a table:Find the foreign key of the table (including the name, the table name of the reference table, and the corresponding key name, whi

My SQL is very poor, half a day response to, was a colleague joke. Oh, ah ah. Must surpass him later!!Table ShopPk_shop PRIMARY KeyName Commodity Shop Pk_shop Name 001 Down 002 Coca - cola Bpm_ll_test2 PK_BPM_LL_TEST2 PRIMARY Key Sname Product Name 01 001 Select (select name from shop where shop s s.pk_shp

Copy codeThe Code is as follows:-- Oracle reads SQL statements from last Monday to weekend-- The day of the week starts on Sunday.Select to_char (to_date ('201312', 'yyyymmdd'), 'D') from dual;-- Result: Note 6: the sixth day of the week is Friday.Select to_char (sysdate + (2-to_char (sysdate, 'D')-7, 'yyyymmdd') from dual; --- last MondaySelect to_char (sysdate + (2-to_char (sysdate, 'D')-1, 'yyyymmdd') fr

,'YYYYMMDD') Rangebetween thePreceding Currentrow)End asf70115_70011, Case whenA.c_date>To_char (sysdate-3,'YYYYMMDD') Then sum(VALUEF1) Over(Partition byA.code,a. Year Order byTo_date (A.c_date,'YYYYMMDD') Rangebetween thePreceding Currentrow)End asf70104_70011, Case whenA.c_date>To_char (sysdate-3,'YYYYMMDD') Then sum(VALUEF6) Over(Partition byA.code,a. Year Order byTo_date (A.c_date,'YYYYMMDD') Rangebetween thePreceding Currentrow)End asf70126_70011, Case whenA.c

String sql= "SELECT * FROM (select E.*,rownum r from (SELECT * to emp ORDER BY sal Desc) e) where r between? and? ";String sql= "SELECT * FROM (select E.*,rownum r from (SELECT * to emp ORDER BY sal Desc) e) where r between 6 and 10";StringBuilder vs = new StringBuilder ();Vs.append ("SELECT * FROM (");Vs.append ("Select E.*, RowNum RNO from (SELECT * from emp ORDER BY sal Desc) e");Vs.append (")");Vs.appen

operation is the same as in 1, and the merge into method is recommended if you make a decision. Because merge into accesses only one emp_new1. Iii. Deleting delete operations 1. Delete duplicate records: There are many kinds of methods, here only one, using the analysis function grouping, determine whether the group number is greater than 1. Delete from emp where rowid in (select Rid from (select rowID as RIDs, row_num