Use the declared way to sneak into SQL to Java programming. It is indeed a good idea.
SELECT Returns the order of records
Http://voruta.sourceforge.net/
It seems that the programming QDox and CGLIB at this level is generally essential.
What does @@ DBTS return? AnswerThe current value of the timestamp data type within the current database. ExplanationThis returns the value of the current timestamp data type in the current database. These are guarenteed to be unqiue within a database. --- select * From YOUTABLEEEN TO_DATE ('20030112', 'YYYYMMDD') And to_date ('20030215', 'YYYYMMMDD') ---------- Select the record of M N to M in Oracle SELECT * From (SELECT ROWNUM R, YOTABLENAME. * from YourTableName WHERE ROWNUM <= N M) SSWHERE SS.R> = N; or Select * from test where id> = nminusselect * from test where id> = n m --- Oracle9.2.0 [Table 2] There are 100,000 records of Test INSERT INTO Table 1 SELECT * FROM Table 2; time consuming 6.969 seconds -------------------- -------------------------- Division 3.332 seconds at once in execution, 1.932 second crete or replace procedure sp_insert_test2as dec_count_wk1 number; Record Test Table Dec_JS_WK1 Number; - Start Insert Mark Begin Dec_JS_WK1: = 0; - Initialized to 0 Select Count (*) INTO DEC_COUNT_WK1 from Test; --Test Table has 100,000 record dec_count_wk1: = DEC_COUNT_WK1 / 2 ; - initialize 50,000 for II I in 1..2 - Plug 2 Loop Insert Into Test2 Select Sys_NO [Field Name], SYS_DATE [Field Name] from (SES_DATE ANNNNO, SYS_DATE AROM TEST) WHERE RO> = DEC_JS_WK1 AND RO <= DEC_COUNT_WK1; DEC_JS_WK1: = DEC_COUNT_WK1; - The start is 50,000 DEC_COUNT_WK1: = DEC_COUNT_WK1 * 2; - End is 100,000 end loop;
COMMIT; EXCEPTION WHEN OTHERS THEN ...; ROLLBACK; END; The following is the structure of two tables TEST and TEST2 structure the same name type nullable default comments -------------- ------------ -------------- SYS_NO VARCHAR2 (100) y sys_date date y sysdate a company's written test topic, everyone comes to answer, By the way, the boys! I asked a group of people in ITPub! 1. Please briefly explain the understanding of the concept of data warehouse. 2. Please explain the following concepts: Data Market, Fact Table, Niki, Drill, OLAP, Particle Size, KPI, ETL3. Please list the various data warehouse tools (including modeling tools, ETL tools, front-end display tools, OLAP Server, Database, Data Mining Tools), and familiarity. (Slightly answered in depth, you can answer the functions you have done in the product) 4. Please use the C or other advanced programming language, write a program, functionality is to generate all the number of days from 2000 to 2010 and the date is the day of the week, write a comma-separated text file, or PL / SQL will be inserted into the data. A temporary table. (On) 5. Do you have any use of Oracle partitions, bitmap indexes, perform plan analysis functions? What is the difference between bitmap indexes and normal index? When do you use a bitmap index? 6. What UNIX system do you use? Write a shell statement, complete the function: Output all files in the current directory to a file file_list after the current system time is 5 hours. 7. A UNIX machine with two CPUs, 2G memory, with a 300g disk array, the database is Oracle8i or Sybase IQ 12. There are 5 main facts, the initial data is loaded 30g of raw data, and each of the factories per day increases about 200M raw data, and each table is about 5 to 10 analyzers, and the main analysis is needed, requiring the next day to be able to Data warehouse accesses the data of the first day. The business system database where the daytime data source cannot be accessed. Please briefly describe your data initial loading policies, data refresh policies, index policies, system parameters, and database parameters for Oracle or Sybaseiq. 8. You have a slow change dimension in the data warehouse, such as the address change of the customer registration information, how to change the information such as the agent change to ensure that the data is consistent? 9. What is your experience in the use of metadata management in a data warehouse? 10. Explain the following Oracle Database Concepts: TableSpace, Extent, Segment. 11. Description Oracle Table Initial, Next, PctFree, PCTUSEDs' meaning. For data warehouse facts for only batch loading, no Update, setting principles of settings. 12. Lists several index types in Sybaseiq, indicating that the features of these indexes are for different query requirements and data content, the usage principles and occasions of these indexes. SQL tuning
SQL> DESC SAM_ROLE;
Name Type Nullable Default Comments
----------------------------------- -------------------------- Rolecode Varchar2 (6) Role code
EnterpriseCode Varchar2 (15) Corporate Code
ROLEDESC VARCHAR2 (60) Role Description
Roletype Char (1) 'I' Role Type ()
Isenabled char (1) 'Y' available identity
SQL> DESC SAM_ROLE_FUNCTION;
Name Type Nullable Default Comments
----------------------------------------------------------------
Funccode Varchar2 (15) Function Code
Rolecode Varchar2 (6) Role code
EnterpriseCode Varchar2 (15) Corporate Code
Isenabled char (1) 'Y' available identity
The field of the black font above is a masterping.
First use a table tuning:
The statement is as follows:
Optimization based on selective:
Rule-based optimization:
You can see that if you do not add any conditions, it is a full table scan.
Let's add the conditions:
Select * from sam_role a where a.enterprisecode = 'aaaa';
It can be seen that no longer uses full mete scanning, and replaced with Index Range Scan.
Note: The above condition EnterpriseCode is Jian.
Let's take a look at how we use it:
The conditions are as follows:
Select * from sam_role a where a.roledesc = 'aaaa';
The optimization results are as follows:
It can be seen that if the query condition does not contain the maintenance, then the full table scan is still used.
Contains a righteous query as follows:
It can be seen: If you contain a masterjust in the query, you will no longer use a full table scan.
As can be seen from the above contrast, set the basic, commonly used selection condition fields to be sympathetic, which can be greatly submitted.
For the statement optimization of the OR in the condition and the difference between the AND, let's take a look:
The condition we used above is and if it is changed to or a full table scan.
The results are as follows:
There is a condition in the above conditions to be a key, the other is not, the execution is full-scale scan.
Let's take a look at both keywords, but the condition is OR:
The above-based optimization is: full table scanning, and rule-based optimization is an index scan.
Obviously this situation uses rule-based optimizer SQL execution results than the Chose rule optimizer.
Author: Tian Chunfeng
Mail: Accesine At Gmail Dot COM
2004-6-26
