sql query tuning techniques in oracle

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

Recursive query of Oracle PL/SQL-CONNECT BY PRIOR: Duzz $ scott @ orcl>SELECTEmployee_id, last_name, job_id, manager_id 2FROMEmployees 3 STARTWITHMaid = 101 4CONNECTBYPRIOREmployee_id = manager_id; (PRIOR: Previous, with priority; perform recursive queries based on the previous employee_id that is equal to the row's manager_id, from top to bottom) EMPLOYEE

Copy Code code as follows: -Oracle takes the SQL from Monday to the weekend This takes the first days of the week, starting with Sunday. Select To_char (to_date (' 20130906 ', ' yyyymmdd '), ' d ') from dual; --Result: 6 Note: 2013.09.06 is Friday, the sixth day of the week Select To_char (sysdate+ (2-to_char (sysdate, ' d '))-7, ' YYYYMMDD ') from dual;---Monday Select To_char (sysdate+ (2-t

nameSELECT * from emp order by ename;--18. Show employee name and date of employment, according to their service life, the oldest employee card at the frontSelect Ename, HireDate from emp order by HireDate;--19. Show the name/work and salary of all employees, sorted in descending order of work, sorted by salary if co-work is the sameSelect ename, Job, Sal from emp order by job Desc, Sal;--20. Displays the names of all employees/the year and month in which the company was joined, arranges the mo

in two columns of a link relationship to mark which column is a ' child id '. This determines the order in which they are retrieved.If Prior is placed in front of the ' child ID ', the retrieval order is retrieved in the order of the top-down (from root to leaf). (At this point the SQL statement identifies the correct ' child ID ')If Prior is placed before the ' parent ID ', the retrieval order is retrieved in the order of the bottom-up (from leaf to

SQL statement for querying and deleting duplicate records1. Search for redundant duplicate records in the Table. duplicate records are determined based on a single field (peopleId ).Select * from peopleWhere peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1)2. Delete unnecessary duplicate records in the Table. Repeat records are determined based on a single field (eagleid), leaving only the records with the smallest

Oracle journey (ii) SQL statement query settings such as t_chmo_order_opera p, business table t_chmo_teruser u, user table t_chmo_province, Province table t_chmo_opera_price r price list ----- value-added business details select p. oo_source_num as phone number, r. opera_price as amount, cp_province_name as region, p. oo_create_time as date, u. ct_category as ope

SQL statements for Oracle management and query Tablespace and capacity: Select tablespace_name, sum (bytes)/1024/1024/1024 from dba_data_files group by tablespace_name; Free space of the tablespace: Select tablespace_name, sum (bytes) from dba_free_space group by tablespace_name; System tablespace: Select tablespace_name from dba_tablespaces; System data

Page query SQL statement for the Oracle database tutorialOracleSELECT *From (select RowNum r,t1.* from table name T1 where rownum where T2.R >= m For example, from the table sys_option (primary key is sys_id) from 10 records or retrieve 20 records, the statement is as follows: SELECT *From (select RowNum r,t1.* from sys_option where RowNum where T2.R >= 10 MyS

Label:SELECT * FROM (SELECT Tt.*,rownum RN from(SELECTa.case_id as Treatid,A.type as TYPE,B.content as CONTENT,b.add_user_id as Adduserid,B.add_user_type as Addusertrpe,A.create_time as Createtime,B.check_time as Checktime,E.addusertypeFrom T_medical_case ALeft JOIN t_treatment B on a.case_id = b.treat_idLeft JOIN (select c.patient_id as ID, C.user_name, ' 2 ' as AddusertypeFrom T_patient_info CUNIONSelect d.doctor_id as ID, D.user_name, ' 1 ' as AddusertypeFrom T_doctor_info D) E on b.add_user_

SELECT DISTINCTFA. Application_short_name, FA. BasePath, Fat. Application_name, fe. Executable_name, fe. Execution_file_name, FCP. Concurrent_program_name, FCP. Output_file_type, FCP. Output_print_style, Fcpt.user_concurrent_program_name, Fcpt.language, fcpt.de Scription, Fcp.enabled_flag fromfnd_application_tl fat, fnd_application fa, Fnd_concurrent_programs_tl fcpt, Fnd_concurrent_program S FCP, Fnd_executables feWHERE 1 = 1 andFat. application_id=FA. application_id andFat. LANGUAGE=FCP

Query the connection information in the database. SQL _COMMAND is an SQL statement that can release A process: selectA. SID, B. SPID, a. SERIAL #, A. lockwait, A. USERNAME,. Query the connection information in the database. SQL _COMMAND is an

1. First, use the top command to view the spid numbers with more resources 2. query the current time-consuming session ID, user name, sqlid, and so on:Select Sid, serial #, machine, username, program, SQL _hash_value, SQL _id,To_char (logon_time, 'yyyy/MM/DD hh24: MI: ss') as login_time from V $ sessionWhere paddr in (select ADDR from V $ process where spid in (

In reality, there is such a requirement. Sometimes the data we need to query is in multiple tables. How can we query data from multiple tables? In this case, you need to use table connections for queries. In reality, there is such a requirement. Sometimes the data we need to query is in multiple tables. How can we query